'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 を使用する。予防策: ALTER がすぐに諦めるよう SET SESSION lock_wait_timeout = 5 の後で DDL を実行し、アプリケーションで長時間開いたままのトランザクションを避ける。