[MDEV-13331] FK DELETE CASCADE does not honor innodb_lock_wait_timeout Created: 2017-07-15  Updated: 2018-10-24  Resolved: 2017-08-15

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.2.2
Fix Version/s: 10.2.8

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: upstream

Issue Links:
Problem/Incident
causes MDEV-15219 FOREIGN KEY CASCADE or SET NULL opera... Closed
Relates
relates to MDEV-17541 KILL QUERY during lock wait in FOREIG... Closed

 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.

 Comments   
Comment by Marko Mäkelä [ 2017-08-15 ]

This looks like a regression introduced by a bug fix in MySQL 5.7.8. The bug is that row_ins_check_foreign_constraint() keeps returning DB_LOCK_WAIT even after a timeout occurred, instead of the error code DB_LOCK_WAIT_TIMEOUT.

The following patch fixes the problem:

@@ -1921,19 +1914,11 @@ row_ins_check_foreign_constraint(
 
 
 		thr->lock_state = QUE_THR_LOCK_NOLOCK;
 
 
-		DBUG_PRINT("to_be_dropped",
 
-			   ("table: %s", check_table->name.m_name));
 
-		if (check_table->to_be_dropped) {
 
-			/* The table is being dropped. We shall timeout
 
-			this operation */
 
-			err = DB_LOCK_WAIT_TIMEOUT;
 
-
 
-			goto exit_func;
 
-		}
 
-
 
+		err = check_table->to_be_dropped
 
+			? DB_LOCK_WAIT_TIMEOUT
 
+			: trx->error_state;
 
 	}
 
 
-
 
 exit_func:
 
 	if (heap != NULL) {
 
 		mem_heap_free(heap);

Comment by Jan Lindström (Inactive) [ 2017-08-15 ]

ok to push.

Generated at Thu Feb 08 08:04:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.