MySQL kill會話不起作用？

背景

在一次日常測試中發現，kill 一個會話後，SQL語句依然在執行並沒終止；被kill的會話重新連線並繼續執行原來的SQL語句。

測試

本次測試基於MySQL 8.0.27

1.建立測試表

create table t1 (id int, name  varchar(30));   
insert into t1 values (1,'a'),(2,'b');

2.開啟3個會話

session1：開啟一個事務不提交

mysql> use test
Database changed
mysql> 
mysql> 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

session2：執行DDL語句

mysql> use test
Database changed
mysql> 
mysql> 
mysql> rename table t1 to t2;

session3：kill session2

mysql> show processlist;
+-----+------+---------+---------+--------------------------------+----------------------+
| Id  | db   | Command | Time    | State                          | Info                 |
+-----+------+---------+---------+--------------------------------+----------------------+
|   6 | NULL | Daemon  | 4399013 | Waiting on empty queue         | NULL                 |
| 132 | test | Sleep   |     232 |                                | NULL                 |
| 134 | test | Query   |     123 | Waiting for table metadata lock| rename table t1 to t2|
| 135 | test | Query   |       0 | init                           | show processlist     |
+-----+------+---------+---------+--------------------------------+----------------------+
4 rows in set (0.00 sec)

mysql> kill 134;
Query OK, 0 rows affected (0.01 sec)
#為了排版，表格欄位略有刪減，具體資訊請看圖片

session2：session2重新連線，並且繼續執行DDL語句，仍處於鎖等待狀態

mysql> rename table t1 to t2; 
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    136
Current database: test

session3：檢視會話資訊

mysql> show processlist;
+-----+------+---------+---------+--------------------------------+----------------------+
| Id  | db   | Command | Time    | State                          | Info                 |
+-----+------+---------+---------+--------------------------------+----------------------+
|   6 | NULL | Daemon  | 4399260 | Waiting on empty queue         | NULL                 |
| 132 | test | Sleep   |     479 |                                | NULL                 |
| 135 | test | Query   |       0 | init                           | show processlist     |
| 136 | test | Query   |     193 | Waiting for table metadata lock| rename table t1 to t2|
+-----+------+---------+---------+--------------------------------+----------------------+
4 rows in set (0.00 sec)
#為了排版，表格欄位略有刪減，具體資訊請看圖片

可以看到， kill session2 後，session2 重新連線並且繼續執行SQL

session1：提交事務

mysql> commit;   
Query OK, 0 rows affected (0.01 sec)

session2：執行成功

mysql> use test
Database changed
mysql> 
mysql> 
mysql> rename table t1 to t2; 
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    136
Current database: test

Query OK, 0 rows affected (8 min 38.00 sec)

透過上述測試，可以看到明明執行了 kill 命令，但是依然沒有達到我們想要的效果，似乎 kill 命令沒有生效一樣。

經過查詢資料發現，由於透過MySQL客戶端登入，--reconnect 重新連線選項預設是開啟的，該選項在每次連線丟失時都會進行一次重新連線嘗試；因此在kill session2 後，session2重新連線並再次執行之前的SQL語句，導致感覺 kill 命令沒有生效。

  --reconnect         Reconnect if the connection is lost. Disable with
                      --disable-reconnect. This option is enabled by default.
                      (Defaults to on; use --skip-reconnect to disable.)

解決

可以透過以下2種方式避免上述問題的發生：

1.執行kill query 命令

KILL QUERY終止連線當前正在執行的語句，但保持連線本身不變

session3：執行 KILL QUERY 命令

mysql> show processlist;
+-----+------+---------+---------+--------------------------------+----------------------+
| Id  | db   | Command | Time    | State                          | Info                 |
+-----+------+---------+---------+--------------------------------+----------------------+
|   6 | NULL | Daemon  | 4401560 | Waiting on empty queue         | NULL                 |
| 132 | test | Sleep   |      11 |                                | NULL                 |
| 135 | test | Query   |       0 | init                           | show processlist     |
| 137 | test | Query   |       3 | Waiting for table metadata lock| rename table t1 to t2|
+-----+------+---------+---------+--------------------------------+----------------------+
4 rows in set (0.00 sec)

mysql> 
mysql> kill query 137;
Query OK, 0 rows affected (0.00 sec)
#為了排版，表格欄位略有刪減，具體資訊請看圖片

session2：

mysql> rename table t1 to t2;   
ERROR 1317 (70100): Query execution was interrupted

可以看到session2執行的語句已經被終止了，達到了我們想要的效果。

2.登入mysql客戶端時加--skip-reconnect選項

--skip-reconnect 表示當連線丟失時不會進行重新連線的嘗試

session2：登入時加 --skip-reconnect 選項

shell> mysql -uroot -p -h127.0.0.1  -P3306 --skip-reconnect

session3：執行 kill 命令

mysql> show processlist;
+-----+------+---------+---------+--------------------------------+----------------------+
| Id  | db   | Command | Time    | State                          | Info                 |
+-----+------+---------+---------+--------------------------------+----------------------+
|   6 | NULL | Daemon  | 4402073 | Waiting on empty queue         | NULL                 |
| 132 | test | Sleep   |     524 |                                | NULL                 |
| 135 | test | Query   |       0 | init                           | show processlist     |
| 139 | test | Query   |       4 | Waiting for table metadata lock| rename table t1 to t2|
+-----+------+---------+---------+--------------------------------+----------------------+
4 rows in set (0.00 sec)

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

session2:

mysql> rename table t1 to t2;
ERROR 2013 (HY000): Lost connection to MySQL server during query

可以看到session2的會話連線已經被終止，並且沒有自動重新連線，達到了我們想要的效果。

總結

1. 透過MySQL客戶端登入時，會話重新連線的選項 --reconnect 預設是開啟的，如果要禁止重新連線可在登入時新增 --skip-reconnect

2. KILL CONNECTION 與 KILL 相同，它在終止連線正在執行的任何語句後，再終止會話連線。

3. KILL QUERY 終止連線當前正在執行的語句，但保持連線本身不變。

參考連結

https://dev.mysql.com/doc/refman/8.0/en/kill.html

https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html