On Sun, Aug 3, 2008 at 1:50 AM, Marco Bizzarri <marco.bizzarri@xxxxxxxxx> wrote: > On Sun, Aug 3, 2008 at 1:40 AM, Berend Tober <btober@xxxxxxxxxxxxxxxx> wrote: >> >> >> The way I understand the documentation at >> >> "http://www.postgresql.org/docs/8.3/static/transaction-iso.html"; >> >> and >> >> 'http://www.postgresql.org/docs/current/static/explicit-locking.html', >> >> you should not have to use the serial isolation level. >> >> I would define the counter table so as to hold the last-used value, rather >> that the "next" value, and then do the UPDATE first. >> >> As a consequence, assuming all this happens within a transaction of course, >> the SELECT FOR UPDATE syntax is not required either because the UPDATE will >> grab a lock on the row and block other updates until the transaction is >> finished. That is, concurrency is protected and you don't have to restart >> any transactions because subsequent transactions will just wait until the >> first one finishes due to nature of the lock automatically acquired by the >> initial UPDATE statement. >> > > Yes, I'm considering moving away from serializable; the problem is > that I have to explore all the implications of this on my code. Up to > now, I wrote considering a serializable level, so I think I should do > quite a review to be sure about it. A fairly simple test shows that you can do this in read committed: S1: # show transaction_isolation; read committed (setup a table for the value) # create table t (i int); # insert into t values (5); S1: # begin; S1: # update t set i=i+1; S2: # update t set i=i+1; (S2 now waits for S1) S1: # select i from t; 6 S1: # commit; (S2 now can continue...) S2: # select i from t; 7 S2: # commit;
