Re: Advice on implementing counters in postgreSQL

On Sun, Aug 3, 2008 at 1:50 AM, Marco Bizzarri <marco(dot)bizzarri(at)gmail(dot)com> wrote:
> On Sun, Aug 3, 2008 at 1:40 AM, Berend Tober <btober(at)ct(dot)metrocast(dot)net> 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;