mysql 查询 线程_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)