Finding InnoDB lock waits and the blocking transaction
MySQL query that lists InnoDB transactions waiting for a lock and the transaction blocking them.
This query lists the InnoDB transactions that wait for a lock, with the transaction that holds the lock they need.
SELECT
r.trx_id AS waiting_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query
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;
Notes
- The version this note first described compared the same transaction as both the waiting and the blocking one. Those two ids are always different in a lock wait, so that version returned no rows. The query above reads the transaction table twice, once for each side.
- The original also showed the user and the state from
information_schema.processlist. Join that view on the thread id to add them. - On MySQL 8.0, the lock wait details are also exposed in
performance_schema.data_lock_waits. Check the documentation of your version before relying on a view.