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

MySQL中经常遇到事务中的SQL正在执行或执行完成后未提交，如何找出对应的SQL？

成都创新互联主要从事成都做网站、网站建设、网页设计、企业做网站、公司建网站等业务。立足成都服务阳明,10年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:18982081108

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 |
+----------+----------------            

                文章题目：MySQL如何查看未提交的事务SQL
                

                文章转载：http://www.shufengxianlan.com/qtweb/news31/236731.html
            

            

                网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等
                                

            

                广告
                
            

            

                声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：
                创新互联

猜你还喜欢下面的内容

静态网站知识

同城分类信息

香港云服务器租用　　 　简阳服务器托管　　 　公司网站建设　　 　汕尾凯敏网站　　 　网站制作价格　　 　网站SEO优化　　 　成都网站制作　　 　成都封阳台　　 　微信公众号开发　　 　证企宝企业　　 　微信公众号开发　　 　响应式网站设计　　 　亚克力背部发光字　　 　成都网站建设　　 　网络营销推广　　 　成都网站维护　　 　外贸营销网站建设　　 　雅安做网站　　 　四川绵阳机房　　 　金融行业网站建设方案