MySQL connection close 后， mysql server上的行為是什么

本文著重講述的是通過 msql client 連接到 mysql server ，發起 update 、 select 操作(由于數據量非常大，所以 update、select 操作都很耗時，即在結果返回前我們有足夠的時間執行一些操作) 。
在客戶端分別嘗試執行

ctrl C 結束
關閉 mysql client 窗口
kill -9 mysql client 進程，當然這需要在另一個窗口進行

然后登陸 mysql server 執行 show processlist 和 select * from INNODB_TRX 看現象

用戶發起update：

用戶 ctrl C 或關閉 mysql client 窗口，mysql server 上的行為是一樣的，就是update 事務會立刻會滾，唯一的小區別是 show processlist 的結果：ctrl C 結束的話，show proceslist 中連接還在；關閉 mysql client 窗口的話，連接不存在了

mysql client

mysql> use robertdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> 
mysql> select count(*) from bigtable;
+----------+
| count(*) |
+----------+
| 10485760 |
+----------+
1 row in set (2.45 sec)
mysql>
mysql> update bigtable set name = concat(name, "a") ;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>

mysql server

用戶發起 update 操作后，登錄 server 執行 show processlist 和 INNODB_TRX

mysql> show processlist;
+-----+--------+-----------------------+--------------------+---------+------+----------+----------------------------------------------+
| Id  | User   | Host                  | db                 | Command | Time | State    | Info                                         |
+-----+--------+-----------------------+--------------------+---------+------+----------+----------------------------------------------+
| 172 | root   | localhost             | information_schema | Query   |    0 | starting | show processlist                             |
| 175 | robert | 111.202.148.190:20580 | robertdb           | Query   |   20 | updating | update bigtable set name = concat(name, "a") |
+-----+--------+-----------------------+--------------------+---------+------+----------+----------------------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> select * from INNODB_TRX \G
*************************** 1. row ***************************trx_id: 48631trx_state: RUNNINGtrx_started: 2025-05-27 13:48:30trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 1698104trx_mysql_thread_id: 175trx_query: update bigtable set name = concat(name, "a")trx_operation_state: fetching rowstrx_tables_in_use: 1trx_tables_locked: 1trx_lock_structs: 13326trx_lock_memory_bytes: 1499344trx_rows_locked: 1696898trx_rows_modified: 1684778trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
mysql>

2.用戶執行 ctrl C 后，登錄server 執行show processlist 和 INNODB_TRX： state 從 updating 變為了 query end

mysql> show processlist;
+-----+--------+-----------------------+--------------------+---------+------+-----------+----------------------------------------------+
| Id  | User   | Host                  | db                 | Command | Time | State     | Info                                         |
+-----+--------+-----------------------+--------------------+---------+------+-----------+----------------------------------------------+
| 172 | root   | localhost             | information_schema | Query   |    0 | starting  | show processlist                             |
| 175 | robert | 111.202.148.190:20580 | robertdb           | Query   |   36 | query end | update bigtable set name = concat(name, "a") |
+-----+--------+-----------------------+--------------------+---------+------+-----------+----------------------------------------------+
2 rows in set (0.00 sec)
mysql> 
mysql> select * from INNODB_TRX \G
*************************** 1. row ***************************trx_id: 48631trx_state: ROLLING BACKtrx_started: 2025-05-27 13:48:30trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 684362trx_mysql_thread_id: 175trx_query: update bigtable set name = concat(name, "a")trx_operation_state: rollbacktrx_tables_in_use: 1trx_tables_locked: 1trx_lock_structs: 20191trx_lock_memory_bytes: 2269392trx_rows_locked: 2337715trx_rows_modified: 664171trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)mysql>

3.等回滾完后，執行 show processlist 和 INNODB_TRX， Command 從 Query 變為了 Sleep , Time 繼續累加

mysql> show processlist;
+-----+--------+-----------------------+--------------------+---------+------+-----------+----------------------------------------------+
| Id  | User   | Host                  | db                 | Command | Time | State     | Info                                         |
+-----+--------+-----------------------+--------------------+---------+------+-----------+----------------------------------------------+
| 172 | root   | localhost             | information_schema | Query   |    0 | starting  | show processlist                             |
| 175 | robert | 111.202.148.190:20580 | robertdb           | Query   |   41 | query end | update bigtable set name = concat(name, "a") |
+-----+--------+-----------------------+--------------------+---------+------+-----------+----------------------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> show processlist;
+-----+--------+-----------------------+--------------------+---------+------+----------+------------------+
| Id  | User   | Host                  | db                 | Command | Time | State    | Info             |
+-----+--------+-----------------------+--------------------+---------+------+----------+------------------+
| 172 | root   | localhost             | information_schema | Query   |    0 | starting | show processlist |
| 175 | robert | 111.202.148.190:20580 | robertdb           | Sleep   |   45 |          | NULL             |
+-----+--------+-----------------------+--------------------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
mysql>
mysql> select * from INNODB_TRX;
Empty set (0.00 sec)
mysql>
mysql>

用戶在shell控制臺執行 kill -9 mysqlclient 進程號，登陸server 查看：發現最終事物會被執行完并提交

mysql client

mysql> use robertdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> 
mysql> select count(*) from bigtable;
+----------+
| count(*) |
+----------+
| 10485760 |
+----------+
1 row in set (2.45 sec)
mysql>
mysql> update bigtable set name=concat(name, "a") ;
[1]    3029 killed     mysql -h116.196.83.235 -p3306 -urobert -pxxx
?  ~

mysql server：

mysql> select * from bigtable limit 5;
+----+----------------------+------+--------------------+
| id | name                 | age  | email              |
+----+----------------------+------+--------------------+
|  1 | Jone1829488e9aaa     |   38 | test1@baomidou.com |
|  2 | Jackb6d920e9d2       |   39 | test2@baomidou.com |
|  3 | Tomc9e5955e81        |   40 | test3@baomidou.com |
|  4 | Sandy8220152054      |   41 | test4@baomidou.com |
|  5 | Billie4de56f25ac     |   40 | test5@baomidou.com |
+----+----------------------+------+--------------------+
10 rows in set (0.00 sec)
mysql>
mysql>
mysql>  show processlist;
+-----+--------+-----------------------+--------------------+---------+------+----------+--------------------------------------------+
| Id  | User   | Host                  | db                 | Command | Time | State    | Info                                       |
+-----+--------+-----------------------+--------------------+---------+------+----------+--------------------------------------------+
| 180 | root   | localhost             | information_schema | Query   |    0 | starting | show processlist                           |
| 194 | robert | 111.202.148.190:21030 | robertdb           | Query   |   83 | updating | update bigtable set name=concat(name, "a") |
+-----+--------+-----------------------+--------------------+---------+------+----------+--------------------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql> select * from INNODB_TRX \G
*************************** 1. row ***************************trx_id: 48643trx_state: RUNNINGtrx_started: 2025-05-27 14:30:13trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 7711233trx_mysql_thread_id: 194trx_query: update bigtable set name=concat(name, "a")trx_operation_state: updating or deletingtrx_tables_in_use: 1trx_tables_locked: 1trx_lock_structs: 94482trx_lock_memory_bytes: 10395856trx_rows_locked: 7671547trx_rows_modified: 7616751trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.01 sec)
mysql>
mysql>			
mysql>  隨著時間推移發現 update 執行完畢了，事務也提交了
mysql>
mysql>
mysql>  show processlist;
+-----+------+-----------+----------+---------+------+----------+------------------+
| Id  | User | Host      | db       | Command | Time | State    | Info             |
+-----+------+-----------+----------+---------+------+----------+------------------+
| 180 | root | localhost | robertdb | Query   |    0 | starting | show processlist |
+-----+------+-----------+----------+---------+------+----------+------------------+
1 row in set (0.00 sec)
mysql> 
mysql> select * from information_schema.INNODB_TRX \G
Empty set (0.00 sec)
mysql> 
mysql> select * from bigtable limit 5;
+----+-----------------------+------+--------------------+
| id | name                  | age  | email              |
+----+-----------------------+------+--------------------+
|  1 | Jone1829488e9aaaa     |   38 | test1@baomidou.com |
|  2 | Jackb6d920e9d2a       |   39 | test2@baomidou.com |
|  3 | Tomc9e5955e81a        |   40 | test3@baomidou.com |
|  4 | Sandy8220152054a      |   41 | test4@baomidou.com |
|  5 | Billie4de56f25aca     |   40 | test5@baomidou.com |
+----+-----------------------+------+--------------------+
5 rows in set (0.00 sec)
mysql>

用戶發起 select：

用戶 ctrl C 或關閉 mysql client 窗口，mysql server 上的行為是一樣的，就是update 事務會立刻會滾，唯一的小區別是 show processlist 的結果：ctrl C 結束的話，show proceslist 中連接還在；關閉 mysql client 窗口的話，連接不存在了

mysql client：

mysql> select count(*) from bigtable;
+----------+
| count(*) |
+----------+
| 10485760 |
+----------+
1 row in set (2.45 sec)
mysql>
mysql> select count(*) from  (select distinct(name) from bigtable) as a;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>
mysql>

mysql server:

1.用戶發起 select 操作后，登錄 server 執行 show processlist 和 INNODB_TRX

mysql> show processlist;
+-----+--------+-----------------------+----------+---------+------+--------------+------------------------------------------------------------------+
| Id  | User   | Host                  | db       | Command | Time | State        | Info                                                             |
+-----+--------+-----------------------+----------+---------+------+--------------+------------------------------------------------------------------+
| 215 | root   | localhost             | NULL     | Query   |    0 | starting     | show processlist                                                 |
| 218 | robert | 111.202.148.190:21049 | robertdb | Query   |    7 | Sending data | select count(*) from  (select distinct(name) from bigtable) as a |
+-----+--------+-----------------------+----------+---------+------+--------------+------------------------------------------------------------------+
2 rows in set (0.00 sec)mysql> select * from information_schema.INNODB_TRX \G
*************************** 1. row ***************************trx_id: 421631104673616trx_state: RUNNINGtrx_started: 2025-05-27 17:07:36trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 0trx_mysql_thread_id: 218trx_query: select count(*) from  (select distinct(name) from bigtable) as atrx_operation_state: NULLtrx_tables_in_use: 1trx_tables_locked: 0trx_lock_structs: 0trx_lock_memory_bytes: 1136trx_rows_locked: 0trx_rows_modified: 0trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 1
trx_autocommit_non_locking: 1
1 row in set (0.00 sec)mysql>

2.用戶執行 ctrl C 后，登錄server 執行show processlist 和 INNODB_TRX： Command 從 Query 變成了 Sleep，也就是說 select 立刻不執行了

mysql>
mysql> show processlist;
+-----+--------+-----------------------+----------+---------+------+----------+------------------+
| Id  | User   | Host                  | db       | Command | Time | State    | Info             |
+-----+--------+-----------------------+----------+---------+------+----------+------------------+
| 215 | root   | localhost             | NULL     | Query   |    0 | starting | show processlist |
| 218 | robert | 111.202.148.190:21049 | robertdb | Sleep   |   13 |          | NULL             |
+-----+--------+-----------------------+----------+---------+------+----------+------------------+
2 rows in set (0.00 sec)mysql> select * from information_schema.INNODB_TRX \G
Empty set (0.00 sec)mysql>

用戶在shell控制臺執行 kill -9 mysqlclient 進程號，登陸server 查看：發現select SQL 語句還會繼續執行，直到執行完畢

mysqlclient

mysql> select count(*) from bigtable;+----------+| count(*) |+----------+| 10485760 |+----------+1 row in set (2.45 sec)mysql>
mysql> select count(*) from  (select distinct(name) from bigtable) as a;
[1]    15935 killed     mysql -h116.196.83.235 -p3306 -urobert -pxxx
?  ~

mysql server

1.用戶發起 select 操作后，登錄 server 執行 show processlist 和 INNODB_TRX

mysql> show processlist;
+-----+--------+-----------------------+----------+---------+------+--------------+------------------------------------------------------------------+
| Id  | User   | Host                  | db       | Command | Time | State        | Info                                                             |
+-----+--------+-----------------------+----------+---------+------+--------------+------------------------------------------------------------------+
| 215 | root   | localhost             | NULL     | Query   |    0 | starting     | show processlist                                                 |
| 218 | robert | 111.202.148.190:21049 | robertdb | Query   |   17 | Sending data | select count(*) from  (select distinct(name) from bigtable) as a |
+-----+--------+-----------------------+----------+---------+------+--------------+------------------------------------------------------------------+
2 rows in set (0.00 sec)mysql> select * from information_schema.INNODB_TRX \G
*************************** 1. row ***************************trx_id: 421631104673616trx_state: RUNNINGtrx_started: 2025-05-27 17:18:15trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 0trx_mysql_thread_id: 218trx_query: select count(*) from  (select distinct(name) from bigtable) as atrx_operation_state: NULLtrx_tables_in_use: 1trx_tables_locked: 0trx_lock_structs: 0trx_lock_memory_bytes: 1136trx_rows_locked: 0trx_rows_modified: 0trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 1
trx_autocommit_non_locking: 1
1 row in set (0.00 sec)mysql>

2.用戶在shell控制臺執行 kill -9 mysqlclient 進程號，登陸server 查看: 發現 SQL 還在繼續執行

mysql> show processlist;
+-----+--------+-----------------------+----------+---------+------+--------------+------------------------------------------------------------------+
| Id  | User   | Host                  | db       | Command | Time | State        | Info                                                             |
+-----+--------+-----------------------+----------+---------+------+--------------+------------------------------------------------------------------+
| 215 | root   | localhost             | NULL     | Query   |    0 | starting     | show processlist                                                 |
| 218 | robert | 111.202.148.190:21049 | robertdb | Query   |   89 | Sending data | select count(*) from  (select distinct(name) from bigtable) as a |
+-----+--------+-----------------------+----------+---------+------+--------------+------------------------------------------------------------------+
2 rows in set (0.00 sec)mysql> select * from information_schema.INNODB_TRX \G
*************************** 1. row ***************************trx_id: 421631104673616trx_state: RUNNINGtrx_started: 2025-05-27 17:18:15trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 0trx_mysql_thread_id: 218trx_query: select count(*) from  (select distinct(name) from bigtable) as atrx_operation_state: NULLtrx_tables_in_use: 1trx_tables_locked: 0trx_lock_structs: 0trx_lock_memory_bytes: 1136trx_rows_locked: 0trx_rows_modified: 0trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 1
trx_autocommit_non_locking: 1
1 row in set (0.00 sec)
mysql>
mysql>
mysql>  隨著時間推移，直到 select SQL 執行完畢
mysql>
mysql>
mysql> show processlist;
+-----+------+-----------+------+---------+------+----------+------------------+
| Id  | User | Host      | db   | Command | Time | State    | Info             |
+-----+------+-----------+------+---------+------+----------+------------------+
| 215 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+-----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)mysql> select * from information_schema.INNODB_TRX \G
Empty set (0.01 sec)mysql>

結論：

對于mysql client 的 ctrl C 結束或關閉 mysql client 窗口， mysql server 會立刻感知到這個行為，從而對 update SQL進行回滾、對 select SQL立刻就不執行了；
但是對于 kill -9 mysqclient 進程，mysql server會繼續執行正在執行的SQL，對 update SQL 會繼續執行直到提交、對 select SQL會繼續執行直到結束。