-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thursday 15 April 2004 04:20 pm, Tom Lane wrote: > "Uwe C. Schroeder" <uwe@oss4u.com> writes: > > I use a stored proc to get the next identifier: > > > > CREATE OR REPLACE FUNCTION ib_nextval (character varying) RETURNS > > character varying > > ... > > BEGIN > > LOCK TABLE ib_counter IN ACCESS EXCLUSIVE MODE; > > UPDATE ib_counter SET last_value=last_value+1 WHERE > > name=countername; SELECT INTO cprefix,counter,dlen > > prefix,last_value,display_length FROM ib_counter WHERE name=countername; > > > > My assumption would be that if I do an exclusive lock on the table I > > can't do the update or a second exclusive lock, so the stored proc > > should block (or fail). > > It does block, and it does do the update correctly (at least if you're > not doing this in serializable mode). The problem is that the SELECT > doesn't get the right result. The SELECT actually sees two row versions > as being valid: the one you just created by UPDATE, and whichever one > was current when the outer transaction started. One question to "was current when the outer transaction started". Does that mean that if I have a long running transaction, all selects inside that transaction will only see what was committed as of the start of that transaction ? So if I do a "update xxx set ...." outside of the transaction, nothing inside the transaction will ever see that change, although it's committed ? This is "read committed" isolation level, where I would expect the selects inside the transaction see anything that is committed, not what WAS committed at the start of the transaction. > But SELECT INTO will > return at most one row, so it's roll-of-the-dice which one you get. > You can avoid this by attaching FOR UPDATE to the SELECT. > > There have been discussions about this effect in the past (try searching > the pghackers archives for mentions of SetQuerySnapshot). In this > particular example it definitely seems like a bug, but if we fix it by > performing SetQuerySnapshot between statements of a plpgsql function, > we may break existing applications that aren't expecting that to happen. > So far there's not been a consensus to change the behavior. > > BTW, I'd lose the LOCK if I were you; it doesn't do anything for you > except prevent concurrent updates of different counters. The row lock > obtained by the UPDATE is sufficient. > > regards, tom lane - -- UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax: +1 650 872 2417 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAfyxsjqGXBvRToM4RAs0pAJ0cwAE/BdrLL/lq3Y2jBnmnW7rMFwCg0mXN 6EXDA/UH1kBRdnz0sm+NgSE= =hT3X -----END PGP SIGNATURE----- ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
