From: Tom Lane <tgl@sss.pgh.pa.us>
To: Forest Wilkinson <fspam@home.com>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: possible row locking bug in 7.0.3 & 7.1
Date: Tue, 27 Mar 2001 18:14:02 -0500
Message-ID: <15828.985734842@sss.pgh.pa.us> (raw)
In-Reply-To: <01032713101600.07241@bartok>
References: <01032713101600.07241@bartok>
Forest Wilkinson <fspam@home.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, fspam@home.com, pgsql-hackers@postgresql.org
Subject: Re: possible row locking bug in 7.0.3 & 7.1
In-Reply-To: <15828.985734842@sss.pgh.pa.us>
References: <01032713101600.07241@bartok> <15828.985734842@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox