MySQL找出未提交事务的SQL代码实例解析

本篇文章小编给大家分享一下MySQL找出未提交事务的SQL代码实例解析，文章代码介绍的很详细，小编觉得挺不错的，现在分享给大家供大家参考，有需要的小伙伴们可以来看看。

--准备测试环境数据（实验环境为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_idG;

如下截图所示：

在会话窗口（连接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_idG; 
*************************** 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

测试、折腾了好久，终于搞出了一个几乎完美的SQL：

SELECT @dt_ts:=UNIX_TIMESTAMP(NOW());
SELECT 
 @dt_timer:=MAX(sh.TIMER_START)
FROM performance_schema.threads AS t
INNER JOIN performance_schema.events_statements_history AS sh
ON t.`THREAD_ID`=sh.`THREAD_ID`
WHERE t.PROCESSLIST_ID=CONNECTION_ID();
 
 
SELECT sh.current_schema  AS database_name
 ,t.thread_id
 ,it.trx_mysql_thread_id  AS connection_id
 ,it.trx_id
 ,sh.event_id
 ,it.trx_state
 ,REPLACE(REPLACE(REPLACE(sh.`SQL_TEXT`,'n',' '),'r',' '),'t',' ') AS executed_sql
 ,it.trx_started
 ,FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-sh.TIMER_START)/1000000000000 AS SIGNED)) AS start_time
 ,FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-sh.TIMER_END) /1000000000000 AS SIGNED)) AS end_time
 ,(sh.TIMER_END-sh.TIMER_START)/1000000000000 AS used_seconds
 ,sh.TIMER_WAIT/1000000000000 AS wait_seconds
 ,sh.LOCK_TIME/1000000000000 AS lock_seconds
 ,sh.ROWS_AFFECTED AS affected_rows
 ,sh.ROWS_SENT AS send_rows
FROM performance_schema.threads AS t
INNER JOIN information_schema.innodb_trx it ON it.trx_mysql_thread_id = t.processlist_id 
INNER JOIN performance_schema.events_statements_history AS sh
 ON t.`THREAD_ID`=sh.`THREAD_ID`
WHERE t.PROCESSLIST_ID IN (
  SELECT 
   p.ID AS conn_id
  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.COMMAND='Sleep'
   AND p.TIME>2
  )
AND sh.TIMER_START<@dt_timer
AND FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-sh.TIMER_START)/1000000000000 AS SIGNED)) >=it.trx_started
ORDER BY it.trx_id ASC, sh.TIMER_START ASC;

它能找出未提交事务的SQL，简单测试完全没有问题，同时构造几个未提交事务测试也OK。但是上面SQL由三个SQL组成，总感觉有点别扭，研究了一下，可以改造成下面SQL。

SELECT sh.current_schema  AS database_name
 ,t.thread_id
 ,it.trx_mysql_thread_id AS connection_id
 ,it.trx_id
 ,sh.event_id
 ,it.trx_state
 ,REPLACE(REPLACE(REPLACE(sh.`SQL_TEXT`,'n',' '),'r',' '),'t',' ') AS executed_sql
 ,it.trx_started
 ,DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME') - sh.TIMER_START*10e-13 second) AS start_time
 ,DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME') - sh.TIMER_END*10e-13 second) AS end_time
 ,(sh.TIMER_END-sh.TIMER_START)/1000000000000 AS used_seconds
 ,sh.TIMER_WAIT/1000000000000 AS wait_seconds
 ,sh.LOCK_TIME/1000000000000 AS lock_seconds
 ,sh.ROWS_AFFECTED AS affected_rows
 ,sh.ROWS_SENT AS send_rows
FROM performance_schema.threads AS t
INNER JOIN information_schema.innodb_trx it ON it.trx_mysql_thread_id = t.processlist_id 
INNER JOIN performance_schema.events_statements_history AS sh
 ON t.`THREAD_ID`=sh.`THREAD_ID`
WHERE t.PROCESSLIST_ID IN (
  SELECT 
   p.ID AS conn_id
  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.COMMAND='Sleep'
   AND p.TIME>2
  )
AND sh.TIMER_START<(SELECT VARIABLE_VALUE*1000000000000 FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME')
AND DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME') - sh.TIMER_START*10e-13 second) >=it.trx_started
ORDER BY it.trx_id ASC, sh.TIMER_START ASC;

注意：performance_schema.global_status是MySQL 5.7引入的，如果数据库是MySQL 5.6的话，可以用INFORMATION_SCHEMA.GLOBAL_STATUS替换SQL中的performance_schema.global_status

那么是否这个SQL就一定完美了呢？ 网友MSSQL123反馈在一个测试环境中，发现上面这种SQL居然查不出任何数据，因为FROM_UNIXTIME(@dt_ts-CAST((@dt_timer-sh.TIMER_START)/1000000000000 AS SIGNED)) >=it.trx_started会将数据过滤掉，检查发现对应的trx_started值都大于start_time

-------------------------------------------------------------------------补充资料--------------------------------------------------------------------------------------------------------

那么同一个测试环境，隔天测试的时候，突然发现上面第一个SQL正常，第二个SQL就由于不同的写法，start_time有细微的差别，导致查询结果完全不同（第二个SQL语句精确到毫秒，对比的时候由于偏差过滤掉一批数据）