Re: Re: have trouble understanding xmin and xmax with update operations from two different sessions

On Sat, Jul 1, 2017 at 6:32 PM, rajan <vgmonnet(at)gmail(dot)com> wrote:

> hello,
>
> thanks for replies, Adrian, Steven.
>
> >So calling it can advance the xid manually. Some testing here showed
> >that what xmin or xmax is created depends on when you call txid_current
> >in either the original session or the concurrent sessions.
>
> I understand this and I am executing my statements inside a Transaction
> block so the xid is not incremented when calling it.
>
> >Also worth noting that an UPDATE in Postgres is a DELETE/INSERT process.
> >The clue is the ctid value. In Session 2 you are looking at the
> >original row(ctid=(0, 2) which has been marked as deleted(non-zero
> >xmax). In Session 3 you are looking at the new row(ctid(0, 4)).
>
> Yes. But why (ctid(0,4)) in *Session 3* carries the xmax of the txid 519115
> in which the update failed with *UPDATE 0* . This is where I can not
> understand,
> 1. Row (0,4) is updated with correct value and (0,3) is not visible in
> Session 2, which is good.
> 2. but in *Session 3* (0,4) also carries xmax which means what? Is it also
> marked for deletion? It can't be, right?
>

When session 2 encounters the locked row which meets the criterion for the
update, it has to wait for the locking transaction to finish. At that
point it locks the row (by writing its transaction into the xmax, and
setting a flag not visible to you, unless you use pgeinspect) and then
re-evaluates if it still meets the criterion. Since it doesn't meet the
criterion anymore, it doesn't finish updating the tuple.

Cheers,

Jeff