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, current_timestamp)) as max_wait_time,
count(*) as num_waiters
from information_schema.innodb_lock_waits as w
join information_schema.innodb_trx as b on b.trx_id = w.blocking_trx_id
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
8.0.1 rearranged how MySQL tracks this information, the query is now ...
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, current_timestamp)) as max_wait_time,
count(*) as num_waiters
from performance_schema.data_lock_waits as w
join information_schema.innodb_trx as b
on b.trx_id = w.blocking_engine_transaction_id
join information_schema.innodb_trx as r
on r.trx_id = w.requesting_engine_transaction_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;
In all versions, Show Engine InnoDB Status exposes more info (though it's painfully opaque).Last updated 3 Aug 2020
