I have a database where some of the tables have parent --> child --> grandchild relationships through foreign keys as shown below.
CREATE TABLE Parent ( ParentID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT ,SomeValue VARCHAR(100) NOT NULL ); CREATE TABLE Child ( ChildID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT ,ParentID INT UNSIGNED NOT NULL ,SomeValue VARCHAR(100) NOT NULL ); ALTER TABLE Child ADD CONSTRAINT FK_Child_ParentID FOREIGN KEY (ParentID) REFERENCES Parent (ParentID) ON DELETE CASCADE; CREATE TABLE GrandChild ( GrantChildID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT ,ChildID INT UNSIGNED NOT NULL ,SomeValue VARCHAR(100) NOT NULL ); ALTER TABLE GrandChild ADD CONSTRAINT FK_GrandChild_ChildID FOREIGN KEY (ChildID) REFERENCES Child (ChildID) ON DELETE CASCADE;
Some sample data I have tested with is:
INSERT INTO Parent (SomeValue) VALUES ('ParentA'), ('ParentB'), ('ParentC'), ('ParentD'); INSERT INTO Child (ParentID, SomeValue) VALUES (1, 'ChildA'), (1, 'ChildB'), (2, 'ChildC'), (3, 'ChildD'), (3, 'ChildE'); INSERT INTO GrandChild (ChildID, SomeValue) VALUES (1, 'GrandChildA'), (2, 'GrandChildB'), (2, 'GrandChildB'), (3, 'GrandChildD'), (3, 'GrandChildE'), (3, 'GrandChildF');
And the stored procedures I'm using to delete records are:
DELIMITER // CREATE PROCEDURE DeleteChild(IN Child_ID INT) BEGIN DELETE FROM Child WHERE ChildID = Child_ID; END // DELIMITER ; DELIMITER // CREATE PROCEDURE DeleteParent(IN Parent_ID INT) BEGIN DELETE FROM Parent WHERE ParentID = Parent_ID; END // DELIMITER ;
After having done some testing I've discovered a data integrity problem where under certain circumstances it's possible for a parent record to be deleted without it's children and grandchildren records also being deleted, even though I have the ON DELETE CASCADE option enabled. I've managed to replicate the issue and the following steps result in it occurring.
Start a long-running statement that inserts a large number of records into the table 'GrandChild'. I've been trying to simulate large insert jobs so I'm running the following statement which takes several minutes if there are around 1 million records:
INSERT INTO GrandChild (ChildID, SomeValue) SELECT ChildID, SomeValue FROM GrandChild;
While the previous statement is running, issue another statement to delete a record from the table 'Child' that has grandchildren records in 'GrandChild'. I've tested this by calling the proc DeleteChild(1). The statement hangs and eventually times out after 50 seconds (the default innodb_lock_wait_timeout value) with an error 1205 after failing to get a lock on 'GrandChild'.
Before the previous statement times out, issue another statement that deletes a record from the table 'Parent' that also has child and grandchild records. I've testing this by calling proc DeleteParent(1).
The ordering looks something like the following:
What happens here is that as soon as the statement in step 2 times out, the statement in step 3 completes but it does so having only deleted the matching records in the table 'Parent' but not any of the record's children or grandchildren, leaving me with orphan records.
Can someone please explain to me why this might be happening?
I'm using MariaDB 10.2.11 with InnoDB and most of the settings I'm using are the defaults. Let me know if any extra information is needed to help debug this.