Dimitry Sibiryakov wrote:

>>> Nope. This order prevents conflicts with other transactions.
>>
>>Shouldn't the 'on delete cascade' mechanism be equivalent to a 'before
>>delete' trigger that deletes detail records?

Let me expand slightly on Dimitry's explanation. The challenge
of foreign key constraints is maintaining consistency when
several transactions are changing the master and slave records
simultaneously. Databases that use locks for concurrency
control typically lock the master record before inserting
child records, and lock the child table - or at least a range
of the child table - before deleting the master. Under
MVCC, that's not an option.

Instead, Firebird implements an internal operation to get
the most current version of a record by key value - whether
or not that record version is committed. By performing the
direct action (inserting a child or deleting a master) first,
then checking the most current in the other table, conflicts
are detected without locks.

Consider the case of transaction 1 which is trying to delete
a master record A with no child records, and transaction 2
which is trying to insert a child record A. Transaction 1
first deletes the master record, then checks to see if there
are any children. Transaction 2 inserts a child record A
then checks to see if there is a parent record. One or
the other - or perhaps both - will notice that the current
state is inconsistent with the constraint.

If transaction 1 deletes the master row and checks the child
table before transaction 2 starts adding the child record,
transaction 2 notices that the master record A was deleted
by a concurrent transaction - transaction 1. Transaction 2
then waits for transaction 1 to end - if it commits,
transaction 2 gets an error that rolls back the insert of
the child record. If transaction 1 rolls back, transaction
2 succeeds.

If transaction 2 stores its child and performs its checks
before transaction 1 begins its delete, transaction 1 first
deletes the master, then notices that transaction 2 has
added a child, waits and succeeds or fails as above.

If the two operations intersect, so that transaction 1
deletes the master while transaction 2 adds the child,
both transactions get errors on their checks, each tries
to wait for the other, and the lock manager detects a
deadlock and selects one to receive an error.

If the check is done before the change is made, two
conflicting operations could both succeed.

I'm not entirely certain that the particular case you
raise - a cascading delete - is affected by this logic.
I think that the operation works correctly if the child
records are deleted first, then the master, then the
check that no new child records have been inserted.

Regards,


Ann