Re: [firebird-support] Scope of uniqueness constraint?
Hello Tim, 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) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! 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.)
Re: [firebird-support] Scope of uniqueness constraint?
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
