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

<p>很久之前曾经总结过一篇博客“<a href="https://www.jb51.net/article/162966.htm" rel="external nofollow" target="_blank">MySQL如何找出未提交事务信息</a>”，现在看来，这篇文章中不少知识点或观点都略显肤浅，或者说不够深入，甚至部分结论是错误的。下面重新探讨一下这个话题。那么我们还是以之前的例子来介绍。</p>
<p>--准备测试环境数据（实验环境为MySQL 8.0.18社区版）</p>
<div class="blockcode">
<pre class="brush:sql;">
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)</pre>
</div>
<p>在一个会话窗口（连接ID=38）执行下面SQL</p>
<div class="blockcode">
<pre class="brush: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> </pre>
</div>
<p>在另外一个会话窗口（连接ID=39）执行下面SQL</p>
<div class="blockcode">
<pre class="brush: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)</pre>
</div>
<p>虽然上面这个SQL找不出事务执行过的SQL，其实呢，MySQL中未提交事务的最后执行的一个SQL是可以通过下面脚本准确查找出来的。如下所示：</p>
<div class="blockcode">
<pre class="brush: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;</pre>
</div>
<p>如下截图所示：</p>
<p style="text-align: center"><a href="https://img2020.cnblogs.com/blog/73542/202012/73542-20201203230926913-1228981133.png" rel="external nofollow"><img alt="" src="https://beijingoptbbs.oss-cn-hangzhou.aliyuncs.com/jb/2426819-6296c5ad918253099496793e5b977ff5.png"></a></p>
<p>在会话窗口（连接ID=38）继续执行下面SQL:"select * from t;"。如下所示</p>
<div class="blockcode">
<pre class="brush:sql;">
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> </pre>
</div>
<p>在会话窗口（连接ID=39）上继续执行下面SQL，你会发现捕获的是事务最后执行的SQL语句“select * from t”</p>
<div class="blockcode">
<pre class="brush:sql;">
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