Re: Best approach for a "gap-less" sequence

On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote:
> Jorge Godoy wrote:
>
> > Chris <dmagick(at)gmail(dot)com> 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.