Thanks Ann! So far all the opinions are that the currently behavior is wrong (or inconsistent, at last).
I'll be glad if someone who has other RDBMS installed can compare how they behave with the exactly described scenario, and report the results back here. Please pay attention in the transaction isolations and make sure they are started/commit in the correct times. Thanks! Carlos http://www.firebirdnews.org FireBase - http://www.FireBase.com.br AH> Cheers, AH> Ann >> On Sep 6, 2019, at 8:24 AM, Mark Rotteveel <m...@lawinegevaar.nl> wrote: >> >>> On 6-9-2019 01:46, Carlos H. Cantu wrote: >>> I understand that there are other scenarios where the currently FK >>> behavior is correct and makes sense, for example, in the case of >>> avoiding deleting a master record with "commited but not visible >>> childs", AH> Yes. Unique, Primary Key, and Foreign Key constraints are AH> handled in a special omniscient mode to avoid concurrent, AH> incompatible changes. Triggers and check constraints operate in AH> the mode of the user transaction. >>> but for the reported example, the currently behavior looks >>> incorrect, and for people with business logic implemented in triggers, >>> it may/will lead to incorrect results. >> >> I think you're right. You should only be able to insert records that >> reference records that are visible to your transaction. Given Tx2 started >> before Tx1 committed, the effects from Tx1 aren't visible to your >> transaction. Your insert in Tx2 should fail as the master record from Tx1 >> doesn't exist from the perspective of Tx2. AH> Interesting. In the case of inserting a child, the master must AH> be visible to the transaction doing the insert. In the case of AH> deleting a master, the existence of a child - even if uncommitted must block the delete. >> >>> Does anyone knows if this behavior is following the Standard? >> >> I don't think this behaviour is correct in view of the standard, but I >> haven't looked it up. >> AH> No, this behavior is not standard compliant. AH> Good luck, AH> Ann Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel