Waiting for table metadata lockが出たら、待っているALTER TABLEだけでなく、そのテーブルの定義を変更できない状態にしている接続を調べます。保持している側は、SQLを実行中とは限りません。SELECT後にトランザクションを開いたまま、Sleepになっていることもあります。
解除方法は、待機中のDDLをいったん中止するか、保持側のトランザクションを終了するかで変わります。ここではMySQL 8.4.11の独立した検証DBで、ALTERに続くSELECTまで止まる状態を作り、止める対象によって何が変わるかを確かめます。
待機者と保持者を、同じテーブル名で照合する
まず接続先、待機状態、計測の有効状態を確認します。以下の照会には、対象のPerformance Schemaやsysビューを参照できる権限が必要です。接続一覧が一部しか見えない場合は、管理担当へ調査を依頼してください。
SELECT @@hostname, VERSION(), CONNECTION_ID(), DATABASE();
SHOW FULL PROCESSLIST;
SELECT @@performance_schema;
SELECT NAME, ENABLED, TIMED
FROM performance_schema.setup_instruments
WHERE NAME = 'wait/lock/metadata/sql/mdl';@@performance_schemaが1、wait/lock/metadata/sql/mdlのENABLEDがYESなら、次の照会へ進みます。この計測はMySQL 8.4では既定で有効ですが、運用で変更されている可能性があります。無効な場合に、空の結果だけを見て「ロックなし」と判断しないでください。設定はmetadata_locksの公式説明で確認できます。
次のmdl_labとmdl_demoは、後半で作る検証用のDB名・テーブル名です。実際の調査では対象名へ差し替えます。最初の照会で接続の組み合わせを見つけ、次の照会で保持状態と現在の処理を見ます。
SELECT object_schema, object_name,
waiting_pid, waiting_account, waiting_lock_type, waiting_query,
blocking_pid, blocking_account, blocking_lock_type
FROM sys.schema_table_lock_waits
WHERE object_schema = 'mdl_lab' AND object_name = 'mdl_demo';
SELECT ml.OBJECT_SCHEMA, ml.OBJECT_NAME, ml.LOCK_TYPE,
ml.LOCK_DURATION, ml.LOCK_STATUS,
t.THREAD_ID, t.PROCESSLIST_ID, t.PROCESSLIST_USER,
t.PROCESSLIST_COMMAND, t.PROCESSLIST_STATE, t.PROCESSLIST_INFO
FROM performance_schema.metadata_locks AS ml
LEFT JOIN performance_schema.threads AS t
ON t.THREAD_ID = ml.OWNER_THREAD_ID
WHERE ml.OBJECT_TYPE = 'TABLE'
AND ml.OBJECT_SCHEMA = 'mdl_lab' AND ml.OBJECT_NAME = 'mdl_demo'
ORDER BY t.PROCESSLIST_ID, ml.LOCK_STATUS;
SELECT trx_mysql_thread_id, trx_state, trx_started, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;| 見る列 | 読み方 |
|---|---|
waiting_pid |
ロックを待っている接続のID。ALTERか後続SELECTかも確認する |
blocking_pid |
同じ対象で保持中のロックと結び付けられた接続。停止対象を決めるための手掛かり |
GRANTED / PENDING |
取得済み／取得待ち。ALTERの接続に両方の行が出る場合もある |
PROCESSLIST_COMMAND |
Sleepでも、未終了のトランザクションがあれば保持し続ける場合がある |
trx_mysql_thread_id |
トランザクションを接続IDと照合する。trx_queryがNULLでも終了済みとは限らない |
KILLが受け取るのはPROCESSLIST_IDです。THREAD_IDやOWNER_THREAD_IDとは別です。sysビューではwaiting_pid・blocking_pidが接続IDに対応します。列の定義はschema_table_lock_waitsの説明を参照してください。
このsysビューの行から、KILL文を自動実行するのは避けます。今回の8.4.11では、同じ対象の取得済みロックと待機ロックを結び付けるため、待機者と保持者が同じIDの行や、後続SELECTと先行SELECTの組み合わせも出ました。表示された全接続が、停止すべき原因という意味ではありません。生のロック状態、現在のSQL、アプリ側の処理と一緒に読みます。
innodb_trxはInnoDBトランザクションの補助情報です。そこに見つからないことだけでmetadata lockを否定せず、metadata_locks側から確認を続けてください。
SELECT後のSleepが、ALTERと後続SELECTを止める状態を再現する
本番と分かれた検証用MySQLで、未使用の名前を使います。次はDBと1行だけのテーブルを作る準備です。
CREATE DATABASE mdl_lab;
CREATE TABLE mdl_lab.mdl_demo (id INT PRIMARY KEY, quantity INT NOT NULL) ENGINE=InnoDB;
INSERT INTO mdl_lab.mdl_demo VALUES (1, 10);SQL接続をA・B・Cの3つと、確認用にもう1つ開きます。GUIのタブが別でも、同じ接続を共有していることがあるため、CONNECTION_ID()が異なることを確認してください。
接続A：SELECT後もトランザクションを閉じない
USE mdl_lab;
SELECT CONNECTION_ID();
START TRANSACTION;
SELECT * FROM mdl_demo;
-- Keep this connection open; do not commit yet.SELECTの結果が返ったところで止め、Aは開いたままにします。今回の検証では接続IDが10でした。明示的なトランザクション内で触れたテーブルのmetadata lockは、トランザクション終了まで保持されます。詳しくはMySQLのmetadata lock解放規則に記載されています。
接続B：LOCK=NONEのALTERを送る
USE mdl_lab;
SELECT CONNECTION_ID();
SET @old_mdl_timeout = @@SESSION.lock_wait_timeout;
SET SESSION lock_wait_timeout = 60;
ALTER TABLE mdl_demo ADD COLUMN note VARCHAR(50) NULL,
ALGORITHM=INPLACE, LOCK=NONE;この60秒は、検証中の待ち時間を制限するための値です。Bが待機に入ったら、その完了を待たずにCへ進みます。60秒を過ぎてタイムアウトした場合は、Aを終了して状態を確認し、再現の途中へ不用意にSQLを継ぎ足さないようにしてください。
接続C：同じテーブルを読む
SELECT * FROM mdl_lab.mdl_demo;Cも待機します。検証では、Aが10、Bが11、Cが12になり、先ほどの照会で次の状態を確認できました。
| 接続 | 状態 | metadata lock |
|---|---|---|
| A：10 | Sleep、trx_queryはNULL | SHARED_READをGRANTEDとして保持 |
| B：11 | ALTERが待機 | SHARED_UPGRADABLEを保持し、EXCLUSIVEはPENDING |
| C：12 | SELECTが待機 | SHARED_READがPENDING |
Aがテーブルの定義を使っている間に、Bが定義変更に必要な排他metadata lockを求め、その待機が後続のCにも波及しています。LOCK=NONEはmetadata lockを不要にする指定ではありません。online DDLでもこの連鎖が起きることは、公式のonline DDLとmetadata lockの説明でも示されています。
DDLを中止するか、保持トランザクションを終了するかを選ぶ
調査結果を保存し、処理の担当者と対象を確かめてから操作します。表の接続番号は今回の検証値です。実環境へその番号を転記せず、操作直前に接続ID・ユーザー・SQL・対象テーブルを読み直してください。
| 目的・状況 | 選ぶ操作 | 起こること |
|---|---|---|
| スキーマ変更を延期し、待機中のDDLを取り下げたい | 待機DDLの接続にKILL QUERY 接続ID |
その文を中断する。保持元のトランザクションを終了する操作ではない |
| 保持側の正当な処理が完了している | 保持している接続自身でCOMMITまたはROLLBACK | 確定か取消かは、その処理の内容で決める。管理接続からCOMMITしても他の接続は終了しない |
| 保持側が放置され、接続を切る判断をした | 保持接続にKILL CONNECTION 接続ID |
接続を終了する。未確定の通常のInnoDBトランザクションは取り消される |
| 保持側がSleepなので、とりあえずKILL QUERYしたい | それだけではトランザクション終了にならない | 今回の検証ではALTERの待機が残った |
検証環境でBの待機DDLだけをKILL QUERY 11により中止すると、Bには1317が返り、Cは1行を読み取れました。一方、AのSHARED_READは残っています。DDLを延期して待機の連鎖を外す選択と、保持元を終了する選択は、このように結果が異なります。
AでCOMMIT;を実行した後に同じALTERを再実行すると、今度は成功し、note列が追加されました。別の検証では、Aでquantityを10から99へ更新して未確定のまま保持し、接続を終了すると、99は取り消されて10へ戻りました。
KILL QUERYとKILL CONNECTIONの範囲や権限はKILLの公式リファレンスで確認できます。KILLの応答が返っても、終了処理まで済んだとは限りません。大量更新の取り消しやDDLの後処理には時間がかかることがあるため、待機状態と接続を再確認します。
接続を切る場合は、アプリの再接続・自動再試行も確認します。原因が残ったまま同じDDLが再投入されれば、再び待機を作ることがあります。また、XAのPREPARED状態は接続終了後もmetadata lockを保持するため、通常の接続切断と同じ手順で解決すると考えず、トランザクション管理担当へ確認してください。
DDL接続のlock_wait_timeoutを短くし、元の値へ戻す
metadata lock取得待ちを制限するのはlock_wait_timeoutです。InnoDBの行ロック待ちに使うinnodb_lock_wait_timeoutとは分けて考えます。今回のBでは保存済みの元値を保ったまま、SESSION値を1秒へ変えて同じALTERを送ると、約1.002秒で1205になりました。
-- In connection B, while A still holds the lock and note is not added yet.
SET SESSION lock_wait_timeout = 1;
ALTER TABLE mdl_lab.mdl_demo ADD COLUMN note VARCHAR(50) NULL,
ALGORITHM=INPLACE, LOCK=NONE;1秒はタイムアウトを確かめる実験値です。運用では、許容する待機時間と中止後の処理を決めたうえで、DDLを実行する接続に設定します。管理用の別接続だけを変更しても、すでに動いているDDL接続の値は変わりません。
この上限は、metadata lockの取得を試みるごとに適用されます。DDL全体が必ずその秒数以内に終わるという実行時間制限ではありません。既定値31536000秒と適用範囲は、lock_wait_timeoutの仕様で確認できます。
検証や一時的な設定変更が終わったら、Bと同じ接続で元へ戻します。保存値がNULLなら、そのままSETせず、別途記録した値を確認してください。検証では31536000へ戻ったことも確認しています。
-- Same connection that saved @old_mdl_timeout.
SELECT @old_mdl_timeout;
-- Stop if NULL; consult your recorded original value instead.
SET SESSION lock_wait_timeout = @old_mdl_timeout;
SELECT @@SESSION.lock_wait_timeout;例ではAを終了した後にALTERを再実行して列を追加しました。一度成功したSQLをそのまま再実行すると同名列のエラーになるため、SHOW CREATE TABLE mdl_lab.mdl_demo;で現在の定義を確認してから次の操作へ進みます。
解除後は、待機が消えたこととアプリの処理を確認する
接続が一覧から消えただけでは、調査は終わりません。次の順に、DBの状態とアプリへの影響を照合します。
- 待機：対象テーブルのPENDINGと、Waiting for table metadata lockが解消したか。
- DDLの結果：中止したか、成功したか。SHOW CREATE TABLEで実際の定義を確認したか。
- データ：保持接続を切った場合、取り消された処理をアプリ側で扱えているか。
- 再試行：失敗した移行や接続が、意図せず同じ処理を繰り返していないか。
- 予防：長いトランザクション、接続プールへ戻す前の終了処理、移行実行時間帯と中止条件を見直したか。
今回の再現では、最終的に対象テーブルのPENDINGが0になり、変更後の列とデータを確認できました。実際の運用では、まず「誰を止めたから、どの待機が消えたか」を残すと、次の移行で同じ状態に気づきやすくなります。
行ロックやデッドロックを調べる場合はMySQLのロック・デッドロック、変更するDDLの構文や適用順を確認する場合はMySQLのDDLの書き方へ進んでください。