Re: Advice on implementing counters in postgreSQL

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.

Regards
Marco

--
Marco Bizzarri
http://iliveinpisa.blogspot.com/