Re: Implicit row locking during an UPDATE

"Dr. Evil" <drevil(at)sidereal(dot)kz> writes:
> From my understanding, this:

> UPDATE account SET value = 10 WHERE number = 99;

> actually implies all of this:

> BEGIN;
> SELECT value FOR UPDATE FROM account WHERE number = 99;
> UPDATE account SET value = 10 WHERE number = 99;
> COMMIT;
> END;

Not really. Plain UPDATE doesn't lock the target tuple in advance of
computing the new version; it optimistically assumes that it doesn't
need to do that. If that turns out to be wrong when it actually goes
to update the tuple, it backs off, recomputes the update, and tries
again. Normally this ends up at the same place as your two-step
example, but not when the UPDATE targetlist has side effects...

regards, tom lane