TL;DR
FLUSH TABLES WITH READ LOCK は「今まさに実行中のクエリ」と競合する
- メタデータロックと違って「生きているトランザクション」ではなくて「今まさに実行中のクエリ」とだけ
WITH READ LOCK という名前ではあれど FLUSH TABLES なので SELECT とも競合する
FLUSH TABLES WITH READ LOCK は先に全体に GLOBAL read lock を置いてから1つずつテーブルを FLUSH するFLUSH TABLES WITH READ LOCK をタイムアウトさせてもセッションを閉じない限り FLUSH待ちロック は解放されない
FLUSH TABLES WITH READ LOCK は実行中のクエリと競合する。
t1, t2, t3のテーブルがあって、t2にすごく時間がかかるクエリを実行している場合、
mysql80 44> SHOW TABLES;
+--------------+
| Tables_in_d1 |
+--------------+
| t1 |
| t2 |
| t3 |
+--------------+
3 rows in set (0.00 sec)
mysql80 44> SELECT SLEEP(100) FROM t2;
あとからやってきた FLUSH TABLES WITH READ LOCK は噛まれる。
mysql80 57> SHOW PROCESSLIST;
+----+-----------------+------------------+------+---------+--------+------------------------+---------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+------------------+------+---------+--------+------------------------+---------------------------+
| 44 | root | localhost | d1 | Query | 12 | User sleep | SELECT SLEEP(100) FROM t2 |
| 57 | root | localhost | d1 | Query | 0 | init | SHOW PROCESSLIST |
+----+-----------------+------------------+------+---------+--------+------------------------+---------------------------+
mysql80 57> FLUSH TABLES WITH READ LOCK; --- 噛まれる
噛まれている FLUSH TABLES WITH READ LOCK を SHOW PROCESSLIST で見るとこんな感じで、 Waiting for table flush で待つ。
mysql80 61> SHOW PROCESSLIST;
+----+-----------------+------------------+------+---------+--------+-------------------------+-----------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+------------------+------+---------+--------+-------------------------+-----------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 178726 | Waiting on empty queue | NULL |
| 44 | root | localhost | d1 | Query | 72 | User sleep | SELECT SLEEP(100) FROM t2 |
| 57 | root | localhost | d1 | Query | 55 | Waiting for table flush | FLUSH TABLES WITH READ LOCK |
| 61 | root | localhost | d1 | Query | 0 | init | SHOW PROCESSLIST |
+----+-----------------+------------------+------+---------+--------+-------------------------+-----------------------------+
FLUSH TABLES WITH READ LOCK は最初(テーブルのFLUSHを始めるより前)に GLOBAL read lock( thd->global_read_lock.lock_global_read_lock(thd) を置く。これによって先にMySQL全体が書き込みをブロックされる。
この時点では、
| table |
read |
write |
| t1 |
OK |
Blocked(waiting for global read lock) |
| t2 |
Blocked(waiting for table flush) |
Blocked(waiting for global read lock) |
| t3 |
OK |
Blocked(waiting for global read lock) |
こうなる。
ところでこういう事態を防ぐために lock_wait_timeout があって FLUSH TABLES WITH READ LOCK がロック待ちになった時にタイムアウトさせられるんだけれど
mysql80 23> SET SESSION lock_wait_timeout = 1;
Query OK, 0 rows affected (0.01 sec)
mysql80 23> FLUSH TABLES WITH READ LOCK;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
何故かタイムアウトした後も FLUSH TABLES WITH READ LOCK の影響が収まらない。
mysql80 24> DELETE FROM t1; -- 噛まれる
mysql80 25> SELECT * FROM t2; -- 噛まれる
mysql80 26> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+-------+------------------------------+---------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+-------+------------------------------+---------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 22347 | Waiting on empty queue | NULL |
| 13 | root | localhost | d1 | Query | 98 | User sleep | SELECT SLEEP(100) FROM t2 |
| 23 | root | localhost | d1 | Sleep | 75 | | NULL | <---- これがFLUSH TABLES WITH READ LOCKがタイムアウトしたやつ
| 24 | root | localhost | d1 | Query | 15 | Waiting for global read lock | DELETE FROM t1 |
| 25 | root | localhost | d1 | Query | 6 | Waiting for table flush | SELECT * FROM t2 |
| 26 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST |
+----+-----------------+-----------+------+---------+-------+------------------------------+---------------------------+
思わぬ巻き込み事故に注意…というかこれバグっぽくない…？
【2025/03/20 21:14】
TL;DRには書いてあったけど、これに当たった場合は 「FLUSH TABLES WITH READ LOCKをブロックしたコネクション」をKILLする（クエリではなくてコネクションごとKILLする）
↑の例なら KILL 13 で復旧する。
【2025/03/14 18:59】
バグレポートしておいた
MySQL Bugs: #117707: FLUSH TABLES WITH READ LOCK timeout, but doesn't release the locks
https://bugs.mysql.com/bug.php?id=117707
