> On Apr 1, 2015, at 5:51 AM, Tim Ward t...@telensa.com [firebird-support] > <firebird-support@yahoogroups.com> wrote: > > > (1) Transaction 1 - check for EXXON, find it doesn't exist > (2) Transaction 1 - create EXXON > (3) Transaction 2 - check for EXXON, find it doesn't exist (because it > can't see the one created by transaction 1) > (4) Transaction 2 - create EXXON > (5) Transaction 1 - commit > (6) Transaction 2 - commit > > This fails, as one would expect, due to the violation of the uniqueness > constraint. But my question is: does it fail at point (4), because the > uniqueness constraint is somehow active/visible/whatever across > transactions, or does it fail at point (6), because the uniqueness > constraint only takes account of committed stuff?
In a WAIT transaction, Transaction 2 will stall after step 4 and receive an error after step 5. That avoids a possible live lock that could occur if Transaction 1 fails between step 2 and step 5. In some pathological cases, the two transactions could kill each other perpetually. In a NO WAIT transaction (80% certainty) Transaction 2 gets an error on step 4, without waiting for Transaction 1 to commit. In no case will Transaction 2 proceed beyond step 4 unless Transaction 1 rolls back. Firebird knows there's a problem. In the WAIT case, it stalls the second transaction until the first finishes. Good luck, Ann