Re: concurrent updates problem

On Mon, Mar 19, 2001 at 02:00:03PM -0500, Tom Lane wrote:
> > 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.

Which leads to correct results (updates becoming serialized), doesn't it?

> I consider the code that does this (EvalPlanQual
> in execMain.c) to be a completely untrustworthy hack.

Oh no, don't tell us the grave truth ;)

> 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.

I have to admit not understanding this devil's work (not intended to offend
any of the developers!) in detail, but I have the feeling it should be
mentioned in some prominent place in documentation. I recall from reading
MySQL manuals that they recommended the UPDATE SET x=x+1 type of queries
as a simple way to avoid transactions in some contexts. Now it looks that
with PostgreSQL the matter is more complicated, perhaps due to a different
policy of row-level locking? Which makes me wonder how many other types
of queries that one would think of as atomic at first have such hidden
gotchas? Can you recommend a rule that would guard against this kind of
errors? Obviously, my naive "each SQL statement = atomic, multiple
statements = atomic if grouped into a transaction" does not suffice!

Thanks in advance for sharing some more light on it (I read the chapter
about multi-version concurrency, but it kind of lacks examples that
would make the real-world dangers of loosing consistency clear).

-JPL