共计 3533 个字符，预计需要花费 9 分钟才能阅读完成。
自动写代码机器人，免费开通
本篇文章给大家分享的是有关怎么在 MySQL 中找出未提交的事务信息，丸趣 TV 小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着丸趣 TV 小编一起来看看吧。
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 中找出未提交的事务信息，丸趣 TV 小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注丸趣 TV 行业资讯频道。
向 AI 问一下细节
