Skip site navigation (1) Skip section navigation (2)

Re: Question Regarding Locks

From: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Question Regarding Locks
Date: 2004-10-29 10:41:43
Message-ID: 20041029124143.F624@hermes.hilbert.loc (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-novice

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;

- 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 ?

GPG key ID E4071346 @
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

In response to

pgsql-novice by date

Next:From: Michael GuerinDate: 2004-10-29 19:15:41
Subject: Postgresql crash- any ideas?
Previous:From: Martijn van OosterhoutDate: 2004-10-29 09:34:12
Subject: Re: Question Regarding Locks

pgsql-general by date

Next:From: Michael ShtelmaDate: 2004-10-29 10:42:17
Subject: PostgreSQL on Windows
Previous:From: Marco ColomboDate: 2004-10-29 10:38:29
Subject: Re: Reasoning behind process instead of thread based

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group