最近遇到一个案例，很多查询被阻塞没有返回结果，使用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>
转载请注明原文链接：MySQL线程处于Waiting for table flush的分析
