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

更新時間：2019年06月13日 09:30:00 作者：瀟湘隱者

這篇文章主要給大家介紹了關于MySQL如何找出未提交事務信息的相關資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用MySQL具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧

前陣子，我寫了一篇博客“ORACLE中能否找到未提交事務的SQL語句”，那么在MySQL數(shù)據(jù)庫中，我們能否找出未提交事務執(zhí)行的SQL語句或未提交事務的相關信息呢？

實驗驗證了一下，如果一個會話(連接)里面有一個未提交事務，然后不做任何操作，那么這個線程處于Sleep狀態(tài)

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值?；旧蠠o法找到未提交事務的SQL語句，MySQL內部關于事務的信息不是很細，甚至可以說有點簡潔。我甚至無法定位到在那個表上發(fā)生了鎖。只能看到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）找到未提交事務執(zhí)行的SQL

如下測試所示，會話（連接 connection_id=11）中執(zhí)行了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>

另外一個會話（連接）執(zhí)行了一個更新記錄的操作。此時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;

我們在另外的會話中，執(zhí)行下面SQL就可以查到未提交事務最后執(zhí)行的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>