By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,372 Members | 1,906 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,372 IT Pros & Developers. It's quick & easy.

Curious about exclusive table locks

P: n/a
-----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: +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/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

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"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

Nov 12 '05 #2

P: n/a
-----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: +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-----
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #3

P: n/a
"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

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.