MySQL查询未提交事务连接线程

  • 查询运行了 10 秒以上的事务,并且没有语句运行,也没有提交的事务进程信息

    • 其实也可以根据 information_schema.innodb_trx 表的 trx_query 字段确定是否在执行 SQL 语句

    • 如果打捞出的事务状态很多都是 NULL要对比下 trx_started 字段,查看事务的开启时间,如果开启的时间比较久,那就是忘记提交了

    • select * from information_schema.innodb_trx where trx_query is null order by trx_started;

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.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';
+---------------------+-----------+-------------------+-------------------+-----------+-----------------+-------------------+------------------+---------------------+---------------------+------+------+-----------+--------+---------+
| trx_mysql_thread_id | trx_state | trx_tables_in_use | trx_tables_locked | trx_query | trx_rows_locked | trx_rows_modified | trx_lock_structs | trx_started         | trx_isolation_level | time | user | host      | db     | command |
+---------------------+-----------+-------------------+-------------------+-----------+-----------------+-------------------+------------------+---------------------+---------------------+------+------+-----------+--------+---------+
|               29652 | RUNNING   |                 0 |                 1 | NULL      |               0 |                 1 |                1 | 2021-02-03 17:34:59 | REPEATABLE READ     |  101 | root | localhost | testdb | Sleep   |
+---------------------+-----------+-------------------+-------------------+-----------+-----------------+-------------------+------------------+---------------------+---------------------+------+------+-----------+--------+---------+
1 row in set (0.00 sec)
「点点赞赏,手留余香」

    还没有人赞赏,快来当第一个赞赏的人吧!
0 条回复 A 作者 M 管理员
    所有的伟大,都源于一个勇敢的开始!
欢迎您,新朋友,感谢参与互动!欢迎您 {{author}},您在本站有{{commentsCount}}条评论