mysql 根据事务id 查找 sql_MySQL如何找出未提交事务的SQL浅析

  很久之前曾经总结过一篇博客“MySQL如何找出未提交事务信息”，现在看来，这篇文章中不少知识点或观点都略显肤浅，或者说不够深入，甚至说部分结论是错误的。下面重新探讨一下这个话题。那么我们还是以之前的例子来介绍。

--准备测试环境数据(实验环境为MySQL 8.0.18社区版)

mysql> create table kkk(id int , name varchar(12));
Query OK, 0 rows affected (0.34 sec)

mysql> insert into kkk values(1, 'kerry');
Query OK, 1 row affected (0.01 sec)

mysql> insert into kkk values(2, 'jerry');
Query OK, 1 row affected (0.00 sec)

mysql> insert into kkk values(3, 'ken');
Query OK, 1 row affected (0.00 sec)

mysql> 

mysql> create table t(a   varchar(10));
Query OK, 0 rows affected (0.47 sec)

mysql> insert into t values('test');
Query OK, 1 row affected (0.00 sec)

在一个会话窗口(连接ID=38)执行下面SQL

mysql> select connection_id() from dual;
+-----------------+
| connection_id() |
+-----------------+
|              38 |
+-----------------+
1 row in set (0.00 sec)

mysql> set session autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql>  delete from kkk where id =1;
Query OK, 1 row affected (0.00 sec)

mysql> 

在另外一个会话窗口(连接ID=39)执行下面SQL

mysql> SELECT t.trx_mysql_thread_id
    ->   ,t.trx_id
    ->       ,t.trx_state
    ->       ,t.trx_tables_in_use
    ->       ,t.trx_tables_locked
    ->       ,t.trx_query
    ->       ,t.trx_rows_locked 
    ->       ,t.trx_rows_modified
    ->       ,t.trx_lock_structs
    ->       ,t.trx_started
    ->       ,t.trx_isolation_level
    ->       ,p.time 
    ->       ,p.user
    ->       ,p.host
    ->       ,p.db
    ->       ,p.command
    -> FROM   information_schema.innodb_trx t 
    ->        INNER JOIN information_schema.processlist p 
    ->                ON t.trx_mysql_thread_id = p.id 
    -> WHERE  t.trx_state = 'RUNNING' 
    ->        AND p.time > 4 
    ->        AND p.command = 'Sleep'\G 
*************************** 1. row ***************************
trx_mysql_thread_id: 38
             trx_id: 7981581
          trx_state: RUNNING
  trx_tables_in_use: 0
  trx_tables_locked: 1
          trx_query: NULL
    trx_rows_locked: 4
  trx_rows_modified: 1
   trx_lock_structs: 2
        trx_started: 2020-12-03 15:39:37
trx_isolation_level: REPEATABLE READ
               time: 23
               user: root
               host: localhost
                 db: MyDB
            command: Sleep
1 row in set (0.00 sec)

虽然上面这个SQL找不出事务执行过的SQL，其实呢，MySQL中未提交事务的最后执行的一个SQL是可以通过下面脚本准确查找出来的。如下所示：

SELECT t.trx_mysql_thread_id                        AS connection_id
      ,t.trx_id                                     AS trx_id          
      ,t.trx_state                                  AS trx_state        
      ,t.trx_started                                AS trx_started     
      ,TIMESTAMPDIFF(SECOND,t.trx_started, now())   AS "trx_run_time(s)"  
      ,t.trx_requested_lock_id                      AS trx_requested_lock_id
      ,t.trx_operation_state                        AS trx_operation_state
      ,t.trx_tables_in_use                          AS trx_tables_in_use
      ,t.trx_tables_locked                          AS trx_tables_locked
      ,t.trx_rows_locked                            AS trx_rows_locked
      ,t.trx_isolation_level                        AS trx_isolation_level