我要评论前阵子，我写了一篇博客“oracle中能否找到未提交事务的sql语句”， 那么在mysql数据库中，我们能否找出未提交事务执行的sql语句或未提交事务的相关信息呢？
实验验证了一下，如果一个会话(连接)里面有一个未提交事务，然后不做任何操作，那么这个线程处于sleep状态
mysql> select connection_id() from dual;
+-----------------+
| connection_id() |
+-----------------+
| 6 |
+-----------------+
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>
在另外一个会话（连接）里面，查询这个超过10秒未提交事务的详细信息：
select t.trx_mysql_thread_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 > 10
and p.command = 'sleep'\g
如上截图所示，trx_query 为null值。基本上无法找到未提交事务的sql语句，mysql内部关于事务的信息不是很细，甚至可以说有点简洁。我甚至无法定位到在那个表上发生了锁。只能看到trx_row_locked、trx_row_modified、trx_started等信息。使用show engine innodb status也是如此，只能看到一些基本信息
mysql> show engine innodb status;
---transaction 1282583, active 11937 sec
2 lock struct(s), heap size 360, 8 row lock(s), undo log entries 1
mysql thread id 6, os thread handle 0x7f8da2de3700, query id 190 localhost root
如果未提交的事务，阻塞了其它会话，那么有可能（仅仅是存在可能性，很多场景也不能找到位提交事务的相关sql）找到未提交事务执行的sql
如下测试所示，会话（连接 connection_id=11）中执行了delete操作，但是未提交事务
mysql> set session autocommit=0;
query ok, 0 rows affected (0.00 sec)
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 11 |
+-----------------+
1 row in set (0.01 sec)
mysql> delete from kkk where id=1;
query ok, 1 row affected (0.00 sec)
mysql>
另外一个会话（连接）执行了一个更新记录的操作。此时sql将被阻塞。
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 13 |
+-----------------+
1 row in set (0.00 sec)
mysql>
mysql> update kkk set id=100 where id=1;
我们在另外的会话中，执行下面sql就可以查到未提交事务最后执行的sql。
mysql> select r.trx_id waiting_trx_id,
-> r.trx_mysql_thread_id waiting_thread,
-> r.trx_query waiting_query,
-> b.trx_id blocking_trx_id,
-> b.trx_mysql_thread_id blocking_thread,
-> b.trx_query blocking_query
-> from information_schema.innodb_lock_waits w
-> inner join information_schema.innodb_trx b
-> on b.trx_id = w.blocking_trx_id
-> inner join information_schema.innodb_trx r
-> on r.trx_id = w.requesting_trx_id;
+----------------+----------------+----------------------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query |
+----------------+----------------+----------------------------------+-----------------+-----------------+----------------+
| 2830 | 13 | update kkk set id=100 where id=1 | 2825 | 11 | null |
+----------------+----------------+----------------------------------+-----------------+-----------------+----------------+
1 row in set (0.00 sec)
mysql> select a.sql_text,
-> c.id,
-> d.trx_started
-> from performance_schema.events_statements_current a
-> join performance_schema.threads b
-> on a.thread_id = b.thread_id
-> join information_schema.processlist c
-> on b.processlist_id = c.id
-> join information_schema.innodb_trx d
-> on c.id = d.trx_mysql_thread_id
-> where c.id=11
-> order by d.trx_started\g;
*************************** 1. row ***************************
sql_text: delete from kkk where id =1
id: 11
trx_started: 2019-06-12 23:36:13
1 row in set (0.03 sec)
error:
no query specified
mysql>
总结：基本上mysql只能找到未提交事务的基本信息，例如trx_mysql_thread_id等。某些场景下，我们几乎没有方法找出未提交事务执行的sql等详细信息。搞不清未提交事务做了什么操作！
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请 点击进行留言回复，万千网友为您解惑！
网友评论