kill flush tables的思考

資料庫發生一個現象：
在mysqldump備份的時候，會執行flush tables語句，再連線上的dml語句就會等待Waiting for table flush。是因為一個長時間執行的select導致flush tables語句的等待。這時把 flush tables語句kill 掉， dml語句依然還在。並且還是等待相同的事件。把長時間執行的select kill之後，回到正常狀態。

重現：

session 1 : ---這個需要3分鐘+執行完
select url,count(*) from t_test a, t1 b where a.help_category_id = b.id group by url;

session 2 :
flush tables ;

view session: --通過這個session檢視程式

mysql> show full processlist;
+----+-----------------+-----------+------+---------+---------+-------------------------+---------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+---------+-------------------------+---------------------------------------------------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 1024079 | Waiting on empty queue | NULL |
| 14 | root | localhost | test | Query | 18 | Sending data | select url,count(*) from t_test a, t1 b where a.help_category_id = b.id group by url |
| 16 | root | localhost | test | Query | 0 | init | show full processlist |
| 18 | root | localhost | test | Sleep | 59 | | NULL |
| 20 | root | localhost | NULL | Query | 11 | Waiting for table flush | flush tables |
+----+-----------------+-----------+------+---------+---------+-------------------------+---------------------------------------------------------------------------------------+

session 3 :
mysql> insert into t1 values (20000);

view session:

mysql> show full processlist;
+----+-----------------+-----------+------+---------+---------+-------------------------+---------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+---------+-------------------------+---------------------------------------------------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 1024100 | Waiting on empty queue | NULL |
| 14 | root | localhost | test | Query | 39 | Sending data | select url,count(*) from t_test a, t1 b where a.help_category_id = b.id group by url |
| 16 | root | localhost | test | Query | 0 | init | show full processlist |
| 18 | root | localhost | test | Query | 16 | Waiting for table flush | insert into t1 values (20000) |
| 20 | root | localhost | NULL | Query | 32 | Waiting for table flush | flush tables |
+----+-----------------+-----------+------+---------+---------+-------------------------+---------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> kill 20;
Query OK, 0 rows affected (0.00 sec)

mysql> show full processlist;
+----+-----------------+-----------+------+---------+---------+-------------------------+---------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+---------+-------------------------+---------------------------------------------------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 1024113 | Waiting on empty queue | NULL |
| 14 | root | localhost | test | Query | 52 | Sending data | select url,count(*) from t_test a, t1 b where a.help_category_id = b.id group by url |
| 16 | root | localhost | test | Query | 0 | init | show full processlist |
| 18 | root | localhost | test | Query | 29 | Waiting for table flush | insert into t1 values (20000) |
+----+-----------------+-----------+------+---------+---------+-------------------------+---------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> show full processlist;
+----+-----------------+-----------+------+---------+---------+-------------------------+---------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+---------+-------------------------+---------------------------------------------------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 1024208 | Waiting on empty queue | NULL |
| 14 | root | localhost | test | Query | 147 | Sending data | select url,count(*) from t_test a, t1 b where a.help_category_id = b.id group by url |
| 16 | root | localhost | test | Query | 0 | init | show full processlist |
| 18 | root | localhost | test | Query | 124 | Waiting for table flush | insert into t1 values (20000) |
+----+-----------------+-----------+------+---------+---------+-------------------------+---------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> \s
--------------
mysql Ver 14.14 Distrib 5.6.20

這時在別的session中操作別的表是沒有問題的，但是和長時間執行的select相關的表都是有問題的。

照我的理解：
dml在等待table flush，那麼我把flush 語句kill了， dml語句應該可以正常執行才對。
檢視文件：
FLUSH TABLES
Closes all open tables, forces all tables in use to be closed, and flushes the query cache. FLUSH
TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement.

在網上搜尋：
https://bugs.mysql.com/bug.php?id=44884 Bug #44884 KILL of FLUSH TABLES does not remove TDC version lock on tables in use

但對於為什麼出現這種情況，還不是太理解。
why????

轉載請註明源出處
QQ 273002188 歡迎一起學習
QQ 群 236941212

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-1794426/，如需轉載，請註明出處，否則將追究法律責任。

kill flush tables的思考

相關文章