Subject Re: [firebird-support] Fwd: How to avoid deadlock ?
Author Ann Harrison
On Fri, Jan 13, 2012 at 4:54 AM, Vander Clock Stephane
<svanderclock@...> wrote:

> "Will the record is lock (dead lock) wait (xx seconds max) and after do
> the query .. and doesn't matter if the value changed between the time the transac start,
> the time you wait and the time you execute the query"

Il y a une trentain d'annees, quelqu'un m'avait dit "Ce qui n'est pas
clair, n'est pas francais."

There's no equivalent in English, but I have no idea what you're
trying to say in the paragraph above and perhaps you could write it
first in French clearly, then translate carefully.

That said, InnoDB in MySQL and NuoDB have a thing that we at NuoDB
call "write committed" which avoids update conflicts (but not actual
deadlocks) at the cost of data consistency.

For those new to the concept, an update conflict occurs whenever a
transaction attempts to update or delete a record that was inserted or
updated by a concurrent transaction and the concurrent transaction
commits. That avoids dirty writes. A deadlock occurs when two
transactions have locks and attempt to get further locks that require
them each to wait for the other.

In the Firebird snapshot mode, update conflicts make sense. If some
concurrent transaction has updated your bank account, changing it from
$20 to $100, you don't want your transaction to add $5 taking the
balance from $20 to $25, losing $80 in the process.

What InnoDB does in that case is to give you different values for a
simple select which would return $20 and a select for update which
would wait until the transaction that added $80 to commit, then return
$100. As long as your update is relative to the stored value, all is
well. If you'd got the $20 from your select, done the addition in
your program, and updated the value to $25, ignoring the $100 value
that you would have got if you'd done a select for update, then you
wouldn't be as happy. But people, even programmers, learn not to do

In read committed mode, Firebird would return $20 as the balance until
the transaction that added $80 commits. After the commit, the same
transaction that saw $20 would see $100, but it still can't add $5
because the $80 was added by a transaction that was not committed when
the current transaction started. In NuoDB, snapshot transactions get
update conflicts by default, and read committed transactions wait for
the conflicting write to be committed so the new value is visible,
then proceed.

Good luck,