MySQLの DELETE / INSERT によるデッドロックをインデックス観点で検証する

この記事は OPENLOGI Advent Calendar 2023 18日目の記事です。

オープンロジは物流をアウトソーシングできるサービスを提供しており、様々な方法で、在庫の管理や配送を行うことができます。

• EC プラットフォームの在庫情報や受注情報からの在庫管理や配送指示
• オープンロジが提供している API を使用しての在庫管理や配送指示
• オープンロジが提供している事業者様向けの管理画面からの在庫管理や配送指示

その仕組みの中の一部でデッドロックが発生したため、その時に行った検証を記載していきたいと思います。

詳細にはかけませんが、問題が起きたときの状況は下記のような感じでした。

• 更新処理を行うワーカーの数を5倍にし、処理数を向上させようとした
• デッドロックが起きた箇所は、 DELETE / INSERT を行っていた
• DELETE は、インデックスが貼られている親テーブルのキーを指定して行われていた
• DELETE 時に親テーブルの id は、 N 件指定されていた
• DELETE 時に空振りは起きていない
  • アプリケーション側で、空振りを避けるように考慮されていた
• MySQLのトランザクション分離レベルは、 REPEATABLE READ を使用

オープンロジのテーブル情報を一般公開することはできないため、雰囲気にはなりますが、下記のような構成でした。

create table children (
  id integer primary key auto_increment,
  parent_id int not null, -- parents.id とリレーションがある
  value varchar(255) not null
  created_at   timestamp null,
  updated_at   timestamp null
);

create index children_prent_id_index
    on children (prent_id);

children テーブルは、 parents テーブルとリレーションがあり、parents.idを保持しているような構成です。
children.parent_id はインデックスが貼られています。
削除時には、 children.parent_id に対して、N 件の id が指定されていました。

delete from children where parent_id in (1, 2, 3...)

SHOW ENGINE INNODB STATUS を確認すると、２つのトランザクションでインテンションロックの挿入時にロック待ちとなり、デッドロックが発生していました。

~省略~
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 93 page no 16252 n bits 816 index
children_parent_id_index of table `test`.`children` trx id 427751285 
lock_mode X locks gap before rec insert intention waiting
~省略~
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 93 page no 16252 n bits 816 index
children_parent_id_index of table `test`.`children` trx id 427751250 
lock_mode X locks gap before rec insert intention waiting
~省略~

雰囲気にはなりますが、以上がデッドロックが発生した状況となります。

なぜデッドロックが起きたのか

インテンションロックの挿入により、ロックの取得待ちが発生していたため、その前の処理に問題があると考えられます。
そのため、DELETE に問題がありそうです。

MySQLのリファレンスでは、下記のように記載があります。

locking reads (SELECT と FOR UPDATE または FOR SHARE)、UPDATE および DELETE ステートメントの場合、実行されるロックは、ステートメントが一意の検索条件を持つ一意のインデックスを使用するか、範囲タイプの検索条件を使用するかによって異なります。

・一意の検索条件を使用した一意のインデックスの場合、InnoDB は見つかったインデックスレコードのみをロックし、その前にあるギャップはロックしません。

・他の検索条件および一意でないインデックスの場合、InnoDB は、gap locks または next-key locks を使用してスキャンされたインデックス範囲をロックし、他のセッションによる挿入を範囲の対象となるギャップにブロックします。 ギャップロックおよびネクストキーロックについては、セクション15.7.1「InnoDB ロック」 を参照してください。

https://dev.mysql.com/doc/refman/8.0/ja/innodb-locks-set.html

上記のことから、一意ではないインデックスで DELETE を行ったために ギャップロック が発生し、 INSERT 時に、インテンションロックの挿入が行われ、その結果デッドロックが起きたと考えられます。

また、今回はインテンションロックの挿入が２つ存在しているため、３つ以上のトランザクションが同時に発生していると考えられます。

実際に検証を行います。

検証

準備

MySQL の用意

v8.0.33 でも再現したため、こちらのバージョンを使用します。
せっかくなので、最新のMySQLで検証できればよかったのですが、docker compose の pull にかなりの時間がかかり、検証環境を準備を諦めたため、ローカルにたまたまビルド済みであった MySQL のバージョンを使用しています（悲）

検証用のテーブルを作成

実際のテーブルと比べるとかなりミニマムですが、不要な情報を削除し、下記のテーブルで検証を行います。

create table children (
  id integer primary key auto_increment,
  parent_index_id int not null
);

create index children_parent_index_id_index
    on children (parent_index_id);

※インデックスが貼られていることをわかりやすくするため、 parent_id ではなく、parent_index_id という名前に変えています。

データの作成

Delete / Insert が行われるテーブルのため、 id 、 parent_index_id ともに非連続になるようにデータを作成しておきます。

insert into children (id, parent_index_id) 
values (1, 1),(5, 5),(9, 9),(13, 13),(17, 17), (21, 21);

デッドロックの検証（インデックスを使用して削除）

デッドロックの再現のために、３つトランザクションを用意して検証をします。

左の番号の順にクエリを実行します。
parent_index_id を in 句を使用して複数のレコードを削除をします。
また、デッドロックが発生した状況を再現するために、parent_index_id を交差するような形で指定します。
INSERT では、同じ parent_index_id を登録します。

# Trx1
1) begin;
4) delete from children where parent_index_id in (1, 21);
7) insert into children (parent_index_id) values (1), (21);

# Trx2
2) begin;
5) delete from children where parent_index_id in (5, 17);
8) insert into children (parent_index_id) values (5), (17);

# Trx3
3) begin;
6) delete from children where parent_index_id in (9, 13);
9) insert into children (parent_index_id) values (9), (13);

結果

ユニークではないインデックスを使用すると、ギャップロックが起き、 Trx2 の INSERT 時にデッドロックになりました。

デッドロックの検証（PKを使用して削除）

リファレンスサイトでは、 一意の検索条件を使用した一意のインデックスの場合、InnoDB は見つかったインデックスレコードのみをロックし、その前にあるギャップはロックしません とのことなので、 SELECT を一度行い、 PK で削除を行ってみます。
SELECT に関しては、今回の検証ではスキップしても良いですが、実際に実装するアプリケーションの処理にあわせて、あえて記載しています。

※サブクエリでも検証したくなりますが、 You can't specify target table 'children' for update in FROM clause というエラーメッセージが表示され、更新を行うテーブルに対して同じテーブルでサブクエリを使用することはできません。

# Trx1
1) begin;
4) select * from children where parent_index_id in (1, 21);
7) delete from children where id in (1, 21);
10) insert into children (parent_index_id) values (1), (21);

# Trx2
2) begin;
5) select * from children where parent_index_id in (5, 17);
8) delete from children where id in (5, 17);
11) insert into children (parent_index_id) values (5), (17);

# Trx3
3) begin;
6) select * from children where parent_index_id in (9, 13);
9) delete from children where id in (9, 13);
12) insert into children (parent_index_id) values (9), (13);

結果

デッドロックは発生ぜず、 PK で削除することにより、安全に処理できました。

デッドロックの検証（ユニークインデックスを使用して削除）

PK ではギャップロックを発生させずに安全に削除ができましたが、ユニークインデックスでも安全に削除が可能かを検証をします。

テーブルを用意します。

create table children (
  id integer primary key auto_increment,
  unique_index_id int not null
);

create unique index children_unique_index_id_unique_index
    ON children (unique_index_id);

わかりやすいように、 unique_index_id というカラム名にしています。
（parent をつけ忘れました😂）

id 、unique_index_id ともに非連続になるようにデータを作成します。

insert into children (id, unique_index_id) 
values (1, 1),(5, 5),(9, 9),(13, 13),(17, 17), (21, 21);

クエリは下記の順番で実行をします。

# Trx1
1) begin;
4) delete from children where unique_index_id in (1, 21);
7) insert into children (unique_index_id) values (1), (21);

# Trx2
2) begin;
5) delete from children where unique_index_id in (5, 17);
8) insert into children (unique_index_id) values (5), (17);

# Trx3
3) begin;
6) delete from children where unique_index_id in (9, 13);
9) insert into children (unique_index_id) values (9), (13);

結果

デッドロックは起きませんでしたが、ギャップロックは発生しました。

・一意の検索条件を使用した一意のインデックスの場合、InnoDB は見つかったインデックスレコードのみをロックし、その前にあるギャップはロックしません。

MySQL のソースコードまでは追っていないため、推測にはなりますが、今回の事象を考えると、上記の一意のインデックスというのは、PK を指しているのかもしれません。

高トラフィックなWebアプリケーションからの更新、スケールしているワーカーからの更新などでは、ユニークインデックスでも、デッドロックが発生する可能性がありそうです。

まとめ

ここまでの検証結果をまとめると、下記のようになりました。

• DELETE 時にインデックス、ユニークインデックスのカラムを使用するとギャップロックが起き、デッドロックになる可能性がある
• インデックスよりは、ユニークインデックスの方がギャップロックは起きづらい
• DELETE 時に PK を使用するとギャップロックが起きず、安全に削除できる

感想

デッドロックの検証から、 MySQL の挙動について多くの学びがあり、とても勉強になりました。

気になる点としては、 ID Required という SQL アンチパターンがありますが、今回検証した MySQL のバージョンにおいては、id をすべてにつけておいたほうが安全に削除できそうな気がしました。
もしかしたら、SELECT FOR UPDATE を適切に実行できれば、IDに依存せずに適切に削除も可能にはなるのかもしれませんが、SELECT FOR UPDATE でも一意ではないインデックスの場合、同様にギャップロックが発生する可能性があるとは思っているため、なかなか大変かもしれません。
SELECT FOR UPDATE に関してはタイミングをみて、また検証ができればと思います。

今回の検証に関しては以上となります。

クエリも用意していますので、もし気になる方はぜひ検証をしてみてください！