MySQL

 

Waiting for table metadata lock

DBAなら誰もが遭遇する?このWaiting for table metadata lock。何故このロックが発生するのか、どう対処するか、を検証したいと思います。もし本番環境でうっかりこれを発生させると更新が停止し、アプリが停止する可能性があります。

新サイト、tree-mapsを公開しました!!

tree-maps: 地図のWEB TOOLの事ならtree-mapsにお任せ!

地図に関するWEB TOOL専門サイトです!!

大画面で大量の緯度経度を一気にプロット、ジオコーディング、DMS<->DEGの相互変換等ができます!

◯ 広告

日本語ページにこの説明は無いので、頑張って翻訳してみます。

To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted transaction in another session.

トランザクションの直列化を確保するため、他セッションによる処理が完了するまでの間、DDLの実行は許可しません。

As of MySQL 5.5.3, the server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends. For example, if a table t1 is in use by a transaction, another session that attempts to execute DROP TABLE t1 blocks until the transaction ends.

MySQL5.5.3では、テーブルのメタデータをロックする事で。トランザクションが完了するまでロックの開放を遅らせる事ができました。

メタデータロックは、テーブルの構造が変えられてしまう事を防止します。

このロック手法は、トランザクションが完了するまで他セッションによるDDLを実行させません。

例えば、テーブルt1がトランザクション実行中である場合、他セッションがt1に対してDROP TABLE文の実行を試みても、トランザクション実行中の間はブロックされます。

If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

もしメタデータロックを取得した場合、SQLの構文が正しかったとしても、最初のロックが開放されない限り実行は失敗します。

実行できなかったクエリーをバイナリログに書き込みと一貫性を保護するためのロックによって伸び続けるトランザクションが完了するまで、ロックは開放されません。

Metadata locks acquired during a PREPARE statement are released once the statement has been prepared, even if preparation occurs within a multiple-statement transaction.

メタデータロック中に取得したprepared statementは、たとえ複数のトランザクションであっても準備ができたら実行されます。

Before MySQL 5.5.3, when a transaction acquired a metadata lock for a table used within a statement, it released the lock at the end of the statement. This approach had the disadvantage that if a DDL statement occurred for a table that was being used by another session in an active transaction, statements could be written to the binary log in the wrong order.

MySQL5.5.3以前では、トランザクションがステートメント内で獲得したメタデータロックは、ステートメントの完了時に開放されました。

この手法にはデメリットがあり、もし他セッションのトランザクション実行中にDDLを実行した場合、バイナリログに間違った順序でステートメントを書き込んでしまっていました。

要約すると、バイナリログの書き込みの一貫性を保護するため、他セッションがトランザクション実行中はDDLを実行させなくしたよ、という訳です。

MySQL5.5.3以前は、ステートメント完了時にメタデータロックを開放してしまったせいで、バイナリログに間違った順番でステートメントを記録していました。

MySQL5.5.3以降は、ステートメントではなくトランザクションの完了時にメタデータロックを開放する事で、より厳密なバイナリログを書き込む事に成功した、という事だと思います。

(英語に自信無いので間違っているかもしれません)

MySQL5.5.53以降になって急にメタデータロックを見るようになったのは、トランザクション完了までメタデータロックが開放されなくなったからです。

MySQL5.5.3以前はステートメント完了時点で開放されたので、メタデータロックされにくかったのです。

DDLがブロックされるのでDBの更新ができなくなります

よくあるパターンは、DBの更新をしようとDDLを発行したが、メタデータロックによってブロックされてしまう場合です。

開発者がツールのトランザクション終了のタイミングを把握していない場合によく起きます。

例えばDBViewerというツールですが、手動コミットモードの場合、 明示的にコミットorロールバックしない限り、トランザクションが張りっぱなし、つまりメタデータロックしっぱなしになります。

具体的には、テーブルのタブを開くとトランザクションが開始されますが、タブを閉じてもトランザクションは終了しません。

こういったツールの挙動を把握していないがため、メタデータロックによるDBの更新不能状態が発生します。

例えばtomcatの起動時に、DBに保存していたアプリケーションメッセージ等を一気にメモリに貯めこむ等の場合です。

サーバの場合はスペックも高い場合が多いのでロックの時間も短いですが、ローカルはスペックが低い事も多いのでtomcatの起動に時間がかかる、つまり長時間メタデータロックしてしまう、という場合。

困ったことに現在のMySQL5.5では、メタデータロックしているセッションを特定する事ができません。

show processlistしても、犯人を特定できません。ではどうするか。

一番手軽な解決手段です。デプロイ時にDB更新する際は必ずMySQLのサービスを再起動しますよ、というルールを設けるとか。

犯人は特定できなくても、メタデータロック開放待ちセッションは解るのです

mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                               |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
|  1 | root | localhost | test | Query   |    0 | NULL                            | show processlist                   |
|  2 | root | localhost | test | Query   |    5 | Waiting for table metadata lock | alter table locktest engine=innodb |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+

例えばこういう状態だった場合、id=2がロック開放待ち、つまり犯人はid=1だ!

ということで、殺せ!殺せ!殺せ!

mysql> kill 1;
Query OK, 0 rows affected (0.00 sec)

こんな感じで kill コマンドでkillしてしまいましょう。

もしStateがメタデータロック以外のセッションが沢山ある場合、怪しいやつを一個づつkillしてしまいましょう。

treeおすすめの書籍です!
【送料無料】実践ハイパフォ-マンスMySQL第2版

【送料無料】実践ハイパフォ-マンスMySQL第2版
価格:4,830円(税込、送料別)

【送料無料】エキスパ-トのためのMySQL運用+管理トラブルシュ-ティングガイド

【送料無料】エキスパ-トのためのMySQL運用+管理トラブルシュ-ティングガイド
価格:3,465円(税込、送料別)

【送料無料】基礎からのMySQL改訂版

【送料無料】基礎からのMySQL改訂版
価格:3,129円(税込、送料別)

◯ 広告