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

时间:2021-12-11 23:41:36

前阵子,我写了一篇博客“ORACLE中能否找到未提交事务的SQL语句”, 那么在MySQL数据库中,我们能否找出未提交事务执行的SQL语句或未提交事务的相关信息呢?

实验验证了一下,如果一个会话(连接)里面有一个未提交事务,然后不做任何操作,那么这个线程处于Sleep状态

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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秒未提交事务的详细信息: 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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

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

如上截图所示,trx_query 为NULL值。基本上无法找到未提交事务的SQL语句,MySQL内部关于事务的信息不是很细,甚至可以说有点简洁。我甚至无法定位到在那个表上发生了锁。只能看到trx_row_locked、trx_row_modified、trx_started等信息。使用show engine innodb status也是如此,只能看到一些基本信息

?
1
2
3
4
5
6
7
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操作,但是未提交事务

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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将被阻塞。

?
1
2
3
4
5
6
7
8
9
10
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。 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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找出未提交事务信息的方法分享

总结:

基本上MySQL只能找到未提交事务的基本信息,例如trx_mysql_thread_id等。某些场景下,我们几乎没有方法找出未提交事务执行的SQL等详细信息。搞不清未提交事务做了什么操作!

好了,以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对服务器之家的支持。

原文链接:https://www.cnblogs.com/kerrycode/p/11013479.html