Диагностика '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;

Что делает

Показывает, metadata lock какой сессии ждёт DDL (ALTER / TRUNCATE / RENAME), и запросы, выстроившиеся в очередь за 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. Профилактика: выполняйте DDL после SET SESSION lock_wait_timeout = 5, чтобы ALTER быстро сдавался, и избегайте долгих открытых транзакций в приложении.