当前位置： 首页 > news >正文

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

news 2025/5/30 15:26:41

本文着重讲述的是通过 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

  1. 用户发起 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会继续执行直到结束。

http://www.xdnf.cn/news/667369.html

相关文章：

  • 【Elasticsearch】调用_flush api会调用_refresh 吗？
  • 火山引擎声音复刻
  • 安全生产例题
  • 知识图谱：AI时代语义认知的底层重构逻辑
  • 游戏引擎学习第314天:将精灵拆分成多个层
  • U 盘数据恢复全攻略
  • 说说 Kotlin 中的 Any 与 Java 中的 Object 有何异同？
  • Go 应用中的 Redis 连接与操作
  • NLua性能对比：C#注册函数 vs 纯Lua实现
  • Nginx--手写脚本压缩和切分日志（也适用于docker）
  • 【Linux】进程状态优先级
  • 【QT】在QT6中读取文件的方法
  • 私服 nexus 之间迁移 npm 仓库
  • Debian 11之解决daemon.log与syslog文件占用空间过大问题
  • pyspark实践
  • [yolov11改进系列]基于yolov11引入感受野注意力卷积RFAConv的python源码+训练源码
  • 手机收不到WiFi，手动输入WiFi名称进行连接不不行，可能是WiFi频道设置不对
  • Matlab实现LSTM-SVM时间序列预测，作者：机器学习之心
  • 链表：数据结构的灵动舞者
  • Linux系统-基本指令（3）
  • 智能体赋能效率，企业知识库沉淀价值：UMI企业智脑的双轮驱动！
  • 【Quest开发】空间音频的使用
  • [AI]大模型MCP快速入门及智能体执行模式介绍
  • HJ25 数据分类处理【牛客网】
  • 小白成长之路-Linux程序与进程（一）
  • Linux 使用 Docker 安装 Milvus的两种方式
  • 记忆术-汉字部首编码记忆
  • APP广告变现，开发者如何判断对接的广告SDK安全合规？
  • CQF预备知识：一、微积分 -- 1.6.1 不定积分详解
  • ctf.show pwn入门 堆利用-前置基础 pwn142