At 10:36 PM 13-11-02 +1100, you wrote:

>If he had lockwait set to false... why would there be a deadlock? The second
>update would just roolback .. no?

No transaction ever just rolls back. Clients start transactions and
clients end transactions. To end a transaction, if you can't commit (for
any reason) then the only way to end the transaction is to roll it back.

With errors, you *want* to roll back; but with lock conflicts, you need to
make the choice (or let the user choose) whether it is safe to retry the
commit (and risk overwriting what someone else just committed) or to roll
it back to avoid overwriting. That's something you design into the workflow.

Even though lockwait was set false in this particular transaction,
Recversion was set True. If you want to ensure that you transaction
doesn't overwrite a recversion that is newer than the one you began with,
you set this true and, obviously roll back the transaction if it encounters
the lock conflict. If you want to have your transaction succeed
ultimately, regardless of commits with later recversions, then you set
recversion to False.

"Deadlock" is an ugly word for a great feature. It's an image taken from
wrestling, where each fighter has the other locked in an embrace which
won't be broken until one capitulates. Don't be scared of deadlock: it's
a good sign that, when your data needed protecting, the database was there
for it. :-) Just don't let users see that horrid message. Something like
"Another user is already updating the same record" sounds much less
threatening.

If you simply must *prevent* deadlock, use pessimistic locking. It will do
the job; but it will slow a busy system to a crawl. Better to design
systems with short transactions and avoid designing in bottlenecks like
accounting apps which constantly hit a single balance.

Helen