怎么在MySQL中找到未提交的事务信息
发布时间：2022-02-14 21:41:16 所属栏目：MySql教程 来源：互联网
导读：本篇文章给大家分享的是有关怎么在MySQL中找出未提交的事务信息，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。 mysql select connection_id() from dual; +-----------------+ | conne
|
本篇文章给大家分享的是有关怎么在MySQL中找出未提交的事务信息，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。 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_startedG; *************************** 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中找出未提交的事务信息，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。 （编辑：温州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
