MySQL找出未提交事务信息的方法分享

2020-01-18 20:47:45

字体：大中小

来源：转载

供稿：网友

前阵子，我写了一篇博客“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.commandFROM 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 sec2 lock struct(s), heap size 360, 8 row lock(s), undo log entries 1MySQL 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: 11trx_started: 2019-06-12 23:36:131 row in set (0.03 sec) ERROR: No query specified mysql>