InnoDB多少查询被哪些线程阻塞

SELECT CONCAT('thread', b.trx_mysql_thread_id, 'from ', p.host) AS who_blocks,
    IF(p.command = "Sleep",p.time, 0) AS idle_in_trx,
    MAX(TIMESTAMPDIFF(SECOND, r.trx_wait_started,NOW())) AS max_wait_time,
    COUNT(*) AS num_waiters
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id
LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id
GROUP BY who_blocks ORDER BY num_waiters DESC \G

转载于:https://my.oschina.net/u/2427170/blog/704235