Re: Table Lock issue

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thursday 15 April 2004 04:20 pm, Tom Lane wrote:
> "Uwe C. Schroeder" <uwe(at)oss4u(dot)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-----