Description:
The mysqldump manual said like this,
This backup acquires a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH statement is issued, the MySQL server may get stalled until those statements finish.
https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
But not only 'long updating statement' but also 'long query'.
This case also should be mentioned on 'FLUSH TABLES' manual.
https://dev.mysql.com/doc/refman/5.6/en/flush.html#flush-tables
https://dev.mysql.com/doc/refman/5.7/en/flush.html#flush-tables
https://dev.mysql.com/doc/refman/5.8/en/flush.html#flush-tables
How to repeat:
(1) Prepare four mysql command line tool (A,B,C,D).
(2) With [A] run query like this.
select *, sleep(100) from mysql.user;
(3) With [B] run querh like this.
flush tables;
(4) with [C] you can see the (2)'s query prevent flush tables;
mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+--------------
-----------+--------------------------------------+
| Id | User | Host | db | Command | Time | State
| Info |
+----+-----------------+-----------------+------+---------+------+--------------
-----------+--------------------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 8227 | Waiting on em
pty queue | NULL |
| 8 | root | localhost:51652 | NULL | Query | 41 | Waiting for t
able flush | flush tables |
| 10 | root | localhost:51682 | test | Query | 45 | User sleep
| select *, sleep(100) from mysql.user |
| 11 | root | localhost:51712 | NULL | Query | 0 | starting
| show processlist |
+----+-----------------+-----------------+------+---------+------+--------------
-----------+--------------------------------------+
4 rows in set (0.00 sec)
(5) With [C] run this query.
select * from mysql.user;
(6) With [D] run show processlist. (5)'s query is prevented by flush tables;
mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+--------------
-----------+--------------------------------------+
| Id | User | Host | db | Command | Time | State
| Info |
+----+-----------------+-----------------+------+---------+------+--------------
-----------+--------------------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 8252 | Waiting on em
pty queue | NULL |
| 8 | root | localhost:51652 | NULL | Query | 66 | Waiting for t
able flush | flush tables |
| 10 | root | localhost:51682 | test | Query | 70 | User sleep
| select *, sleep(100) from mysql.user |
| 11 | root | localhost:51712 | NULL | Query | 13 | Waiting for t
able flush | select * from mysql.user |
| 12 | root | localhost:51713 | NULL | Query | 0 | starting
| show processlist |
+----+-----------------+-----------------+------+---------+------+--------------
-----------+--------------------------------------+
5 rows in set (0.00 sec)
Suggested fix:
Modify & add the behavior about these.
