Re: PostgreSQL transaction locking problem

On Sat, 2 Feb 2002, Tom Lane wrote:

"Jeff Martin" <jeff@dgjc.org> writes:

LOCK TABLE Test IN EXCLUSIVE MODE;

/* This function should be able to operate concurrently BUT CANNOT */

How you figure that? EXCLUSIVE lock is clearly documented to be
self-conflicting; so only one process can hold it at a time. See
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/locking-tables.html

If concurrency is important I'd suggest generating your ID values using
a sequence generator, rather than trying to build your own
implementation.

I actually thought that he's complaining about the fact that it gives him
the same id twice if he runs it in two transactions rather than the delay
until the first commits.

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:

I actually thought that he's complaining about the fact that it gives him
the same id twice if he runs it in two transactions rather than the delay
until the first commits.

As written, he gets a delay (because of the LOCK) *and* duplicate IDs
(because with the serializable isolation level, the second xact can't
see the row inserted by the first, even after waiting for it to commit).
Worst of both worlds.

There's a reason why we provide sequences as primitive objects: you
can't build an equivalent behavior very easily under MVCC rules.

regards, tom lane

I actually thought that he's complaining about the fact that it gives him
the same id twice if he runs it in two transactions rather than
the delay until the first commits.

yes that is the problem.

As written, he gets a delay (because of the LOCK) *and* duplicate IDs
(because with the serializable isolation level, the second xact can't

i get the same error whether using "read commited" or "serializable".

see the row inserted by the first, even after waiting for it to commit).
Worst of both worlds.
There's a reason why we provide sequences as primitive objects: you
can't build an equivalent behavior very easily under MVCC rules.

first the case of my example is just that, an example. i want to learn to
use transactions and locking so that i can do the following....

1. run multiple processes in different transactions,
2. executing the same pg/sql functions which,
3. need to read data at the beginning of the function that is committed,
4. perform calculations and write a result.
5. thus competing processes will need to wait for each to commit the result
in turn.

If I could figure this out the code in my first posting would work. Or even
if I could
get the transaction to bail with a transaction error of some sort, but
waiting would be best.

You say it cannot be done easily, but can it be done? This seems like an
essential feature
of locking to me.

Jeff

Jeff Martin
704 Torrey Lane, Apartment D
Boalsburg, PA 16827
H814-466-7791
jeff@dgjc.org <mailto:jeff@dgjc.org>
www.dgjc.org <http://www.dgjc.org&gt;

Tom Lane wrote:

"Jeff Martin" <jeff@dgjc.org> writes:

As written, he gets a delay (because of the LOCK) *and* duplicate IDs
(because with the serializable isolation level, the second xact can't

i get the same error whether using "read commited" or "serializable".

[ thinks about that... ] Yeah, probably so, because SetQuerySnapshot
is presently executed only in the outer command loop; there won't be
one between the LOCK and the SELECT inside your function. So the SELECT
still doesn't think that the other xact has committed. You could make
it work (in read-committed mode) if you issued the LOCK from the
application before calling the function.

There's been some discussion about whether SetQuerySnapshot should occur
between statements in plpgsql functions or not --- AFAIR, there were
arguments on both sides, and we haven't come to a consensus yet.

I've always been on the side that the Snapshot should be changed
in read-committed mode except the sequence of SELECT statements.

1) AFAIR no one was able to understand the current behavior.
For whom the current spec is ?
2) IIRC the current spec comes from a demand to keep a
consistency of queries with functions. But where was
the proof of the possibility to keep a consistency ?
IMHO it's impossible to guarantee a consistency of
queries with functions which have side effects and
the result would be implementation-dependent.

regards,
Hiroshi Inoue