On Monday 14 August 2006 02:46 pm, Adrian Klaver wrote:
> > 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-UPDA
>TE-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).***"
I spoke too soon. Actually trying this exposed the fact that FOR UPDATE does
not work with aggregates. Something I would have discovered earlier if I had
read the documentation all the way through.
--
Adrian Klaver
aklaver(at)comcast(dot)net