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@xxxxxxxxxxx
