MySql查询正在进行中的事务

- 日理万妓 2023-06-25 04:37 29阅读 0赞

查看事务相关信息

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.threads c ON b.id = c.PROCESSLIST_ID LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;`

针对mysql 5.5，查看更具体的信息：  
`SELECT a.trx_id, a.trx_state, a.trx_started, a.trx_query, b.ID, b. USER, b. HOST, b.DB, b.COMMAND, b.TIME, b.STATE, b.INFO FROM information_schema.INNODB_TRX a LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id WHERE b.COMMAND = 'Sleep';`

查看事务等待情况：  
`SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;`

# 查看连接及对应的事务 #

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.threads c ON b.id = c.PROCESSLIST\_ID LEFT JOIN PERFORMANCE\_SCHEMA.events\_statements\_current d ON d.THREAD\_ID = c.THREAD\_ID;  
SELECT \* FROM information\_schema.processlist p LEFT JOIN information\_schema.INNODB\_TRX t ON p.id = t.trx\_mysql\_thread\_id;