Description:
FLUSH TABLES WITH READ LOCK doesn't release Global read lock and table flushing status even if FLUSH TABLES WITH READ LOCK is timed out.
How to repeat:
### Prepare
CREATE DATABASE test;
CREATE TABLE test.t1 (num int);
INSERT INTO test.t1 VALUES (1);
CREATE TABLE test.t2 (num int);
INSERT INTO test.t2 VALUES (2);
## Connection1
SELECT SLEEP(50) FROM test.t2; -- Simulate long-running SELECT query
## Connection2
SET lock_wait_timeout = 1;
FLUSH TABLES WITH READ LOCK; -- This is blocked by long-running SELECT and timeout
## Connection3
SELECT * FROM test.t2; -- This is blocked by "waiting for table flush"
## Connection4
DELETE FROM test.t1; -- This is blocked by "waiting for global read lock"
mysql84 10> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+------+------------------------------+---------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------------+---------------------------------------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 64 | Waiting on empty queue | NULL |
| 9 | root | localhost | NULL | Query | 15 | User sleep | SELECT SLEEP(50) FROM test.t2 -- Simulate long-running SELECT query |
| 10 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST |
| 11 | root | localhost | NULL | Query | 8 | Waiting for table flush | SELECT * FROM test.t2 -- This is blocked by "waiting for table flush" |
| 12 | root | localhost | NULL | Query | 4 | Waiting for global read lock | DELETE FROM test.t1 -- This is blocked by "waiting for global read lock" |
+----+-----------------+-----------+------+---------+------+------------------------------+---------------------------------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)
Suggested fix:
Release Global read lock and table flushing status after FLUSH TABLES WITH READ LOCK timed out.
