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
 ,t.trx_is_read_only    AS trx_is_read_only
 ,t.trx_autocommit_non_locking   AS trx_autocommit_non_locking
 ,e.event_name     AS event_name
 ,e.timer_wait / 1000000000000   AS timer_wait
 ,e.sql_text 
FROM information_schema.innodb_trx t, 
 performance_schema.events_statements_current e, 
 performance_schema.threads c 
WHERE t.trx_mysql_thread_id = c.processlist_id 
 AND e.thread_id = c.thread_id\G;

如下截图所示：

在会话窗口（连接ID=38）继续执行下面SQL:"select * from t;"。如下所示

mysql> set session autocommit=0;
Query OK, 0 rows affected (0.01 sec)
 
mysql> delete from kkk where id =1;
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t;
+------+
| a |
+------+
| test |
+------+
1 row in set (0.00 sec)
 
mysql>

在会话窗口（连接ID=39）上继续执行下面SQL，你会发现捕获的是事务最后执行的SQL语句“select * from t”

mysql> 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
 -> ,t.trx_is_read_only    AS trx_is_read_only
 -> ,t.trx_autocommit_non_locking   AS trx_autocommit_non_locking
 -> ,e.event_name     AS event_name
 -> ,e.timer_wait / 1000000000000   AS timer_wait
 -> ,e.sql_text 
 -> FROM information_schema.innodb_trx t, 
 -> performance_schema.events_statements_current e, 
 -> performance_schema.threads c 
 -> WHERE t.trx_mysql_thread_id = c.processlist_id 
 -> AND e.thread_id = c.thread_id\G; 
*************************** 1. row ***************************
  connection_id: 38
   trx_id: 7981581
   trx_state: RUNNING
  trx_started: 2020-12-03 15:39:37
  trx_run_time(s): 237
 trx_requested_lock_id: NULL
 trx_operation_state: NULL
  trx_tables_in_use: 0
  trx_tables_locked: 1
  trx_rows_locked: 4
 trx_isolation_level: REPEATABLE READ
  trx_is_read_only: 0
trx_autocommit_non_locking: 0
  event_name: statement/sql/select
  timer_wait: 0.0002
   sql_text: select * from t
1 row in set (0.00 sec)
 
ERROR: 
No query specified

也是说上面SQL只能获取未提交事务最后执行的一个SQL语句，生产环境中，一个事务中往往不止一个SQL语句，而是多个SQL语句的集合。如果想将一个未提交事务里面所有执行过的SQL找出来怎么办呢？其实在MySQL中还是有办法的。下面SQL语句就可以找出或者

SELECT trx.trx_mysql_thread_id AS processlist_id
 ,sc.thread_id
 ,trx.trx_started
 ,TO_SECONDS(now())-TO_SECONDS(trx_started) AS trx_last_time 
 ,pc1.user
 ,pc1.host
 ,pc1.db
 ,sc.SQL_TEXT AS current_sql_text
 ,sh.history_sql_test
FROM INFORMATION_SCHEMA.INNODB_TRX trx
INNER JOIN INFORMATION_SCHEMA.processlist pc1 ON trx.trx_mysql_thread_id=pc1.id
INNER JOIN performance_schema.threads th on th.processlist_id = trx.trx_mysql_thread_id
INNER JOIN performance_schema.events_statements_current sc ON sc.THREAD_ID = th.THREAD_ID
INNER JOIN (
  SELECT thread_id AS thread_id, GROUP_CONCAT(SQL_TEXT SEPARATOR ';') AS history_sql_test
  FROM performance_schema.events_statements_history 
  GROUP BY thread_id 
  ) sh ON sh.thread_id = th.thread_id
WHERE trx_mysql_thread_id != connection_id()
 AND TO_SECONDS(now())-TO_SECONDS(trx_started) >= 0 ;

但是这两个SQL有个问题：它会找出当前连接历史上所有执行过的SQL（当然前提是这些SQL还保存在performance_schema.events_statements_history表中），也就是说这个SQL，不仅查询出未提交事务所有执行过的脚本，而且会查询出很多历史SQL脚本，例如，这个会话（连接）之前事务的SQL语句，而且还有一个比较头疼的问题：这里不好区分哪些SQL对应哪些事务。需要借助其他信息来甄别。比较费时费力。如下截图所示。

因为只有information_schema.innodb_trx系统表中包含事务的开始时间（trx_started），其它系统表没有跟事务相关的时间，只能借助performance_schema.events_statements_history中的TIMER_START字段来获取事件的SQL开始执行的时间，而这个时间必然是小于或等于对应事务的开始时间（trx_started）的。所以从这个突破口来找出未提交事务的所有SQL，下面是关于TIMER_START等字段的详细介绍。

关于TIMER_START，TIMER_END，TIMER_WAIT的介绍如下：

TIMER_START，TIMER_END，TIMER_WAIT：事件的时间信息。这些值的单位是皮秒（万亿分之一秒）。

TIMER_START和TIMER_END值表示事件的开始时间和结束时间。

TIMER_WAIT是事件执行消耗的时间（持续时间）

如果事件未执行完成，则TIMER_END为当前时间，TIMER_WAIT为当前为止所经过的时间（TIMER_END - TIMER_START）。

如果监视仪器配置表setup_instruments中对应的监视器TIMED字段被设置为 NO，则不会收集该监视器的时间信息，那么对于该事件采集的信息记录中，TIMER_START，TIMER_END和TIMER_WAIT字段值均为NULL

阅读剩余部分