SELECT
p2.HOST Blockedhost,
p2.USER BlockedUser,
r.trx_id BlockedTrxId,
r.trx_mysql_thread_id BlockedThreadId,
TIMESTAMPDIFF(
SECOND,
r.trx_wait_started,
CURRENT_TIMESTAMP
) WaitTime,
r.trx_query BlockedQuery,
l.lock_table BlockedTable,
m.lock_mode BlockedLockMode,
m.lock_type BlockedLockType,
m.lock_index BlockedLockIndex,
m.lock_space BlockedLockSpace,
m.lock_page BlockedLockPage,
m.lock_rec BlockedLockRec,
m.lock_data BlockedLockData,
p.HOST blocking_host,
p.USER blocking_user,
b.trx_id BlockingTrxid,
b.trx_mysql_thread_id BlockingThreadId,
b.trx_query BlockingQuery,
l.lock_mode BlockingLockMode,
l.lock_type BlockingLockType,
l.lock_index BlockingLockIndex,
l.lock_space BlockingLockSpace,
l.lock_page BlockingLockPage,
l.lock_rec BlockingLockRec,
l.lock_data BlockingLockData,
IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) idel_in_trx
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
INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.lock_trx_id=b.trx_id
INNER JOIN information_schema.INNODB_LOCKS m ON m.lock_id=w.requested_lock_id AND m.lock_trx_id=r.trx_id
INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id
INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id
ORDER BY
WaitTime DESC \G;
对应中文解析
SELECT
p2.HOST 被阻塞方host,
p2.USER 被阻塞方用户,
r.trx_id 被阻塞方事务id,
r.trx_mysql_thread_id 被阻塞方线程号,
TIMESTAMPDIFF(
SECOND,
r.trx_wait_started,
CURRENT_TIMESTAMP
) 等待时间,
r.trx_query 被阻塞的查询,
l.lock_table 阻塞方锁住的表,
m.lock_mode 被阻塞方的锁模式,
m.lock_type "被阻塞方的锁类型(表锁还是行锁)",
m.lock_index 被阻塞方锁住的索引,
m.lock_space 被阻塞方锁对象的space_id,
m.lock_page 被阻塞方事务锁定页的数量,
m.lock_rec 被阻塞方事务锁定行的数量,
m.lock_data 被阻塞方事务锁定记录的主键值,
p.HOST 阻塞方主机,
p.USER 阻塞方用户,
b.trx_id 阻塞方事务id,
b.trx_mysql_thread_id 阻塞方线程号,
b.trx_query 阻塞方查询,
l.lock_mode 阻塞方的锁模式,
l.lock_type "阻塞方的锁类型(表锁还是行锁)",
l.lock_index 阻塞方锁住的索引,
l.lock_space 阻塞方锁对象的space_id,
l.lock_page 阻塞方事务锁定页的数量,
l.lock_rec 阻塞方事务锁定行的数量,
l.lock_data 阻塞方事务锁定记录的主键值,
IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' 秒'), 0) 阻塞方事务空闲的时间
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
INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.lock_trx_id=b.trx_id
INNER JOIN information_schema.INNODB_LOCKS m ON m.lock_id=w.requested_lock_id AND m.lock_trx_id=r.trx_id
INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id
INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id
ORDER BY
等待时间 DESC \G;
