查询运行了 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)