メタデータロックの調べ方

MySQL 8.0

メタデータロックの調べ方

目次

メタデータとメタデータロックとは

メタデータは何ぞや?と思う方も多いと思いますが、マニュアルから引用すると「データに関するデータ」だそうです。

メタデータは「データに関するデータ」です。 データベースについて記述しているすべてのものがメタデータであり、データベースの内容ではありません。 したがって、カラム名、データベース名、ユーザー名、バージョン名、および SHOW の文字列結果のほとんどがメタデータです。 INFORMATION_SCHEMA 内のテーブルは定義上、データベースオブジェクトに関する情報を含んでいるので、これは、このテーブルの内容にも当てはまります。
10.2.2 メタデータ用の UTF-8

大雑把にいうと、DML(Data Manipulation Statements)で操作するデータ以外のデータ、主にDDL(Data Definition Statements)などで操作するデータなどを指しています。

普段はあまり気にすることはありませんが、トランザクションの途中でテーブル定義が変わってしまったなんてことが起きないように、これらのデータもトランザクション中にロックの取得と解放が自動的に行われています。

クエリチューニングを行っているとあるテーブルにIndexを追加をしようとすると、別の誰かがトランザクションの中でIndexを追加したいテーブルのメタデータロックを取得していてIndex追加が待たされることが時々発生します。

そのような場合に SHOW PROCESSLIST を見ると、StatusがWaiting for table metadata lock となって、メタデータのロック解放待ちになっていつまでも待たされています。(ID 74の一番下のクエリがメタデータロックの解放待ち)

なので、このメタデータロックを行っているDBコネクションを探す必要が出てきます。

メタデータのロックを取得しているコネクションの調べ方

メタデータのロック情報は、performance_schemaデータベースにあるmetadata_locks テーブルから取得できます。

https://dev.mysql.com/doc/refman/8.0/ja/performance-schema-metadata-locks-table.html

ただ、こちらのテーブルにある情報だけでは、そのメタデータロックを取得しているDBコネクションの情報に行きつかないため、threads テーブルのthread_idと結合する必要があります。

https://dev.mysql.com/doc/refman/8.0/ja/performance-schema-threads-table.html

目的のテーブルのメタデータロックを取得しているDBコネクションを探し出すには、以下のようなクエリを実行することで、ロックを保持しているDBコネクションを探し出せます。

上記クエリを実行すると以下のような結果が得られます。

ALTER 文を実行中のConnection ID 74以外の64~73までの10本のDBコネクションが問題のメタデータロックを解放していない原因のDBコネクションだとわかります。

メタデータロックの外し方

基本的にはメタデータロックを外すには、該当のDBコネクションの持ち主に解放(トランザクションの終了など)をしてもらうのが正当な手順です。

ただ、開発環境など処理の途中のDBコネクションを切断しても影響がコントロール可能な場合には、DBコネクションを強制的に切断してしまって解放する方法もあります。

DBコネクションを強制的に切断する方法にKILLステートメントを利用します。

https://dev.mysql.com/doc/refman/8.0/ja/kill.html

上記の例だと、10回繰り返しKILLコマンドを実行する必要があります。

たくさんのDBコネクションを切断するのは面倒

Killコマンドでも1つ、2つのぐらいならコネクションIDを特定して1個づつKILLするも簡単ですが、プログラムのバグ等で大量のDBコネクションをKILLする必要が出てきた場合には面倒です。
なので、KILLステートメントはそのままではクエリに組み込めないので、ラップするFunctionを作成します。

下記のクエリで、対象テーブルのメタデータロックを行っているのDBコネクションを切断できます。

実行例

ロックが解放されると、ALTER 文の実行が終了しています。

終わりに

普段はあまり気にしないメタデータロックですが、クエリチューニングでIndexの作成等を行おうとすると時々メタデータのロック待ちで待たされることがあります。

この記事がクエリチューニング中のトラブル対処の一助になれば幸いです。

スマートスタイルTECHブログについて

スマートスタイルTECHブログでは、日頃MySQLのサポート業務に従事している有資格者で構成された技術サポートチームがMySQLに関する技術情報を発信しています。データベースのお困りごとはお気軽にご相談下さい。

よかったらシェアしてね!
  • URLをコピーしました!
目次