诊断 'Waiting for table metadata lock' 并找到真正的阻塞者

命令

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

作用

显示 ALTER / TRUNCATE / RENAME 等 DDL 正在等待哪个会话的元数据锁，以及排在该 DDL 之后的查询。阻塞者通常是读取过该表却从未关闭事务的 Sleep 会话。

适用场景

ALTER 启动后，该表上的所有查询都堆积在 'Waiting for table metadata lock' 状态时。

注意

等待中的 DDL 也会阻塞其后到达的 SELECT；故障期间要快速决断：KILL 阻塞会话或取消 ALTER（KILL QUERY）。阻塞者的 PROCESSLIST_INFO 可能为 NULL（空闲）；使用 performance_schema.events_statements_history 或 sys.session.last_statement 查看其最后一条语句。预防：先执行 SET SESSION lock_wait_timeout = 5 再运行 DDL，使 ALTER 快速放弃，并避免应用中出现长时间打开的事务。