mysql 5.1 innodb trx_MySQL 查看事务和锁情况的常用语句分享

一些查看数据库中事务和锁情况的常用语句

查看事务等待状况：

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;

查看更具体的事务等待状况：

SELECT

b.trx_state,

e.state,

e.time,

d.state AS block_state,

d.time AS block_time,

a.requesting_trx_id,

a.requested_lock_id,

b.trx_query,

b.trx_mysql_thread_id,

a.blocking_trx_id,

a.blocking_lock_id,

c.trx_query AS block_trx_query,

c.trx_mysql_thread_id AS block_trx_mysql_tread_id

FROM

information_schema.INNODB_LOCK_WAITS a

LEFT JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id = b.trx_id

LEFT JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id = c.trx_id

LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = d.id

LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = e.id

ORDER BY

a.requestin