Curious about exclusive table locks

[Uwe C. Schroeder]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi.
I have the following little stored proc:

CREATE OR REPLACE FUNCTION public.ib_nextval(varchar)
RETURNS varchar AS
'DECLARE
countername ALIAS FOR $1;
cprefix varchar;
counter integer;
dlen integer;
complete varchar;
format varchar;

BEGIN
LOCK TABLE ib_counter IN ACCESS EXCLUSIVE MODE;
SELECT INTO cprefix,counter,dlen prefix,last_value,display_length FROM
ib_counter WHERE name=countername;
counter := counter + 1;
UPDATE ib_counter SET last_value=counter WHERE name=countername;
format := \'FM\';
FOR i IN 1..dlen LOOP
format := format || \'0\';
END LOOP;
complete := cprefix || to_char(counter,format);
RAISE NOTICE \'result is %,%,%,%\',complete,cprefix,counter,dlen;
RETURN complete;
END;
It's basically a counter incremental thing that is independant from any serial
value, but it behaves like a serial.So everytime the func is called, it
increments a counter and returns the new "key".
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.
To my understanding the access exclusive mode should lock the table for read
access also, so it should be impossible to get the same result twice. (btw
the result looks like ABC-0000123)
Why doesn't this lock the table for read ?

Thx

UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: [phone] San Bruno, CA 94066
Cell: [phone] United States
Fax: [phone]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/svHPjqGXBvRToM4RApv/AJ9BrDgWVYmFahr0dUJ1kxbJpbjzkQCgvhfW
9sv+WWSlOuf8+FZA/F9nD/c=
=Cl1k
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

"Uwe C. Schroeder" <uw*@oss4u.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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Uwe C. Schroeder]

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

On Wednesday 12 November 2003 07:37 pm, Tom Lane wrote:

"Uwe C. Schroeder" <uw*@oss4u.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: [phone] San Bruno, CA 94066
Cell: [phone] United States
Fax: [phone]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/s8KLjqGXBvRToM4RAjtJAKCIFW0nZ9xpXc/ovZR7QyUlgcdKgwCfZp/8
S9plLHJy7T3edWOdpX/xy9M=
=6MBP
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

"Uwe C. Schroeder" <uw*@oss4u.com> writes:

Tom: What did you mean by "snapshot has been set" ?

Read the MVCC documentation. Also it'd be worth your while to read the
slides from my 2002 O'Reilly talk:
http://conferences.oreillynet.com/cs...ew/e_sess/2681
I've been meaning to get those slides put onto the Postgres web site,
but it's never gotten done ...

The slides don't really go into the issues for writing functions,
but the critical point is that the snapshot does not change while
a function runs; it's set when the interactive command that invokes
the function begins. (There has been some discussion about whether
this is a bug or not, but that's how it works in all extant releases.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org