Re: Question Regarding Locks

Martijn,

thanks for your clarification.

> You need to look at it (XMIN) in conjunction with XMAX. A newly insert row has
> XMIN set and XMAX null. When a row is updated the XMAX of the old row
> is set and a new row is created with an XMIN. When you delete a row it
> just sets the XMAX.
But, as you say below, it also "disappears"... :-)

> > IOW, can I also detect my row being *deleted* from under me by
> > another transaction by way of checking XMIN ? Else I would
> > likely need to check XMAX, too.
> Easy, look for it. If you can't find it, it got deleted...
Doh, of course you are right. I was thinking of doing this:

(assume a row with pk set to 1)

select xmin, ... from ... where pk=1;

... remember xmin as <old_xmin> ...
... do some time-intensive application work ...

select 1 from ... where pk=1 and xmin=<old_xmin> for update;

Now:
- if one row (eg. the "1") is returned then I locked my row
and can happily update it and commit
- if more than one row is returned I am in deep trouble and
I better consider shutting down both my application and the
database for serious investigation - rollback is in order
- if zero rows are returned my row was either deleted (eg.
nothing found for pk=1) or it was updated by someone else
(eg. xmin != <old_xmin>),
from this point on I am entering the slow path anyways (eg.
notifying the user, merge handling, delete detection etc.),
so rollback is in order, too

IOW I should be fine looking at xmin only for *detecting* a
concurrency conflict - be it concurrent updates or the row
having been deleted.

Am I getting this right ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346