DML breaks FLUSH TABLE WITH READ LOCK

[Philip Stoev]

Maybe similar to 1376747

If a concurrent DML arrives via the applier on a table that is locked, further queries on the table return "Deadlock found when trying to get lock". The problem is that this error, if it happened on the same thread that locked the table, causes the table to be silently unlocked, making potential future operations on the table, e.g. taking backup, unsafe.

This applies to FLUSH TABLE, LOCK TABLE, FLUSH TABLE FOR EXPORT.

Test case:

CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);

--connection node_2
FLUSH TABLES t1 WITH READ LOCK;
SELECT COUNT(*) = 1 FROM t1;
--error ER_TABLE_NOT_LOCKED_FOR_WRITE
INSERT INTO t1 VALUES (2); # This statement fails as expected with ER_TABLE_NOT_LOCKED_FOR_WRITE
--error ER_TABLE_NOT_LOCKED_FOR_WRITE
INSERT INTO t1 VALUES (3); Same here, the lock holds

--connection node_1
INSERT INTO t1 VALUES (4);

--connection node_2
--error ER_LOCK_DEADLOCK
INSERT INTO t1 VALUES (5); # This statement fails with ER_LOCK_DEADLOCK rather than ER_TABLE_NOT_LOCKED_FOR_WRITE
INSERT INTO t1 VALUES (6); # The table is now silently unlocked ,this statement succeeds.