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 :-) Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.
