Diagnose 'Waiting for table metadata lock' and find the real blocker

Command

-- 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;

What it does

Shows which session's metadata lock a DDL such as ALTER / TRUNCATE / RENAME is waiting on, and the queries queued behind the DDL. The blocker is usually a session in Sleep that read the table and never closed its transaction.

When to use it

When, after an ALTER starts, every query on that table piles up in 'Waiting for table metadata lock'.

Caution

A waiting DDL also blocks the SELECTs that arrive after it; decide quickly during the incident: KILL the blocking session or cancel the ALTER (KILL QUERY). The blocker's PROCESSLIST_INFO may be NULL (idle); use performance_schema.events_statements_history or sys.session.last_statement to see its last statement. Prevention: run DDL after SET SESSION lock_wait_timeout = 5 so the ALTER gives up quickly, and avoid long open transactions in the application.