LayerX エンジニアブログ

LayerX の エンジニアブログです。

MySQL の UPDATE で IN 句の要素が多すぎてデッドロックした話 #LayerXテックアドカレ

この記事は、LayerX Tech Advent Calendar 2024 の7日目の記事です。

tech.layerx.co.jp

こんにちは。バクラクビジネスカード開発チーム エンジニアの iwamatsu です。

何か書くことないかな〜と頭を悩ませていたところ、見たことのない不思議なデッドロックに出くわしたので、今回はそれについて書こうと思います。

実行環境

  • バージョン: MySQL 8.0.39
  • ストレージエンジン: InnoDB
  • トランザクション分離レベル: REPEATABLE READ

発生した事象

以下のようなユーザーテーブルがあるとします。

CREATE TABLE `users` (
    `id` INT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `lucky_color` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

ユーザーが2,000人いるとします。

INSERT INTO users (id, name, lucky_color)
VALUES
    (1, 'user1', 'white'),
    (2, 'user2', 'white'),
    (3, 'user3', 'white'),
    ...
    (2000, 'user2000', 'white');

まず Tx1 で id が3のユーザーのラッキーカラーを赤に更新します。

-- Tx1
BEGIN;
UPDATE users SET lucky_color = 'red' WHERE id = 3;

次に Tx2 で id が偶数のユーザーのラッキーカラーを青に更新します。

-- Tx2
BEGIN;
UPDATE users SET lucky_color = 'blue' WHERE id IN (2, 4, 6, ...2000);

そして Tx1 に戻り、id が1のユーザーのラッキーカラーを緑に更新します。

-- Tx1
UPDATE users SET lucky_color = 'green' WHERE id = 1;

これでデッドロックが発生します (!?)。

Tx1 は id が奇数のユーザー、Tx2 は id が偶数のユーザーをそれぞれ更新しているので、ロックが競合することはないように思えますが、なんとデッドロックが発生するのです。

なぜこれがデッドロックになるかについて、以下で説明していきます。

調査手順

InnoDB モニターを見る

デッドロックの原因を調査する際は、まず InnoDB モニター (SHOW ENGINE INNODB STATUS) を見るのがおすすめです。

InnoDB モニターを見る際は、事前に標準モニターとロックモニターを有効化しておく必要があります。

SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;

有効化したらデッドロックを再現させて、出力内容を見てみましょう。

SHOW ENGINE INNODB STATUS\G

LATEST DETECTED DEADLOCK というセクションに、以下のような構成でデッドロックの情報が出力されているかと思います。

*** (1) TRANSACTION:
トランザクション(1)の情報

*** (1) HOLDS THE LOCK(S):
トランザクション(1)が保持しているロック

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
トランザクション(1)が取得しようとしているロック

*** (2) TRANSACTION:
トランザクション(2)の情報

*** (2) HOLDS THE LOCK(S):
トランザクション(2)が保持しているロック

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
トランザクション(2)が取得しようとしているロック

*** WE ROLL BACK TRANSACTION (2)

まずは Tx1 の情報を見てみます。

Tx1 は user3 のレコードロックを保持しています。

RECORD LOCKS space id 60646 page no 5 n bits 496 index PRIMARY of table `work`.`users` trx id 1301525 lock_mode X locks rec but not gap
Record lock, heap no 425 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 00000013dc15; asc       ;;
 2: len 7; hex 0100000126034b; asc     & K;;
 3: len 5; hex 7573657233; asc user3;;
 4: len 3; hex 726564; asc red;;

field0 が id、field1, 2 が制御用の値、field3, 4 が name, lucky_color です。

そして次に user1 のレコードロックを取得しようとしています。

RECORD LOCKS space id 60646 page no 5 n bits 496 index PRIMARY of table `work`.`users` trx id 1301525 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000013daa3; asc       ;;
 2: len 7; hex 82000000900110; asc        ;;
 3: len 5; hex 7573657231; asc user1;;
 4: len 5; hex 7768697465; asc white;;

ここまでは実行した UPDATE と一致しているので想定通りです。

次に Tx2 の情報を見てみます。

Tx2 は user1 と user2 のレコードロック (とギャップロック) を保持しています (!?)。

RECORD LOCKS space id 60646 page no 5 n bits 496 index PRIMARY of table `work`.`users` trx id 1301526 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000013daa3; asc       ;;
 2: len 7; hex 82000000900110; asc        ;;
 3: len 5; hex 7573657231; asc user1;;
 4: len 5; hex 7768697465; asc white;;

Record lock, heap no 424 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000013dc16; asc       ;;
 2: len 7; hex 02000000ec1d45; asc       E;;
 3: len 5; hex 7573657232; asc user2;;
 4: len 4; hex 626c7565; asc blue;;

user2 はIN 句で指定しているので想定通りですが、指定していない user1 のロックもなぜか保持しています。

そして次に user3 のレコードロック (とギャップロック) を取得しようとしています (!?)。

RECORD LOCKS space id 60646 page no 5 n bits 496 index PRIMARY of table `work`.`users` trx id 1301526 lock_mode X waiting
Record lock, heap no 425 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 00000013dc15; asc       ;;
 2: len 7; hex 0100000126034b; asc     & K;;
 3: len 5; hex 7573657233; asc user3;;
 4: len 3; hex 726564; asc red;;

これまた IN 句で指定していない user3 のロックを取得しようとしています。

一旦ここまでの情報から、以下の状態でデッドロックが発生したことが分かりました。

  • Tx1 は user3 のレコードロックを保持、user1 のレコードロックを取得しようとしている。
  • Tx2 は user1, 2 のレコードロックを保持、user3 のレコードロックを取得しようとしている。

実行計画を見る

どうやら Tx2 が想定外の挙動をしていそうなので、次は実行計画を見てみます。

EXPLAIN UPDATE users SET lucky_color = 'blue' WHERE id IN (2, 4, 6, ...2000);

すると以下のような結果になりました。

Name         |Value      |
-------------+-----------+
id           |1          |
select_type  |UPDATE     |
table        |users      |
partitions   |           |
type         |index      |
possible_keys|PRIMARY    |
key          |PRIMARY    |
key_len      |4          |
ref          |           |
rows         |2000       |
filtered     |100.0      |
Extra        |Using where|

通常 IN 句で主キーを複数指定した場合、type は range (指定範囲のみ検索) を期待しますが、実行計画を見てみると type が index (フルインデックススキャン) になっています。

IN 句の要素が1,000個と多いせいで、オプティマイザがフルスキャンを選択し、テーブル全体のレコードを走査 & ロックしようとしている可能性がありそうです。

IN 句の要素を減らしたらどうなるか、試しに要素数を500個まで減らして確認してみます。

Name         |Value      |
-------------+-----------+
id           |1          |
select_type  |UPDATE     |
table        |users      |
partitions   |           |
type         |range      |
possible_keys|PRIMARY    |
key          |PRIMARY    |
key_len      |4          |
ref          |const      |
rows         |500        |
filtered     |100.0      |
Extra        |Using where|

すると type が range に変わりました。

実行計画の結果から、IN 句の要素が多いためフルスキャンが選択され、Tx2 がテーブル全体のレコードをロックしようとした、という仮説が立てられます。

performance_schema を見る

念の為、仮説が正しいか最後に実際の挙動も確認しておきましょう。

Tx2 だけを再度実行し、ロック状況を確認します。

ロック状況は performance_schema.data_locks から参照できます。

SELECT
    ENGINE_TRANSACTION_ID,
    OBJECT_NAME,
    INDEX_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    LOCK_DATA
FROM performance_schema.data_locks;

結果は以下のようになりました。

ENGINE_TRANSACTION_ID|OBJECT_NAME|INDEX_NAME|LOCK_TYPE|LOCK_MODE|LOCK_STATUS|LOCK_DATA             |
---------------------+-----------+----------+---------+---------+-----------+----------------------+
              1301542|users      |          |TABLE    |IX       |GRANTED    |                      |
              1301542|users      |PRIMARY   |RECORD   |X        |GRANTED    |supremum pseudo-record|
              1301542|users      |PRIMARY   |RECORD   |X        |GRANTED    |1                     |
              1301542|users      |PRIMARY   |RECORD   |X        |GRANTED    |2                     |
              1301542|users      |PRIMARY   |RECORD   |X        |GRANTED    |3                     |
...
              1301542|users      |PRIMARY   |RECORD   |X        |GRANTED    |2000                  |

テーブル全体のレコードをロックしていることが確認できたので、仮説は正しそうです。

まとめると、以下の流れでデッドロックが発生したということが分かりました。

  1. Tx1 が user3 のレコードをロックする。
  2. Tx2 の IN 句が多いためフルスキャンが選択され、テーブル全体のレコードをロックを試みる。
  3. Tx2 が user1, 2 のレコードをロックし、user3 のレコードをロックしようとする。
  4. Tx1 が user1 のレコードをロックしようとする。 ← デッドロック発生

解決策

IN 句の要素を減らす

上述の通り、IN 句の要素を減らすことでフルスキャンを避けられます。

ただし具体的に何個にすれば良いのかはテーブル定義やデータに依存するため、実際に検証しながら探っていく必要があります。

インデックスを指定する

使用して欲しいインデックスを指定することも解決策の1つです。

以下のように FORCE INDEX で PRIMARY を指定するとフルスキャンを避けられます。

UPDATE users FORCE INDEX (PRIMARY) SET lucky_color = 'blue' WHERE id IN (2, 4, 6, ...2000);

ただしフルスキャンが選択されるということは、オプティマイザがその方が効率が良いと判断しているわけなので、IN 句の要素を減らすなどある程度チューニングしたうえで、インデックスを指定するのが良さそうです。

対応方針

実際に発生したケースでは、上記2つを組み合わせて対応する方針としました。

上で挙げた以外にも解決策はありそうですが (トランザクション分離レベルを下げるなど)、今回は調査しきれていないので割愛します。

もし他のやり方をご存知でしたら、是非コメントいただけますと幸いです！

終わりに

今回は実際に発生したデッドロックと、その調査過程についてまとめました。まとめてみるとシンプルですが、実際にはドキュメントを見つつ、手元で色々試しながら原因を探っていく地道な作業でした。知識不足を痛感したので、年末年始に MySQL の書籍でも買って勉強しようかと思います。

明日は同じくバクラクビジネスカード開発チーム Tech Lead の budougumi0617 さんの記事が出る予定です！お楽しみに！