Bug #117707 FLUSH TABLES WITH READ LOCK timeout, but doesn't release the locks
Submitted: 14 Mar 9:58 Modified: 14 Mar 12:29
Reporter: Tsubasa Tanaka (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:8.0.41, 8.4.4, 9.2.0 OS:Oracle Linux (8.10)
Assigned to: CPU Architecture:x86

[14 Mar 9:58] Tsubasa Tanaka
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.
[14 Mar 12:29] MySQL Verification Team
Hello tanaka-San,

Thank you for the report and feedback.

regards,
Umesh