SELECTでデッドロック発生！？メタデータロックによるデッドロックにご用心

この記事はカケハシ Advent Calendar 2024 の 10日目の記事です。

AI在庫管理というプロダクトチームのソフトウェアエンジニアをしている金子です。今回は本番環境で発生したデッドロックをきっかけに、その原因に関する調査結果と対策についてまとめていきます。

本番環境で Query 実行中にデッドロック

事象

まずは実際に発生した事象についてです。いつも通りリリース作業を実施していた時のことでした。リリース内容はレコードの無い不要になったテーブルの削除。そのテーブルに対する参照箇所が無いことは確認済みで、メンテナンスモードなどの対応は不要で、通常のリリースにおけるマイグレーションでDBを修正できる想定でした。

本番環境におけるエラー発生を通知するSlackチャンネルを監視しながら、リリース処理を行うGitHub Workflowを見守っていると、突然複数のエラーが通知され始めました。

AI在庫管理ではGraphQLを使用していますが、通知されたエラーは複数の種類のQueryに関するもので、内容はどれも「DBでデッドロックが発生した」というものでした。

Queryの処理においては、DBのデータを参照するSELECTしか実施しないように実装しているはずなのに、なぜデッドロックが発生するのか、と混乱しました。（AI在庫では AWS Aurora MySQL 8.0.32 を使用しています）

デッドロック時に実施していたDBマイグレーション

デッドロックが発生した時間帯（時間としては0.5sec程度の間）は、ちょうど本番環境のDBに対するマイグレーション処理（変更操作）が行われている時間帯と一致していました。

その日のリリースで実施されていたDBマイグレーションは、不要になったテーブル（レコードは入っていない）の削除処理のみ。現状AI在庫管理では100以上のテーブルが定義されている中、少しでも認知負荷を下げるべく、カイゼン活動の一貫として実施したものでした。

InnoDB モニターの確認

通常デッドロックが発生した場合、InnoDB ロックモニターを有効化していれば、SHOW ENGINE INNODB STATUS コマンドにより表示される InnoDB モニターに、直近発生したデッドロックに関する情報が表示されるはずです。ところが実際に InnoDB ロックモニターを確認してみると、表示されたデッドロック情報は、今回デッドロックが発生した時刻とは全く別時刻のもの。ロック取得対象のテーブルなども今回のデッドロックとは全く関係の無いテーブルに関する記録でした。

「全くレコードの入っていないテーブルの削除処理により、SELECT処理がデッドロックし、InnoDBロックモニターにも表示されない」という事象について、何が起きていたのかを明らかにするべく、その原因について調べてみることにしました。

MySQL におけるロックについて

まずは、MySQLのロックについて改めて整理していきます。

ロックの種類

MySQLのロックには大きく分けて、ストレージエンジンレイヤでのロックと、サーバーコアでのロックの2種類のロックが存在します。

ストレージエンジンレイヤでのロックには、以下のような種類があります。

ギャップなしレコードロック
レコードロック
ギャップロック
ネクストキーロック

いずれのロックもテーブル全体ではなく、一部のインデックスレコードとインデックスレコード間のギャップの単位でロックを取得します。ロックを取得する主な目的はレコードの更新です。

一方サーバーコアでのロックには以下のような種類があります。

メタデータロック（MDL）
インテンションロック
グローバルリードロック
ユーザー定義ロック

こちらはいずれもインデックスレコードやそのギャップ単位ではなく、テーブルやMySQL全体の単位でロックを取得します。テーブル定義の更新などの目的で使用されます。

トランザクション分離レベルとロックの取得

ストレージエンジンレイヤでのロックに関しては、トランザクション分離レベルが SERIALIZABLE 以外の場合（READ UNCOMMITTED、READ COMMITTED、REPEATABLE READの場合）は、SELECT 時にはロックを取得しません。

一方で、サーバーコアでのロックに関しては、SELECT 操作においてもMDLを取得します。これはトランザクション分離レベルの設定に依りません。また、MDLの取得状況については、 performance_schema.metadata_locks を参照することで確認することができます。

試しに以下のようなテーブルを用意して、SELECT 時の MDL の取得状況を確認してみます。

CREATE TABLE a (i INT AUTO_INCREMENT PRIMARY KEY) ENGINE = InnoDB;
INSERT INTO a (i) VALUES(1);

CREATE TABLE b (i INT AUTO_INCREMENT PRIMARY KEY) ENGINE = InnoDB;
INSERT INTO b (i) VALUES(1);

これらの a, b のテーブルを JOIN して SELECT します。

START TRANSACTION;

SELECT * FROM a JOIN b on a.i = b.i;

この状態で performance_schema.metadata_locks を確認すると、 a, b 両方のテーブルに対してMDLが取得されていることが分かります。

SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS FROM performance_schema.metadata_locks;

+-------------+-------------------+-------------+-------------+
| OBJECT_TYPE | OBJECT_NAME       | LOCK_TYPE   | LOCK_STATUS |
+-------------+-------------------+-------------+-------------+
| TABLE       | a                 | SHARED_READ | GRANTED     |
| TABLE       | b                 | SHARED_READ | GRANTED     |
+-------------+-------------------+-------------+-------------+

このように SELECT 操作はストレージエンジンレイヤのロックは取得しなくても、MDLを取得することが分かります。

MDLを取得するその他の操作

上記で確認した SELECT 以外にも、MDLを取得する操作があります。特に以下の操作は、共有MDLと競合する（もちろん排他MDLとも競合します）排他MDLを取得するため、注意が必要です。

CREATE TABLE, DROP TABLE
オンライン ALTER TABLE（ステートメントの開始時と、終了時に排他MDLを取得する）

発生したデッドロックの正体

ここまで整理すると、今回 DROP TABLE 中に SELECT 操作がデッドロックされた原因が見えてきます。レコードの入っていない不使用テーブルでしたが、そのテーブルには外部キー制約が貼られていたことがポイントです。今回のデッドロックの正体は、以下のように2つのプロセスがMDL取得しようとして発生したデッドロックだったのです。

SELECT 操作が JOIN により複数テーブルに対して共有MDLを取得しようとしていた
DROP TABLE が削除対象のテーブルに加え、外部キー制約が設定されている複数のテーブルに対して排他MDLを取得しようとしていた
これらの2つのプロセスが、それぞれ複数のMDLを取得していく際にデッドロックが発生した

デッドロックの検出

デッドロックが発生した原因は分かりました。ですがまだ「なぜデッドロックの記録が InnoDB ロックモニターに表示されなかったのか」という疑問が残っています。

前述の通り、MySQLでは SHOW ENGINE INNODB STATUS コマンドによって、直近発生したデッドロックの状況を確認することができます。デッドロックが発生した場合は、このコマンドの結果の一部に LATEST DETECTED DEADLOCK というセクションが表示されます。

ただし、この InnoDB ロックモニターは、全てのデッドロックを表示できるわけではないことが、公式ドキュメントに記載されています。

InnoDB は、MySQL LOCK TABLES ステートメントで設定されたテーブルロックまたは InnoDB 以外のストレージエンジンで設定されたロックが関連しているデッドロックを検出できません。 ref: https://dev.mysql.com/doc/refman/8.0/ja/innodb-deadlock-detection.html

つまり、InnoDB ロックモニターが表示するのは、ストレージエンジン（= InnoDB）レイヤでのロックによるデッドロックに関する情報であり、サーバーコアでのロック（MDLなど）によるデッドロックは表示されないのです。これが今回の DROP TABLE と SELECT 操作による「MDL取得におけるデッドロック」が、InnoDB ロックモニターに表示されなかった理由です。

メタデータロックによるデッドロックの再現

ここまで整理してきたことを、実際に手元の MySQL で再現しながら検証してみたいと思います。

先ほど SELECT 操作によるMDLの取得を確認した a、b のテーブルに加え、これらのテーブルに対して外部キー制約を持つテーブル c （レコードは無し）を用意します。

CREATE TABLE a (i INT AUTO_INCREMENT PRIMARY KEY) ENGINE = InnoDB;
INSERT INTO a (i) VALUES(1);

CREATE TABLE b (i INT AUTO_INCREMENT PRIMARY KEY) ENGINE = InnoDB;
INSERT INTO b (i) VALUES(1);

CREATE TABLE c (
    a_i INT,
    b_i INT,
    FOREIGN KEY (a_i) REFERENCES a(i),
    FOREIGN KEY (b_i) REFERENCES b(i)
) ENGINE = InnoDB;

ここから2つのプロセスを操作して行きます。まずは SELECT を実行するプロセス（プロセス1）でテーブル b を SELECT し、テーブル b の MDL を取得します。

-- プロセス1
START TRANSACTION;

SELECT * FROM b;

次に DROP TABLE を実行するプロセス（プロセス2）で、テーブル c の DROP TABLE を実行します。

-- プロセス2
DROP TABLE c;

プロセス2はテーブル c が持っている外部キー制約によってによってテーブル b の排他MDLを取得しようとしますが、プロセス1がすでにテーブル b の共有 MDL を先に取得しているため、ロックの取得待ちになります。プロセス2はテーブル a についても同様に排他MDLを取得しますが、こちらはプロセス1がMDLを取っていないため、プロセス2が排他MDLを取得します。

続いてプロセス1で、テーブル a を SELECT し、テーブル a のMDLを取得しようとします。

-- プロセス1
SELECT * FROM a;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

するとデッドロックが発生します。プロセス2がすでにテーブル a の排他ロックを取得済みだからです。結局以下のような状態になりデッドロックになったのです。

プロセス1はテーブル b の共有MDLを取得し、テーブル a の共有MDLを取得待ち
プロセス2はテーブル a の排他MDLを取得し、テーブル b の排他MDLを取得待ち

ここで SHOW ENGINE INNODB STATUS を実行して InnoDB ロックステータスを確認しても、今回のテーブルに関するデッドロックの記録は残っていません。（これまで1度もデッドロックが発生していなければ、 LATEST DETECTED DEADLOCK のセクション自体が表示されません）

今回発生した事象を期待通りに再現することができました。再現においては、SELECT を実行するプロセス1で、テーブル a と b を順番に SELECT していますが、1つの SELECT 操作でテーブル a と b が JOIN されている場合も、a と b のテーブルそれぞれについて順次MDLを取得する間に、DROP TABLE が実行されることで、デッドロックが発生する場合もあると考えられます（実際に発生したデッドロックにおいては JOIN を伴う SELECT 操作がデッドロックしていました）。

DROP TABLE 実行時の SELECT 操作デッドロックの回避策

SELECT 操作で発生したデッドロックについて、その原因を理解することはできました。一方で、今後同様にのように DROP TABLE 時に SELECT 操作がデッドロックしてしまうことを回避するにはどうすればよいのでしょうか。その回避策について考えてみます。

問題は DROP TABLE 実行時に、外部キー制約が設定されているテーブルも含めて（複数のテーブルに対して）排他MDLを取得してしまうことです。これを回避するために、事前に外部キー制約の削除（ALTER TABLE <table_name> DROP FOREIGN KEY <foreign_key_name>）を実行しておくことで、DROP TABLE 実行時には削除対象のテーブルのみに対する排他MDLの取得で済みます。 ALTER TABLEによる外部キー制約削除時に、MDLを取得すること自体はDROP TABLEと同じですが、MDLの取得対象が異なります。外部キー制約削除時にMDLを取得するのはALTER TABLEの対象テーブルと、外部キーとして参照されるカラムを持つテーブルの2テーブルのみです。通常、削除対象のテーブルに対するアプリケーションからの参照は事前に削除しているはずなので、実際には参照先のテーブルに対するMDL取得待ちのみが発生することになり、デッドロックは発生しないはずです。

まとめ

他テーブルに外部キー制約が貼られた未使用テーブル（レコードの無いテーブル）の DROP TABLE 処理と、SELECT 処理がMDLの取得において競合することで SELECT 操作にデッドロックが発生することを見てきました。またMDLによるデッドロックはストレージエンジンレイヤでのロック起因ではないため、InnoDB ロックモニターではその記録を確認することができませんでした。

テーブル削除時には、「外部キー制約を先に削除しておくこと」に留意することで、デッドロックを回避しながら、より安全にテーブル削除を実行できます。