Vander Clock Stephane wrote:
Say we have one table Data and one Table Stats:
Table_Data ID 1 2 5 8 Table_Stats NB_IDs 4
Every time an user adds or deletes one row in Table_Data, then trigger will update the table_stats (NB_IDs) by increasing or decreasing the actual number in it
Several users can update different rows at the same in Table_Data
How to avoid deadlock when the trigger update the NB_Ds in table_stats? What mechanism to use?
Ann W. Harrison answers:
The usual answer (which is in the FAQ) is to have the change to Table_Data insert a row into table_stats indicating that it added a value (+1) or removed one (-1). Periodically, run a procedure to aggregate the results. Updates will cause deadlocks unless they are serialized, meaning that only one transaction can change Table_Data at a time.
Vander Clock Stephane replied:
Thanks Ann,
Yes, i need to use an intermediate system (like a table) that will store the change ... which mean more disk space, mean more memory, mean more slower :(
It could be good if it's possible to have something like a "mutex" inside the database that we can activate in trigger ...
or at least something like:
"If the record is locked (dead lock) wait (xx seconds max) and after do the query .. and doesn't matter if the value changed between the time the transaction starts, the time you wait and the time you execute the query"
Ann W. Harrison answers:
"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 that.
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.
Vander Clock Stephane replied:
c'est claire :)
What I want to say:
Table_A Field_A 12
Transaction 1 (isc_tpb_wait):
update Table_A Set Field_A = Field_A + 1;
At the same time, Transaction 2 (isc_tpb_wait):
update Table_A Set Field_A = Field_A + 1;
Here we imagine a dead lock will appear ! But we set isc_tpb_wait to say to wait that the lock dispear.
BUT the probleme is that at the begining:
Transac_1 Is the first to do the job (FieldA = 12) Transac_2 wait (FieldA = 12) Transac 1 finish the job (FieldA = 13) Transac_2 ok i can do the job ... ooh no i can't because fieldA <> what it was at the begining 12 :(
It's how Firebird work actually (if I make no mistake).
But you resume it:
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.
Exactly what I want to say... but with instruction like FieldA = FieldA + 1 it's normally doesn't really matter because the transaction saw like you say $ 80 (after the commit of the first transac and the release of the lock) and can do the math ...
Dmitry Yemanov answers:
As far as I understand, your problem can be worked around in two different ways in Firebird:
- by using read-committed no-record-version isolation mode
- by locking the records explicitly (SELECT WITH LOCK) before updating