Re: concurrent updates problem

Jan Ploski <jpljpl@gmx.de> writes:
>> 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).

> But the one-line version without select for update is equivalent, right?
> That is, a single UPDATE statement is atomic?

No, not really.

What will happen in the case of a conflict is that the second process to
try to update the tuple will wait for the first to commit, then throw
away its computed update tuple and try to re-evaluate the query plan for
the tuple at issue.  I consider the code that does this (EvalPlanQual
in execMain.c) to be a completely untrustworthy hack.  It probably works
in really simple query plans like the given example, but heaven help you
if you've got joins, subplans, aggregates, nextval() calls, etc.  To say
nothing of rules or triggers, which will see none of this.

If you want something you can actually have some confidence in, I'd
recommend the FOR UPDATE approach.

            regards, tom lane