3

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.

  1. 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;

  2. 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'.

  3. 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:

enter image description here

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.

Thanks!

  • And both transactions 1 and 3 are committed without errors? And what isolation level is used? – ypercubeᵀᴹ Dec 30 '17 at 12:15
  • Yes that's correct – user3607758 Dec 30 '17 at 12:17
  • 1
    I've been able to reproduce the problem on mariadb 10.2.11, although calling DeleteChild(1) returned quite quickly with ERROR 1213 (40001): Deadlock found when trying to get lock; .... I also attempted to trigger the problem with mysql 5.7.20, but this was unsuccessful, i.e. referential integrity was maintained as no Child or Parent was deleted. However, the two calls to the Delete procs did seem to lock up the database, and eventually I got ERROR 2013 (HY000): Lost connection to MySQL server during query on both calls. So seems like a bug, could be reported at jira.mariadb.org – dbdemon Dec 31 '17 at 14:21
  • 1
    Use just one table. Use "self-joins". Have a parent_id that links to id. Put "cascade" code in your code, don't depend on FOREIGN KEYs. Use transactions. – Rick James Dec 31 '17 at 23:15
  • 1
    I think I may have found what seems like it could be a solution. By modifying the statement in DeleteParent to delete using joins onto all the child and grandchild records, the procedure return quickly with an error 1213 if the GrandChild table is locked, rather than deleting parent records and leaving orphans. – user3607758 Jan 1 '18 at 23:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.