LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon