> On Sep 7, 2019, at 8:58 AM, Karol Bieniaszewski <liviusliv...@poczta.onet.pl> > wrote: > > You have right, there is a bug and the big one! > I suppose that index of Foreign Key is not validated by existence of value in > the record itself and its (the record version) transaction numer is not > compared to snapshot number. >
It's also an old bug, probably dating to the implementation of foreign keys in InterBase. The obvious implementation - validate the foreign key in the context of the client transaction - fails miserably in snapshot mode when the parent record is deleted by one transaction and a matching child record is inserted by a second concurrent transaction. That leads to orphaned child records, which is very wrong. The next possible implementation is to use the same internal omniscient mode that maintains unique and primary key constraints. The omniscient mode sees the current state of the database is that there is a committed parent record that matches the proposed insert. That eliminates the orphan child problem, but introduces the problem Carlos discovered. The transaction that stores the child record can "see" the master record for the purpose of validating the relationship between the two records, but for no other purpose. That's slightly obscure case - updating the master when a child is stored tends to create a hotspot - but it's certainly legitimate. Good luck, Ann
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
