查看未提交事务的sql

事务未结束且连接空闲超过3秒的sql

SELECT 
p.ID AS conn_id,
P.USER AS login_user,
P.HOST AS login_host,
p.DB AS database_name,
P.TIME AS trx_sleep_seconds,
TIME_TO_SEC(TIMEDIFF(NOW(),T.trx_started)) AS trx_open_seconds,
t.trx_started,
t.trx_isolation_level,
-- t.trx_tables_locked,
-- t.trx_rows_locked,
-- t.trx_state,
-- p.COMMAND AS process_state,
-- stat.event_id as event_id, 
t.trx_is_read_only,
stat.sql_txt
FROM `information_schema`.`INNODB_TRX` t
INNER JOIN `information_schema`.`PROCESSLIST` p
ON t.trx_mysql_thread_id=p.id
inner join 
(
SELECT t1.SQL_TEXT as sql_txt, t2.PROCESSLIST_ID as pid , t1.EVENT_ID as event_id
FROM performance_schema.events_statements_history AS T1
INNER JOIN performance_schema.threads AS T2
ON T1.`THREAD_ID`=T2.`THREAD_ID`
) stat on stat.pid = p.id
WHERE t.trx_state='RUNNING'
-- 连接空闲超过3秒
AND p.COMMAND='Sleep' AND P.TIME>3
ORDER BY stat.event_id ASC

参考:https://www.cnblogs.com/gaogao67/p/10790520.html
https://blog.csdn.net/maxmao1024/article/details/79575829