检查未提交的事务

参考老叶:查看最新的事务

https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=203875697&idx=1&sn=16596bbe7c5c0d805d1723c0704b1d2d&chksm=2f327f1b1845f60d2904c59ea09adfd49e6fb5e5eec262a94d73c53b1140f9a26094e21a7d44&mpshare=1&scene=24&srcid=#rd

1.在开启 performance_schema=on 的情况下 

select proc.id,tx.trx_state,proc.user, left(proc.host,position(':' in proc.host)-1) as host,proc.DB, cast(tx.trx_started as char(20)) as trx_started
,proc.TIME,sc.SQL_TEXT,left(sc.SQL_TEXT,60) as sqlsample 
from information_schema.INNODB_TRX as tx join information_schema.processlist as proc on tx.trx_mysql_thread_id=proc.ID 
join performance_schema.threads as th on proc.id=th.PROCESSLIST_ID 
join performance_schema.events_statements_current as sc on th.THREAD_ID=sc.THREAD_ID 
where proc.COMMAND='Sleep' 
and proc.time>60 and proc.user not in ('system user','usvr_replication','usvr_serveroper','event_scheduler') 
order by proc.TIME desc;

2.没开启的情况

-- mysql 5.6
information_schema 简称I_S
innodb_trx
innodb_locks
innodb_lock_waits

SELECT  lw.requesting_trx_id AS request_XID, trx.trx_mysql_thread_id as request_mysql_PID
, trx.trx_query AS request_query,  lw.blocking_trx_id AS blocking_XID
, trx1.trx_mysql_thread_id as blocking_mysql_PID, trx1.trx_query AS blocking_query
, lo.lock_index AS lock_index 
FROM     information_schema.innodb_lock_waits lw 
INNER JOIN information_schema.innodb_locks lo ON lw.requesting_trx_id = lo.lock_trx_id 
INNER JOIN information_schema.innodb_locks lo1 ON lw.blocking_trx_id = lo1.lock_trx_id 
INNER JOIN information_schema.innodb_trx trx ON lo.lock_trx_id = trx.trx_id 
INNER JOIN information_schema.innodb_trx trx1 ON lo1.lock_trx_id = trx1.trx_id limit 100 ;

 

只展示查询中一段语句,方便查看,并展示事务开始时间和隔离级别
SELECT  lw.requesting_trx_id AS request_XID, trx.trx_mysql_thread_id as request_mysql_PID
, substring(trx.trx_query,1,30) AS request_query,  lw.blocking_trx_id AS blocking_XID
, trx1.trx_mysql_thread_id as blocking_mysql_PID,substring(trx1.trx_query,1,30) AS blocking_query
, lo.lock_index AS lock_index,trx.trx_started as 'requesting starttime',trx1.trx_started as 'blocking starttime',trx1.trx_isolation_level
FROM     information_schema.innodb_lock_waits lw 
INNER JOIN information_schema.innodb_locks lo ON lw.requesting_trx_id = lo.lock_trx_id 
INNER JOIN information_schema.innodb_locks lo1 ON lw.blocking_trx_id = lo1.lock_trx_id 
INNER JOIN information_schema.innodb_trx trx ON lo.lock_trx_id = trx.trx_id 
INNER JOIN information_schema.innodb_trx trx1 ON lo1.lock_trx_id = trx1.trx_id limit 10 ;

 -- request_XID 请求的语句,被柱塞的语句

-- blocking_XID 源头语句