On Wednesday 16 August 2006 10:59 am, elein wrote: > On Mon, Aug 14, 2006 at 02:46:17PM -0700, Adrian Klaver wrote: > > 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@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 :-) > > > > 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-UP > >DATE-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).***" > > If this is true the solution for a transactional, gapless sequence is > possible for table.gl_id where updated from count.gl_id. It is simple. > However, it *depends* on the fact that the second transaction getting the > newly updated record from the first transaction. It seems pretty clear, > not counting aggregates, that this is true from this doc snippet. Speak > now, if someone doesn't read it this way! I'd like to understand why. > > If it weren't true, there would also be a workaround which caught a > duplicate value and tried again, looping. > > I may publish the gapless sequence technique on general bits if there is no > discrepancy in the understanding of the status of the second transaction's > row value (updated). > > --elein > elein@xxxxxxxxxxx After I discovered that aggregates did not work I did some simple tests updating a single row table. As I far as I could determine the docs hold true :) I only ran three transactions at a time but each saw the incremented value from the previous transaction. -- Adrian Klaver aklaver@xxxxxxxxxxx
