MySQLでmetadata lockにハマりやすいパターン

しばらくブログエントリあげてませんでしたが、引き続きMySQL関係のお仕事していたので、復活弾もやっぱりMySQLネタです。

そもそも

「metadata lockって何よ？」
とか
「Waiting for Table metadata lockでハマったらどうしたら良いの？」
って話は、過去にエントリ
MySQL5.6以前でmetadata lock発生の犯人を後追いする - なからなLife
にあげているので、そちらも参照してください。

とはいえ長いので、簡潔に書くとこんな感じです。

metadata lockって何よ？

metadata＝テーブル定義情報のWrite Lock。DML同士は共有、DDLとは排他。

Waiting for Table metadata lockでハマったらどうしたら良いの？

select * from information_schema.innodb_trxを見て、TRX_STARTED(トランザクション開始時刻）が異常に古いものからKillする。

ハマリパターン

で、本題に入ります。
「metadata lockで処理が詰まるケースにハマリやすいパターンってこんなだよね」
っていうのを、経験にもとづいて整理してみました。

他にもこういうのあるぜ、ってのがあったら、是非教えていただきたいです。

パターン1.「autocommit=OFF」

普通そうするよね、とは思うけど、これが引き金の一つです。

そもそも、「autocommit=ON」だったら、SQL単位でしかmetadata lockを取らないから、そんなに長引くこともない！？
（1日かけても帰ってこないようなSELECTを投げるケースを除く）

「autocommit=ON」にすると、複数SQLをまとめて1つのトランザクションとしたい場合には、明示的にSTART TRANSACTION宣言しないといけないので、「autocommit=OFF」の時のような暗黙のトランザクションの閉じ忘れ、というケースは防げそう。

パターン2.「ポーリング」

「キューテーブルをポーリング（SELECT）して、処理対象があったら～」という仕様にもとづいてプログラムを書くと、「処理対象がなかったら何もしない」となるケースが多いです。
本当に何もしないと、「autocommit=OFF」ではSELECTによる暗黙のトランザクションが開始され、そのまま放置されることになります。

実践ハイパフォーマンスMySQL 第3版にも記載されている通り、「キューテーブル」は性能面の担保にも苦労するが、トランザクションの閉じ忘れも引き起こしやすいですね。

パターン3.「コネクションプーリング」

トランザクションをオープンしたままコネクションをプールに戻されると、原因特定がめんどくさいことになります。
コネクションをプールせず、都度確立、都度切断していれば、中途半端なものを引き継ぐこともないですね。

新規コネクション確立のオーバーヘッドが気にならないレベルの性能要件であったり、多重度の制御がうまくできていて無謀な同時接続数にならないのであれば、コネクションプールを使わない、という選択することもアリですね。

パターン4.「定時洗い替え」

ジョブスケジューラを利用して、テーブルの再作成（DROP/CREATE）、テーブルのリセット（TRUNCATE）、パーティション切り替えなどのDDLを発行すると、metadata lockによるトラブルの引き金となりやすいです。

そもそも、「Waiting for Table metadata lock」はDDLを発行したタイミングで起こる話なので、メンテナンス作業以外で頻繁に発生するとしたらコレ。

じゃあヤメロ、っていう話にはならないはずなので、「これを仕様に組み込んでいる場合、トランザクションの閉じ忘れにはより一層気をつけろ」ということ。

なお、Oracleで、DDLが同様のメタデータ情報のロック衝突を起こした場合、デフォルトだとリソースビジーで即エラーが帰ってきます。
MySQLにも同じような挙動をさせたい（DDLでロック待ちさせたくない）場合、「lock_wait_timeout=1」を設定します。
最小1秒、最大＆デフォルト31536000（1年）です。

DDL以外にも影響するので、公式ドキュメント参照のこと。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数