> On Apr 1, 2015, at 5:51 AM, Tim Ward tdw@... [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