MySQL线程处于Waitingfortableflush的分析

最近遇到一个案例，很多查询被阻塞没有返回结果，使用show processlist查看，发现不少MySQL线程处于Waiting for table flush状态，查询语句一直被阻塞，只能通过Kill进程来解决。那么我们先来看看Waiting for table flush的官方解释：https://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html

Waiting for table flush

The thread is executing FLUSH TABLES and is waiting for all threads to close their tables, or the thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.

This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

那么我们接下来模拟一下线程处于Waiting for table flush状态的情况，如所示：

在第一个会话连接（connection id=13）中，我们使用lock table 锁定表test。

mysql> use MyDB;

Database changed

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

| 13 |

+-----------------+

1 row in set (0.00 sec)

mysql> lock table test read;

Query OK, 0 rows affected (0.00 sec)

mysql>

在第二个会话连接（connection id=17）中，我们执行flush table 或 flush table test 皆可。此时你会发现flush table处于阻塞状态。

mysql> use MyDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

| 17 |

+-----------------+

1 row in set (0.00 sec)

mysql> flush table test;

在第三个会话/连接中，当你切换到MyDB时，就会提示“You can turn off this feature to get a quicker startup with -A” ，此时处于阻塞状态。此时你退出会话，使用参数-A登录数据库后，你如果查询test表，就会处于阻塞状态（当然查询其它表不会被阻塞）。如下所示：

mysql> use MyDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

mysql> use MyDB;

Database changed

mysql> select * from test;

在第四个会话/连接，我们用show processlist查看到当前数据库所有连接线程状态，你会看到17、18都处于Waiting for table flush的状态。如下截图所示：

mysql> show processlist;

+----+------+-----------+------+---------+------+-------------------------+--------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+------+-----------+------+---------+------+-------------------------+--------------------+

| 13 | root | localhost | MyDB | Sleep | 90 | | NULL |

| 14 | root | localhost | NULL | Query | 0 | init | show processlist |

| 17 | root | localhost | MyDB | Query | 52 | Waiting for table flush | flush table test |

| 18 | root | localhost | MyDB | Query | 9 | Waiting for table flush | select * from test |

+----+------+-----------+------+---------+------+-------------------------+--------------------+

4 rows in set (0.00 sec)

mysql>

mysql> show processlist;

+----+------+-----------+------+---------+------+-------------------------+--------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+------+-----------+------+---------+------+-------------------------+--------------------+

| 13 | root | localhost | MyDB | Sleep | 90 | | NULL |

| 14 | root | localhost | NULL | Query | 0 | init | show processlist |

| 17 | root | localhost | MyDB | Query | 52 | Waiting for table flush | flush table test |

| 18 | root | localhost | MyDB | Query | 9 | Waiting for table flush | select * from test |

+----+------+-----------+------+---------+------+-------------------------+--------------------+

4 rows in set (0.00 sec)

mysql>

mysql>

mysql>

mysql>

mysql> show open tables where in_use >=1;

+----------+-------+--------+-------------+

| Database | Table | In_use | Name_locked |

+----------+-------+--------+-------------+

| MyDB | test | 1 | 0 |

+----------+-------+--------+-------------+

1 row in set (0.00 sec)

mysql> kill 17;

Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;

+----+------+-----------+------+---------+------+-------------------------+--------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+------+-----------+------+---------+------+-------------------------+--------------------+

| 13 | root | localhost | MyDB | Sleep | 442 | | NULL |

| 14 | root | localhost | NULL | Query | 0 | init | show processlist |

| 18 | root | localhost | MyDB | Query | 361 | Waiting for table flush | select * from test |

+----+------+-----------+------+---------+------+-------------------------+--------------------+

3 rows in set (0.00 sec)

mysql> kill 13;