Tom Lane wrote:
>
> begin;
> select * from threads where forum_id = 1 and thread_id = 1 FOR UPDATE;
> update threads set views = views + 1 where forum_id = 1 and thread_id = 1;
> end;
>
> Note the FOR UPDATE to lock the row and the transaction wrapping to
> define the scope of the lock. Without this I'd expect you to lose
> some counter increments as a result of two processes doing the UPDATE
> at about the same time (both will read the old value of "views" and
> increment it by one).
Tom - Surely the update runs in its own transaction and will only ever
update a previously consistent value? If there is another transaction
concurrently updating it should block until the results are available?
I can see what you're getting at but for the example query it shouldn't
be necessary if I understand this right.
- Richard Huxton