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

At 18:14 27/03/01 -0500, Tom Lane wrote:
>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 ]
>
>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.

Surely we should distinguish between real new tuples, and new tuple
versions? I don't think it's ever reasonable behaviour to see two versions
of the same row.


>(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.

Looking at the docs, I see that 'SERIALIZABLE' has the same visibility
rules as 'READ COMMITTED', which is very confusing. I expect that a Read
Committed TX should see committed changes for a TX that commits during the
first TX (although this may need to be limited to TXs started before the
first TX, but I'm not sure). If this is not the case, then we never get
non-repeatable reads, AFAICT:
   P2 (��Non-repeatable read��): SQL-transaction T1 reads a row.    SQL-transaction T2 then modifies or deletes that
rowand performs    a COMMIT. If T1 then attempts to reread the row, it may   receive the modified value or discover
thatthe row has been deleted.
 

which is one of the differences between SERIALIZABLE and READ-COMMITTED.


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

Eek. Does this seem good to you? I would expect that SELECT and
SELECT...FOR UPDATE should return the same result set.


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

Disallowing visibility of two versions of the same row would help.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/