MySQLのMetadata Lockで困るケースと解決策的なもの #MySQL

この記事は、Wano Group Advent Calendar 2021 の記事です。

MySQL の Metadata Lockとは？

その前にMetadataって何?という話ですが、Metadata自体の説明はMySQLのドキュメントを探せませんでした。
まぁ、普通に考えて、information_schemaに入っている、tableとかcolumnとかtriggerとかの情報かと思います。

Metadata Lockというのは、Metadataを変更する際にかかるロックと思っておけばいいでしょう。

端的に言うと、Metadata Lockでselectが待たされれるということが問題となります。
待たされる条件は、書いてしまえばすごいシンプルです。

短いtransaction中であれば、問題にならないケースも多いでしょう(扱ってるアプリケーションによると思いますが)。
なので、問題となる具体的な条件は、下記になるかなと思います。

1のトランザクションが、2がアプリケーションとして待てる時間以上の長さのトランザクションであれば、問題となります。

要は、

長めのトランザクションを発行している最中にalterを行うと、長めのトランザクションが終わるまで、alterは待たされるし、そのalterが終わるまで、他のselectが待たされるという問題が出る可能性があります。

下記のような手順を行うと再現できます。

(接続1) begin
(接続1) 対象テーブル(a)をselect
(接続2) alter table(a) ... 待たされる(Waiting for table metadata lock)
(接続3) 対象テーブル(a)をselect(クエリキャッシュの効かないselect) ... 待たされる(Waiting for table metadata lock)
(接続1) commit
(接続2) alter成功
(接続3) selectの結果が返る

起こる条件はわかるのですが、これを避けるためにはどうしたら良いのか？

下記で、トランザクションで行われていることはわかるのですが、その前に別のテーブルがselectされているかもしれないので、あまり使えません。

select trx_mysql_thread_id,command,state,info 
  from informatio_schema.INNODB_TRX t 
  join PROCESSLIST p on p.id = t.trx_mysql_thread_id;

他にも使えるものはあるかもしれませんが、ちょっと探しきれませんでした。

ただ、Web系のシステムによっては、下記で十分なケースもあるかなと思います。

Web系のシステムであれば、長いトランザクションがあるのは、基本的には、バッチ処理とかデーモン化されている処理とかだと思います。もしかしたら、管理画面の処理とかもあるかもしれません。
ですが、一般ユーザーのWebブラウザからの処理であれば、1秒もかかるような処理はないはずかと思います。

なので、バッチ処理やデーモンを一時的に止めてから行う、管理画面を使わないように周知してから行う、というようなことでも大方避けられるのではないかと思います。

もちろん携わっているアプリケーションしだいですので、0.1秒も止められないみたいなシステムだともっと別の方法が必要になるかもしれません。

もうちょっと明確に問題を避けられる(というか、確認できる)方法があれば良かったんですが、時間の関係もあり、そこまで調べきれませんでした。ちょっとでも助けになれば幸いです。