Brad Nicholson wrote:
On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote:
Jorge Godoy wrote:
Chris <dmagick@xxxxxxxxx> writes:
I'm not sure what type of lock you'd need to make sure no other transactions
updated the table (see
http://www.postgresql.org/docs/8.1/interactive/sql-lock.html) but "in theory"
something like this should work:
begin;
select id from table order by id desc limit 1;
insert into table (id, blah) values (id+1, 'blah');
commit;
This is part of the solution, yes. But I would still need locking this table
so that no other concurrent transaction gets another "id". I don't want to
lock the main table --
Wouldn't SELECT ... FOR UPDATE give you the row lock you need without
locking the table?
Nope, concurrent transactions won't work.
Let current max id = x
Transaction 1 (t1) does a select max(id) for update, gets a lock on the
last tuple at the time of the select, and gets x as a value for max id
Transaction 2 (t2) does a select max(id) for update, has to wait for t1
to release its lock.
t1 inserts (x+1) as the new max id of the table. t1 releases its lock
t2 is granted the lock on the tuple it has been waiting for, which
contains the max id of x
t2 tries to insert a value of x+1, insert fails (if it doesn't, you
really want to have a close look at your constraints :-)
I see. The FOR UPDATE form is not applicable with aggregates.
I was looking at this as if he uses a separate table to keep track of
the most-recently-issued sequence value, as the original post specified
was the case, whereas using the MAX aggregate, as suggested
subsequently, implies calculating the most-recently-used sequence value
from the table that has all the earlier sequence values in it.
The original question stated he "...acquire(d) a SHARE ROW EXCLUSIVE
lock", which locks the whole table, so my thought was to suggest that
locking the entire table was not necessary -- rather only the row. Of
course, if the control table has only one row, then this may not matter.
But since nothing beats empirical evidence, I tried it using
BEGIN;
SELECT column1 FROM test.table1 FOR UPDATE;
UPDATE test.table1 SET column1=column1+1;
COMMIT;
executed line-by-line alternating between two separate pgAdmin SQL
windows, and concurrency is properly accounted for.
Regards,
Berend Tober
