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会继续执行直到结束。