Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13331

FK DELETE CASCADE does not honor innodb_lock_wait_timeout

    Details

      Description

      Note: I'm not sure if it's intentional, cannot find anything about it in MySQL changelog, bug base or manual. If it's intentional, please feel free to close or move to documentation if necessary.

      In InnoDB 5.7, if a row which needs to be deleted through DELETE CASCADE is locked, the DELETE hangs seemingly forever – neither innodb_lock_wait_timeout nor lock_wait_timeout have any effect.

      In InnoDB 5.6 (MariaDB 10.1, MySQL 5.6) it works as expected.

      Test case

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
       
      CREATE TABLE t2 (
        id INT NOT NULL PRIMARY KEY,
        ref_id INT NOT NULL DEFAULT 0,
        f INT NULL,
        FOREIGN KEY (ref_id) REFERENCES t1 (id) ON DELETE CASCADE
      ) ENGINE=InnoDB;
       
      INSERT INTO t1 VALUES (1),(2);
      INSERT INTO t2 VALUES (1,1,10),(2,2,20);
       
      --connect (con1,localhost,root,,)
      BEGIN;
      UPDATE t2 SET f = 11 WHERE id = 1;
       
       
      --connection default
      SET innodb_lock_wait_timeout=1;
      SET lock_wait_timeout=2;
       
      --error ER_LOCK_WAIT_TIMEOUT
      DELETE FROM t1 WHERE id = 1;
       
      --connection con1
      COMMIT;
      --disconnect con1
       
      --connection default
       
      DROP TABLE t2, t1;
      

      Result with 10.1 or MySQL 5.6

      CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
      CREATE TABLE t2 (
      id INT NOT NULL PRIMARY KEY,
      ref_id INT NOT NULL DEFAULT 0,
      f INT NULL,
      FOREIGN KEY (ref_id) REFERENCES t1 (id) ON DELETE CASCADE
      ) ENGINE=InnoDB;
      INSERT INTO t1 VALUES (1),(2);
      INSERT INTO t2 VALUES (1,1,10),(2,2,20);
      BEGIN;
      UPDATE t2 SET f = 11 WHERE id = 1;
      SET innodb_lock_wait_timeout=1;
      SET lock_wait_timeout=2;
      DELETE FROM t1 WHERE id = 1;
      ERROR HY000: Lock wait timeout exceeded; try restarting transaction
      COMMIT;
      DROP TABLE t2, t1;
      bug.t4                                   [ pass ]   3621
      

      Result with 10.2 or MySQL 5.7

      CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
      CREATE TABLE t2 (
      id INT NOT NULL PRIMARY KEY,
      ref_id INT NOT NULL DEFAULT 0,
      f INT NULL,
      FOREIGN KEY (ref_id) REFERENCES t1 (id) ON DELETE CASCADE
      ) ENGINE=InnoDB;
      INSERT INTO t1 VALUES (1),(2);
      INSERT INTO t2 VALUES (1,1,10),(2,2,20);
      connect  con1,localhost,root,,;
      BEGIN;
      UPDATE t2 SET f = 11 WHERE id = 1;
      connection default;
      SET innodb_lock_wait_timeout=1;
      SET lock_wait_timeout=2;
      DELETE FROM t1 WHERE id = 1;
      

      the test case hangs after that. With MySQL the last line, DELETE, is not printed, it's normal and signifies the same result.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                marko Marko Mäkelä
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: