possible row locking bug in 7.0.3 &

possible row locking bug in 7.0.3 &

Post by Philip Warn » Thu, 29 Mar 2001 09:15:47

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

Quote:>(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 row and performs
    a COMMIT. If T1 then attempts to reread the row, it may
    receive the modified value or discover that the row has been deleted.

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

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

Quote:>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.   |----/       -  \

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?



1. possible row locking bug in 7.0.3 & 7.1

I did call it a workaround ;-)

I don't think that we dare try to make any basic changes in MVCC for 7.1
at this late hour, so Forest is going to have to live with that answer
for awhile.  But I would like to see a cleaner answer in future
releases.  As I've opined before, the whole EvalPlanQual mechanism
strikes me as essentially bogus in any case...

                        regards, tom lane

---------------------------(end of broadcast)---------------------------

2. register Informix database in Visual Basic

3. possible row locking bug in 7.0.3 & 7

4. FW: 4gl Reports

5. possible row locking bug in 7.0.3 & 7.1

6. Automating View update?

7. Does XML SQL support XSLT ?

8. Possible BCP Bug?, bcp row termination problem

9. Update query fails due to row size being exceeded (not though, possible bug)

10. Is row level locking possible using the JDBC?

11. Row locking inside a rule, is it possible?

12. Possible Bug - SQL 7.0 & WriteText