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

On Monday 14 August 2006 01:59 pm, Brad Nicholson wrote:
> 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 :-)
>

I am still working through this stuff myself, but the following excerpt from
the documentation would seem to contradict what you are saying. See the part
marked with ***. t2 should see a new max(id) after t1 commits and therefore
insert(x+1) would succeed.

http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-FOR-UPDATE-SHARE

"FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as
though for update. This prevents them from being modified or deleted by other
transactions until the current transaction ends. That is, other transactions
that attempt UPDATE, DELETE, or SELECT FOR UPDATE of these rows will be
blocked until the current transaction ends.*** Also, if an UPDATE, DELETE, or
SELECT FOR UPDATE from another transaction has already locked a selected row
or rows, SELECT FOR UPDATE will wait for the other transaction to complete,
and will then lock and return the updated row (or no row, if the row was
deleted).***"
--
Adrian Klaver
aklaver(at)comcast(dot)net