Bug #87084 FK DELETE CASCADE does not honor innodb_lock_wait_timeout

• Submitted: 15 Jul 2017 20:25
• Modified: 16 Jul 2017 5:19
• Reporter: Elena Stepanova
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.7, 5.7.18
• OS: Any
• CPU Architecture: Any

[15 Jul 2017 20:25] Elena Stepanova

Description:
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 it works (fails with a wait timeout) as expected.

I'm not sure if it's an intentional change, cannot find anything about it anywhere.

How to repeat:
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;

[16 Jul 2017 5:19] MySQL Verification Team

Hello Elena,

Thank you for the report and test case.
Verified as described with 5.7.18 build.

Thanks,
Umesh