LLI Wiki — My Digital Knowledge Base

Trouver les attentes de verrous InnoDB et la transaction bloquante

Requête MySQL qui liste les transactions InnoDB en attente de verrou et la transaction qui les bloque.

Cette requête liste les transactions InnoDB qui attendent un verrou, avec la transaction qui détient le verrou dont elles ont besoin.

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;

Remarques

Mastodon LLIMastodon SocialFosstodon