Identify deadlocks

from the Artful MySQL Tips List


Before MySQL 8.0.1, this query exposes the 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
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


Return to the Artful MySQL Tips page