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 -A Database 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: 48631 trx_state: RUNNING trx_started: 2025-05-27 13:48:30 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 1698104 trx_mysql_thread_id: 175 trx_query: update bigtable set name = concat(name, "a") trx_operation_state: fetching rows trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 13326 trx_lock_memory_bytes: 1499344 trx_rows_locked: 1696898 trx_rows_modified: 1684778 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_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: 48631 trx_state: ROLLING BACK trx_started: 2025-05-27 13:48:30 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 684362 trx_mysql_thread_id: 175 trx_query: update bigtable set name = concat(name, "a") trx_operation_state: rollback trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 20191 trx_lock_memory_bytes: 2269392 trx_rows_locked: 2337715 trx_rows_modified: 664171 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_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 -A Database 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: 48643 trx_state: RUNNING trx_started: 2025-05-27 14:30:13 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 7711233 trx_mysql_thread_id: 194 trx_query: update bigtable set name=concat(name, "a") trx_operation_state: updating or deleting trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 94482 trx_lock_memory_bytes: 10395856 trx_rows_locked: 7671547 trx_rows_modified: 7616751 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_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: 421631104673616 trx_state: RUNNING trx_started: 2025-05-27 17:07:36 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 218 trx_query: select count(*) from (select distinct(name) from bigtable) as a trx_operation_state: NULL trx_tables_in_use: 1 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory_bytes: 1136 trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_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: 421631104673616 trx_state: RUNNING trx_started: 2025-05-27 17:18:15 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 218 trx_query: select count(*) from (select distinct(name) from bigtable) as a trx_operation_state: NULL trx_tables_in_use: 1 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory_bytes: 1136 trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_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: 421631104673616 trx_state: RUNNING trx_started: 2025-05-27 17:18:15 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 218 trx_query: select count(*) from (select distinct(name) from bigtable) as a trx_operation_state: NULL trx_tables_in_use: 1 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory_bytes: 1136 trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_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会继续执行直到结束。
免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们。
