文章详情

登录

短信预约-IT技能 免费直播动态提醒

选择考试省份

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL如何查看未提交的事务SQL

2024-11-30 18:44

关注

1. 查看正在执行的SQL

查看事务中正在执行的SQL方式有多种，例如

1.1 通过processlist查看

会话1：执行1个SQL

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


mysql> select sleep(20),now() ,id from test1;

会话2：开启另一个会话，查看对应的SQL

mysql> select  id ,info  from information_schema.processlist where info is not null;
+----+------------------------------------------------------------------------------+
| id | info                                                                         |
+----+------------------------------------------------------------------------------+
| 36 | select sleep(20),now() ,id from test1                                        |
| 37 | select  id ,info  from information_schema.processlist where info is not null |
+----+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
可以看到正在执行的SQL，包括自己的SQL的id及内容。

1.2 通过events_statements_current查看

会话1：执行1个SQL

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


mysql> select sleep(20),now() ,id from test1;

会话2：查看对应的SQL

mysql> select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id\G
*************************** 1. row ***************************
       id: 36
     info: select sleep(20),now() ,id from test1
thread_id: 76
 sql_text: select sleep(20),now() ,id from test1
*************************** 2. row ***************************
       id: 37
     info: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id
thread_id: 77
 sql_text: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id
2 rows in set (0.01 sec)

2. 方式对比

通过processlist和通过events_statements_current区别在于，processlist中能查到的SQL是正在运行的SQL，而运行结束的SQL是看不到的。

会话1：执行1个SQL

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


mysql> select sleep(2),now() ,id from test1;
+----------+---------------------+----+
| sleep(2) | now()               | id |
+----------+---------------------+----+
|        0 | 2023-01-03 22:01:09 |  1 |
+----------+---------------------+----+
1 row in set (2.00 sec)

此时查看事务情况

mysql> select  * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 421227264232664
                 trx_state: RUNNING
               trx_started: 2023-01-03 22:01:09
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 36
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1128
           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: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.00 sec)

其中trx_mysql_thread_id=36的会话正是我们会话1的线程id,但是我们看不到具体的SQL。

mysql> select *  from information_schema.processlist where id=36;
+----+------+-----------+--------+---------+------+-------+------+
| ID | USER | HOST      | DB     | COMMAND | TIME | STATE | INFO |
+----+------+-----------+--------+---------+------+-------+------+
| 36 | root | localhost | testdb | Sleep   |  177 |       | NULL |
+----+------+-----------+--------+---------+------+-------+------+
1 row in set (0.00 sec)

但是此时通过方式2就可以查到​

mysql> select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id\G
*************************** 1. row ***************************
       id: 36
     info: NULL
thread_id: 76
 sql_text: select sleep(2),now() ,id from test1
*************************** 2. row ***************************
       id: 37
     info: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id
thread_id: 77
 sql_text: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id
2 rows in set (0.00 sec)

注意：此时只能查到一个事务中的多条SQL的最后一个。

例如：

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


mysql> select sleep(2),now() ,id from test1;
+----------+---------------------+----+
| sleep(2) | now()               | id |
+----------+---------------------+----+
|        0 | 2023-01-03 22:01:09 |  1 |
+----------+---------------------+----+
1 row in set (2.00 sec)


mysql> select sleep(1),now() ,id from test1;
+----------+---------------------+----+
| sleep(1) | now()               | id |
+----------+---------------------+----+
|        0 | 2023-01-03 22:06:35 |  1 |
+----------+---------------------+----+

会话2查看结果

mysql> select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id\G
*************************** 1. row ***************************
       id: 36
     info: NULL
thread_id: 76
 sql_text: select sleep(1),now() ,id from test1
*************************** 2. row ***************************
       id: 37
     info: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id
thread_id: 77
 sql_text: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id
2 rows in set (0.00 sec)

可见，查到的是最后一个SQL了，如果事务手动commit提交了，则显示的是commit

1. 查看正在执行的SQL

查看事务中正在执行的SQL方式有多种，例如

1.1 通过processlist查看

会话1：执行1个SQL

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


mysql> select sleep(20),now() ,id from test1;

会话2：开启另一个会话，查看对应的SQL

mysql> select  id ,info  from information_schema.processlist where info is not null;
+----+------------------------------------------------------------------------------+
| id | info                                                                         |
+----+------------------------------------------------------------------------------+
| 36 | select sleep(20),now() ,id from test1                                        |
| 37 | select  id ,info  from information_schema.processlist where info is not null |
+----+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

可以看到正在执行的SQL，包括自己的SQL的id及内容

1.2 通过events_statements_current查看

会话1：执行1个SQL

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


mysql> select sleep(20),now() ,id from test1;

会话2：查看对应的SQL

mysql> select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id\G
*************************** 1. row ***************************
       id: 36
     info: select sleep(20),now() ,id from test1
thread_id: 76
 sql_text: select sleep(20),now() ,id from test1
*************************** 2. row ***************************
       id: 37
     info: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id
thread_id: 77
 sql_text: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id
2 rows in set (0.01 sec)

2. 方式对比

通过processlist和通过events_statements_current区别在于，processlist中能查到的SQL是正在运行的SQL，而运行结束的SQL是看不到的。

会话1：执行1个SQL

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


mysql> select sleep(2),now() ,id from test1;
+----------+---------------------+----+
| sleep(2) | now()               | id |
+----------+---------------------+----+
|        0 | 2023-01-03 22:01:09 |  1 |
+----------+---------------------+----+
1 row in set (2.00 sec)

此时查看事务情况

mysql> select  * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 421227264232664
                 trx_state: RUNNING
               trx_started: 2023-01-03 22:01:09
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 36
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1128
           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: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.00 sec)

其中trx_mysql_thread_id=36的会话正是我们会话1的线程id,但是我们看不到具体的SQL。

mysql> select *  from information_schema.processlist where id=36;
+----+------+-----------+--------+---------+------+-------+------+
| ID | USER | HOST      | DB     | COMMAND | TIME | STATE | INFO |
+----+------+-----------+--------+---------+------+-------+------+
| 36 | root | localhost | testdb | Sleep   |  177 |       | NULL |
+----+------+-----------+--------+---------+------+-------+------+
1 row in set (0.00 sec)

但是此时通过方式2就可以查到

mysql> select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id\G
*************************** 1. row ***************************
       id: 36
     info: NULL
thread_id: 76
 sql_text: select sleep(2),now() ,id from test1
*************************** 2. row ***************************
       id: 37
     info: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id
thread_id: 77
 sql_text: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id
2 rows in set (0.00 sec)

注意：此时只能查到一个事务中的多条SQL的最后一个。

例如：

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


mysql> select sleep(2),now() ,id from test1;
+----------+---------------------+----+
| sleep(2) | now()               | id |
+----------+---------------------+----+
|        0 | 2023-01-03 22:01:09 |  1 |
+----------+---------------------+----+
1 row in set (2.00 sec)


mysql> select sleep(1),now() ,id from test1;
+----------+---------------------+----+
| sleep(1) | now()               | id |
+----------+---------------------+----+
|        0 | 2023-01-03 22:06:35 |  1 |
+----------+---------------------+----+

会话2查看结果

mysql> select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id\G
*************************** 1. row ***************************
       id: 36
     info: NULL
thread_id: 76
 sql_text: select sleep(1),now() ,id from test1
*************************** 2. row ***************************
       id: 37
     info: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id
thread_id: 77
 sql_text: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id
2 rows in set (0.00 sec)

可见，查到的是最后一个SQL了，如果事务手动commit提交了，则显示的是commit。


来源：今日头条内容投诉

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题

  • 2024年上半年信息系统项目管理师第二批次真题及答案解析（完整版）

    难度     813人已做
    查看

  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看

  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看

  • 2024年上半年软考高项第一、二批次真题考点汇总（完整版）

    难度     435人已做
    查看

  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机

MySQL如何查看未提交的事务SQL

后端开发MySQLSQL2024-11-30

MySQL找出未提交事务的SQL实例浅析

后端开发2022-05-19

MySQL通过performance_schema定位未提交事务所执行的SQL

后端开发2024-04-02

mysql怎么查看事务是否自动提交

mysql怎么查看事务是否自动提交
后端开发2024-04-02

找出未提交的MySQL线程/事务

后端开发2024-04-02

mysql事务未提交导致锁等待如何解决

后端开发2024-04-02

oracle如何查看未结束的事务

oracle如何查看未结束的事务
后端开发2024-04-23

ORACLE中怎么找到未提交事务的SQL语句

后端开发2024-04-02

MySQL未提交事务造成的等待事件怎么解决

后端开发2024-04-02

怎么在MySQL中找出未提交的事务信息

后端开发2024-04-02

如何理解MySQL层事务提交流程

后端开发2024-04-02

如何查看MySQL当前的事务模式？

后端开发2023-10-22

如何理解mysql隐式提交事务transaction

后端开发2024-04-02

git如何查看提交的所有文件

后端开发2023-08-23

mysql如何查看事务是否开启

mysql如何查看事务是否开启
后端开发2024-04-02

mysql如何查看事务隔离级别

mysql如何查看事务隔离级别
后端开发2024-04-17

MySQL中常用查看锁和事务的SQL语句

后端开发2023-09-08

如何查看当前MySQL的事务隔离级别？

后端开发2023-10-22

如何使用Git查看提交修改的方法

后端开发2023-10-22

MySQL事务在Python中如何正确地进行管理和提交？（使用Python如何有效地管理并提交MySQL事务？）

MySQL事务在Python中如何正确地进行管理和提交？（使用Python如何有效地管理并提交MySQL事务？）
后端开发2024-04-02
位置：首页-资讯-后端开发
咦！没有更多了？去看看其它编程学习网 内容吧
首页课程
资料下载问答资讯