MySQL中经常遇到事务中的未提SQL正在执行或执行完成后未提交，如何找出对应的事务SQL？ 
1. 查看正在执行的SQL查看事务中正在执行的SQL方式有多种，服务器托管例如 1.1 通过processlist查看会话1：执行1个SQL 复制mysql> begin ; Query OK,未提 0 rows affected (0.00 sec ) mysql> select sleep(20),now() ,id from test1;1.2.3.4.5. 会话2：开启另一个会话，查看对应的事务SQL 复制mysql> select id ,info from information_schema.processlist where info is not null ; +----+------------------------------------------------------------------------------+| id | info |+----+------------------------------------------------------------------------------+| 36 | select sleep(20),now() ,id from test1 || 37 | select id ,info from information_schema.processlist where info is not null |+----+------------------------------------------------------------------------------+2 rows in set (0.00 sec)1.2.3.4.5.6.7.8. 可以看到正在执行的SQL，包括自己的亿华云未提SQL的id及内容。1.2 通过events_statements_current查看会话1：执行1个SQL 复制mysql> begin ; Query OK,事务 0 rows affected (0.00 sec ) mysql> select sleep(20),now() ,id from test1;1.2.3.4.5. 会话2：查看对应的SQL 复制mysql> select a.id,a.info, b.thread_id, c.sql_text from information_schema.processlist a, performance_schema.threads b, performance_schema.events_statements_current c where a.id=b.processlist_id and b.thread_id = c.thread_id\G |
喜欢
讨厌