Received: from acheron.rime.com.au (albatr.lnk.telstra.net [139.130.54.222]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2S0FAw31902; Tue, 27 Mar 2001 19:15:11 -0500 (EST) (envelope-from pjw@rhyme.com.au) Received: from oberon (Oberon.rime.com.au [203.8.195.100]) by acheron.rime.com.au (8.9.3/8.9.3) with SMTP id KAA09137; Wed, 28 Mar 2001 10:14:05 +1000 Message-Id: <3.0.5.32.20010328101405.02b57790@mail.rhyme.com.au> X-Sender: pjw@mail.rhyme.com.au X-Mailer: QUALCOMM Windows Eudora Pro Version 3.0.5 (32) Date: Wed, 28 Mar 2001 10:14:05 +1000 To: Tom Lane From: Philip Warner Subject: Re: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1 Cc: pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org In-Reply-To: <15828.985734842@sss.pgh.pa.us> References: <01032713101600.07241@bartok> <01032713101600.07241@bartok> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Archive-Number: 200103/567 X-Sequence-Number: 2237 At 18:14 27/03/01 -0500, Tom Lane wrote: >Forest Wilkinson 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 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. >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 |/