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
