Re: Curious about exclusive table locks

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

On Wednesday 12 November 2003 07:37 pm, Tom Lane wrote:
> "Uwe C. Schroeder" <uwe(at)oss4u(dot)com> writes:
> > This works nice and throws no errors, however the line
> > LOCK TABLE ib_counter IN ACCESS EXCLUSIVE MODE;
> > seems to be ignored, since it's possible to create the same counter twice
> > when the func is run twice at virtually the same time.
>
> The lock is certainly being taken. The real problem is that the
> snapshot has already been set (at the start of the interactive command
> that invoked this function) and so your SELECT fetches a stale value.
>
> You could probably make it work with
>
> 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;
>
> The UPDATE will do the right thing (at least in READ COMMITTED mode) and
> I believe the subsequent SELECT will be forced to see the UPDATE's
> result.
>
> regards, tom lane

Still doesn't work. I assume it's something in the calling method that causes
this. The caller actually has a transaction open which is comitted a bit
later (after this counter function is called).
Tom: What did you mean by "snapshot has been set" ?

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.1 (GNU/Linux)

iD8DBQE/s8KLjqGXBvRToM4RAjtJAKCIFW0nZ9xpXc/ovZR7QyUlgcdKgwCfZp/8
S9plLHJy7T3edWOdpX/xy9M=
=6MBP
-----END PGP SIGNATURE-----