查看事务相关信息
SHOW ENGINE INNODB STATUS;
查看锁表 data_lock_wait 锁等待
select * from information_schema.INNODB_LOCKS;
查看事务相关信息
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
查询历史事务
SELECT a.trx_id, a.trx_state, a.trx_started, a.trx_query, b.ID, b.USER, b.DB, b.COMMAND, b.TIME, b.STATE, b.INFO, c.PROCESSLIST_USER, c.PROCESSLIST_HOST, c.PROCESSLIST_DB, d.SQL_TEXT FROM
用法
SELECT * FROM information_schema.INNODB_TRX
这个只能查询此刻正在进行中的事务,已经完成的是查不到的
针对mysql 5.6,查看更具体的信息:SELECT a.trx_id, a.trx_state, a.trx_started, a.trx_query, b.ID, b.USER, b.DB, b.COMMAND, b.TIME, b.STATE, b.INFO, c.PROCESSLIST_USER, c.PROCESSLIST_HOST, c.PROCESSLIST_DB, d.SQL_TEXT FROM information_schema.INNODB_TRX a LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id AND b.COMMAND = 'Sleep' LEFT JOIN PERFORMANCE_SCHEMA.