Why do you insert master and detail record in separate transaction? If you do master and detail in separate transaction then tx2 should not be started before tx1, tx2 start MUST wait for tx1 SUCCESSFUL commit, otherwise your business logis FLAWED.
This scenario is looks like "Dirty read" : https://en.wikipedia.org/wiki/Isolation_(database_systems)#Dirty_reads. You leak uncommied (for tx2) information. -----Eredeti üzenet----- Feladó: Carlos H. Cantu [mailto:lis...@warmboot.com.br] Küldve: 2019. szeptember 6., péntek 1:47 Címzett: firebird-devel@lists.sourceforge.net Tárgy: [Firebird-devel] Inserts and FKs Recently I had a real situation that drove me crazy for several days while trying to find what actually happened. After lots of thinking, checking the auditing logs and chatting with Vlad, the only scenario that could explain what happened is: TableA (master) TableB (detail) There is FK between TableB and TableA tx1 starts tx1 inserts master record in TableA tx2 starts (snapshot isolation) tx1 commits tx2 inserts a detail record in TableB Trigger on TableB tries to update master record, since the record is still not visible for this snapshot, update has null effect tx2 commits (no error) The detail insert does not fail with broken FK because FK checking is out of transaction control, but the update ran by the trigger had no effect because the master record was not visible for the update. >From the consistency point of view, in this example, if the trigger could not >see the record and modify it, the FK validation should fail too. IMHO, if the >transaction isolation doesn't allow the record to be seen (at trigger level) >it should not be seen at the FK validation level too. 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", 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. Does anyone knows if this behavior is following the Standard? Any comments? []s Carlos http://www.firebirdnews.org Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel __________ Information from ESET Mail Security, version of virus signature database 19974 (20190905) __________ The message was checked by ESET Mail Security. http://www.eset.com __________ Information from ESET Mail Security, version of virus signature database 19976 (20190906) __________ The message was checked by ESET Mail Security. http://www.eset.com Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
