from the Artful MySQL Tips List
This query exposes source of a deadlock ...
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
Show InnoDB Status exposes more info.
Return to the Artful MySQL Tips page