Обсуждение: Curious about exclusive table locks

-----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-----

"Uwe C. Schroeder" <uwe@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

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

On Wednesday 12 November 2003 07:37 pm, Tom Lane wrote:
> "Uwe C. Schroeder" <uwe@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-----

"Uwe C. Schroeder" <uwe@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/os2002/view/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