MySQL找出未提交事务信息的方法分享
16lz
2021-12-22
前阵子,我写了一篇博客“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>
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
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
如下测试所示,会话(连接 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>
mysql> select connection_id();+-----------------+| connection_id() |+-----------------+| 13 |+-----------------+1 row in set (0.00 sec) mysql> mysql> update kkk set id=100 where id=1;
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>
基本上MySQL只能找到未提交事务的基本信息,例如trx_mysql_thread_id等。某些场景下,我们几乎没有方法找出未提交事务执行的SQL等详细信息。搞不清未提交事务做了什么操作!
好了,以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对的支持。
更多相关文章
- ES6 变量声明,箭头函数,数组方法,解构赋值,JSON,类与继承,模块化练习
- 浅谈Java中Collections.sort对List排序的两种方法
- Python list sort方法的具体使用
- python list.sort()根据多个关键字排序的方法实现
- android上一些方法的区别和用法的注意事项
- android实现字体闪烁动画的方法
- Android中dispatchDraw分析
- Android四大基本组件介绍与生命周期
- Android(安卓)MediaPlayer 常用方法介绍