07.09.2019 19:35, Ann Harrison wrote:
...
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.
So, to fix this we need to repeat validation of master record in context of
client transaction,
correct ? Of course, it should be made for concurrency (snapshot) transaction
only and if current
state of master record is not visible for client transaction.
Regards,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
