lobolo2000 wrote:
Shouldn't FB2 delete master records after detail records if an 'on delete cascade' clause is used (otherwise the referential integrity would be violated)?
An example to illustrate the point:
CREATE EXCEPTION EX_ERROR 'Master record should be deleted after detail record';
CREATE TABLE T1(
U1 BIGINT PRIMARY KEY);
CREATE TABLE T2(
U2 BIGINT PRIMARY KEY,
U3 BIGINT REFERENCES T1(U1) ON DELETE CASCADE);
SET TERM ^;
CREATE TRIGGER TR2 FOR T2 ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
IF (NOT EXISTS(SELECT U1 FROM T1 WHERE T1.U1=1)) THEN
EXCEPTION EX_ERROR;
END^
SET TERM ;^
COMMIT;
INSERT INTO T1 VALUES(1);
INSERT INTO T2 VALUES(1,1);
DELETE FROM T1;
COMMIT;
The exception is triggered! Any ideas?
Dimitry Sibiryakov answers:
Nope. This order prevents conflicts with other transactions.
And lobolo2000 reply:
But shouldn't the 'on delete cascade' mechanism be equivalent to a 'before delete' trigger that deletes detail records? The way it seems to be implemented with is the equivalent of an 'after delete' trigger, unless I am missing something.
And Dimitry Sibiryakov answers:
No, because referential integrity can be violated inside one statement. Even more: according to standard, it can be violated even inside of transaction. Fortunatelly, FB does not support deferred constraints.
Ann W. Harrison steps in:
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.