Re: Curious about exclusive table locks

"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