Subject Re: Is UPDATE in read-committed transaction atomic?
Author jaratulpas
--- In, "Ann W. Harrison"
<aharrison@...> wrote:
> jaratulpas wrote:
> > I have an application which uses read-committed transactions for
> > performance reasons.
> Have you actually tested the performance of the two modes, or
> are you assuming that read-committed must be faster because it's
> faster in other databases?

Yes, we measured the performance of both modes with our application
and the 'snapshot' mode was much slower. It is not a typical business
application, however.

> > Is the statement
> > update object set cnt = cnt + 1 where id = 'a';
> > safe, i.e. is the row locked for the duration of the update
> No, the row isn't locked at all, but yes, the statement is safe,
> though it will lead to deadlocks.

Could you please tell me more about the mechanics? How can it lead to
deadlocks if the row is not locked?

The only way I can imagine is that the whole 'update' statement refers
to the same version of the row (even of someone commits a new version
in the middle) and that the 'deadlock' is actually conflicting update
reported as deadlock.

Best regards, Jaar