Bug #117707 FLUSH TABLES WITH READ LOCK timeout, but doesn't release the locks

• Submitted: 14 Mar 9:58
• Modified: 20 Mar 12:26
• Reporter: Tsubasa Tanaka (OCA)
• Status: Verified
• Category: MySQL Server: Locking
• Severity: S2 (Serious)
• Version: 8.0.41, 8.4.4, 9.2.0
• OS: Oracle Linux (8.10)
• 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

[19 Mar 14:49] Jean-François Gagné

A complement on this bug: when the connection that did the FTWRL ends, the locks are released (at least for 8.0.30, 8.0.41, 8.4.4 and 9.2.0).

I am not implying the bug is not serious, just that its impact is not as bad for clients disconnecting after FTWRL times-out (which btw, is a nice workaround for this bug).

[20 Mar 12:24] Tsubasa Tanaka

Thank you, I confirmed global read lock removed when FTWRL connection ends.

By the way, even if FTWRL connection had gone, queries blocked by "waiting for table flush" are still there.
Workaround to resolve those queries is `KILL the connection which had blocked FTWRL` .
(In my example, KILL 9 statement)

I hope this workaround reaches someone who faced this situation.

[20 Mar 12:26] Tsubasa Tanaka

But you're right, flush-status is not lock.
Is there any better expression?