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

Re: possible row locking bug in 7.0.3 & 7.1

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Forest Wilkinson <fspam(at)home(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: possible row locking bug in 7.0.3 & 7.1
Date: 2001-03-27 23:14:02
Message-ID: 15828.985734842@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
Forest Wilkinson <fspam(at)home(dot)com> writes:
> session1<< create function nextid( varchar(32)) returns int8 as '
> session1<<   select * from idseq where name = $1::text for update;
> session1<<   update idseq set id = id + 1 where name = $1::text;
> session1<<   select id from idseq where name = $1::text;
> session1<<   ' language 'sql';
> [ doesn't work as expected in parallel transactions ]

This is a fairly interesting example.  What I find is that at the final
SELECT, the function can see both the tuple outdated by the other
transaction AND the new tuple it has inserted.  (You can demonstrate
that by doing select count(id) instead of select id.)  Whichever one
happens to be visited first is the one that gets returned by the
function, and that's generally the older one in this example.

MVCC seems to be operating as designed here, more or less.  The outdated
tuple is inserted by a known-committed transaction, and deleted by a
transaction that's also committed, but one that committed *since the
start of the current transaction*.  So its effects should not be visible
to the SELECT, and therefore the tuple should be visible.  The anomalous
behavior is not really in the final SELECT, but in the earlier commands
that were able to see the effects of a transaction committed later than
the start of the second session's transaction.

The workaround for Forest is to make the final SELECT be a SELECT FOR
UPDATE, so that it's playing by the same rules as the earlier commands.
But I wonder whether we ought to rethink the MVCC rules so that that's
not necessary.  I have no idea how we might change the rules though.
If nothing else, we should document this issue better: SELECT and SELECT
FOR UPDATE have different visibility rules, so you probably don't want
to intermix them.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Thomas LockhartDate: 2001-03-27 23:17:28
Subject: Re: IANA registration
Previous:From: Thomas LockhartDate: 2001-03-27 21:38:09
Subject: Re: 7.1 RC1 RPM

pgsql-sql by date

Next:From: Christopher SawtellDate: 2001-03-27 23:34:31
Subject: Re: Re: psql win32 version
Previous:From: Bruce MomjianDate: 2001-03-27 22:21:22
Subject: Re: RE: pl/pgsql and returning rows

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