Re: [SQL] possible row locking bug in 7.0.3 & 7.1

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