Deadlocks

from the Artful MySQL Tips List


This query exposes source of a deadlock ...

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

Show InnoDB Status exposes more info.



Return to the Artful MySQL Tips page