Diagnostiquer 'Waiting for table metadata lock' et trouver le vrai bloqueur

Commande

-- Waiter -> blocker pairs; filter out the ALTER's own SHARED_UPGRADABLE entry
SELECT object_schema, object_name, waiting_pid, LEFT(waiting_query, 60) AS waiting_query,
       waiting_lock_type, blocking_pid, blocking_lock_type, sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE blocking_lock_type <> 'SHARED_UPGRADABLE';

-- All MDLs on the table, owner age and open transaction
SELECT ml.OBJECT_SCHEMA, ml.OBJECT_NAME, ml.LOCK_TYPE, ml.LOCK_DURATION, ml.LOCK_STATUS,
       t.PROCESSLIST_ID AS conn_id, t.PROCESSLIST_COMMAND AS command, t.PROCESSLIST_TIME AS sec,
       LEFT(t.PROCESSLIST_INFO, 60) AS query, trx.trx_started
FROM performance_schema.metadata_locks ml
JOIN performance_schema.threads t ON t.THREAD_ID = ml.OWNER_THREAD_ID
LEFT JOIN information_schema.INNODB_TRX trx ON trx.trx_mysql_thread_id = t.PROCESSLIST_ID
WHERE ml.OBJECT_TYPE = 'TABLE' AND ml.OBJECT_SCHEMA NOT IN ('performance_schema', 'mysql')
ORDER BY ml.OBJECT_NAME, ml.LOCK_STATUS DESC;

Ce que fait la commande

Indique sur le metadata lock de quelle session un DDL comme ALTER / TRUNCATE / RENAME attend, ainsi que les requêtes en file derrière le DDL. Le bloqueur est généralement une session en Sleep qui a lu la table sans jamais fermer sa transaction.

Quand l'utiliser

Quand, après le lancement d'un ALTER, toutes les requêtes sur cette table s'accumulent en 'Waiting for table metadata lock'.

Attention

Un DDL en attente bloque aussi les SELECT qui arrivent après lui ; décidez vite pendant l'incident : KILL de la session bloquante ou annulation de l'ALTER (KILL QUERY). Le PROCESSLIST_INFO du bloqueur peut être NULL (inactif) ; utilisez performance_schema.events_statements_history ou sys.session.last_statement pour voir sa dernière instruction. Prévention : exécutez le DDL après SET SESSION lock_wait_timeout = 5 pour que l'ALTER abandonne rapidement, et évitez les longues transactions ouvertes dans l'application.