Tim Ward wrote:
If there's some code which says "if there isn't an EXXON then create one", and there's a uniqueness constraint such that there can't be two Bxs, then "clearly" the above code can go wrong, in that the following cannot succeed:
(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?
(Yes I do know that's what generators are for.)
Martijn Tonies answers:
Firebird constraints are evaluated immediately for the transaction.
So that means that during constraint validation, it doesn't "see" other transactions, and due to it's "immediate" evaluation, you can't temporarily ignore validation for your transaction either. (eg: "increase all PK values by 1")
Ann W. Harrison answers:
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.