MySQL のデッドロックを調査した

こんにちは。アーキテクト見習いエンジニアの小池です。

年の瀬ですね。弊社は今日が最終業務日です。
掃除がてら今年あったことを何か記事にしておこうと思います。

とあるシステムでデータベースのデッドロックが原因のエラー調査をすることになり、普段データベースをガッツリ触らない僕にとって、この調査をすること自体が非常に勉強になったので記事にします。
そのシステムのデータベースは Amazon Aurora MySQL (InnoDB) なのですが、これまで SQL Server を使ったシステムに関わることが多かったので両者の違いも感じられました。

テストテーブル

実際のテーブルはお見せできないので、再現用にミニマムなテストテーブルを用意します。

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `value` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx1` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

カラムは PK の id の他に user_id と value の3つだけです。user_id は制約こそ持たせていないものの使い方としては FK になっている、そんなイメージです。 user_id での検索をよくするのでインデックスを付けています。

適当にデータも入れておきます。

id	user_id	value
1	100	50
2	100	100
3	200	10
4	100	90
5	300	70
6	200	120

ログを見てみる

さて、デッドロックが発生しているということは事前に分かってたので、どのクエリがデッドロックを起こしているのかを探します。

調べ方を調べ(笑)、とりあえず SHOW ENGINE INNODB STATUS; を見ることにしました。その中の "LATEST DETECTED DEADLOCK" という部分に直近のデッドロック情報が書かれています。
（以下はローカルのテストテーブルで実際にデッドロック発生させたときのログを一部修正したものです）

 ------------------------
 LATEST DETECTED DEADLOCK
 ------------------------
 2017-12-26 18:17:19 0x8a28
 *** (1) TRANSACTION:
 TRANSACTION 46120, ACTIVE 29 sec inserting
 mysql tables in use 1, locked 1
 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
 MySQL thread id 6, OS thread handle 37052, query id 664 localhost ::1 root update
 INSERT INTO test_table (user_id, value) values (500, 50)
 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 174 page no 4 n bits 80 index idx1 of table `sandbox`.`test_table` trx id 46120 lock_mode X insert intention waiting
 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
  0: len 8; hex 73757072656d756d; asc supremum;;
 
 *** (2) TRANSACTION:
 TRANSACTION 46121, ACTIVE 21 sec inserting, thread declared inside InnoDB 5000
 mysql tables in use 1, locked 1
 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
 MySQL thread id 7, OS thread handle 35368, query id 665 localhost ::1 root update
 INSERT INTO test_table (user_id, value) values (600, 50)
 *** (2) HOLDS THE LOCK(S):
 RECORD LOCKS space id 174 page no 4 n bits 80 index idx1 of table `sandbox`.`test_table` trx id 46121 lock_mode X
 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
  0: len 8; hex 73757072656d756d; asc supremum;;
 
 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 174 page no 4 n bits 80 index idx1 of table `sandbox`.`test_table` trx id 46121 lock_mode X insert intention waiting
 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
  0: len 8; hex 73757072656d756d; asc supremum;;
 
 *** WE ROLL BACK TRANSACTION (2)

ここから役立ちそうな情報を拾ってみます。

トランザクション (1) と (2) でデッドロックが発生して、ロック待ちをしているクエリはそれぞれ以下である。
(1) INSERT INTO test_table (user_id, value) values (500, 50)
(2) INSERT INTO test_table (user_id, value) values (600, 50)

トランザクション (1) は test_table の idx1 インデックスの最大インデックスから最終端までの挿入インテンション排他ネクストキーロックを取得するために待っている。
トランザクション (2) は test_table の idx1 インデックスの最大インデックスから最終端までの排他ネクストキーロックを持っていて、同じく最大インデックスから最終端までの挿入インテンション排他ネクストキーロックを取得するために待っている。

（デッドロックが発生したので）トランザクション (2) をロールバックした。

0: len 8; hex 73757072656d756d; asc supremum;;

この部分はロック範囲を表しているそうで、今回の例では idx1 に対するロックなので user_id の値が16進数 (=hex) で表示されるはずですがとてつもなく大きな数になっています 。
さらに supremum という文字もあります。調べてみると supremum は無限大的な意味合いで使われていて、最大インデックスよりさらに後ろを表しているらしいです。

---

2018/01/15 追記
73757072656d756d は supremum の 16進数表記でした。
hex 欄は asc 欄を16進数表記したもののようです。
列のデータ型とは直接対応してるわけではないようです。

---

また、lock_mode X が排他ロックを意味しています。

ロックの種類については InnoDB の行レベルロックについて解説してみる - あらびき日記 の記事を参考にさせていただき、"locks rec but not gap" とも "locks gap before rec" とも書かれていないことからネクストキーロックと判断しました。
（ちょっと自信ないです）

他にもこのあたりを参考にさせていただきました。

• MySQL(InnoDB)でのDeadLock調査 - Qiita
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.3 InnoDB のロックモード
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.6 InnoDB のレコード、ギャップ、およびネクストキーロック

発生箇所の特定

前節のロック待ちをしていたクエリから、デッドロックを引き起こしていたトランザクションを発行している部分のソースを特定できました。
そのトランザクションで対象のテーブルに関与しているクエリは以下の2つでした。
（実際のものとは異なります）

UPDATE test_table SET value = (value + 10) WHERE user_id = @usid;
INSERT INTO test_table (user_id, value) values (@usid, @vlu);

クエリを再現しながらロックを確認してみます。

このときのテーブル内データはテストテーブルのとこで書いた状態で、トランザクション分離レベルは MySQL のデフォルトの REPEATABLE READ です。

#	トランザクション (1)	トランザクション (2)	概要
1	BEGIN;	BEGIN;	2つのトランザクションが（ほぼ）同時に開始される
2	UPDATE test_table SET value = (value + 10) WHERE user_id = 500;		UPDATE 文が実行さるが、 user_id = 500 に一致するカラムがないので更新はなし
3		UPDATE test_table SET value = (value + 10) WHERE user_id = 600;	UPDATE 文が実行さるが、 user_id = 600 に一致するカラムがないので更新はなし
4	INSERT INTO test_table (user_id, value) values (500, 50);		user_id = 500 に INSERT
5		INSERT INTO test_table (user_id, value) values (600, 60);	user_id = 600 に INSERT

2 の UPDATE 文は条件に一致するものがないので空振りしますが、このとき InnoDB はこのトランザクション内でこの条件が空振りすることを保証するために（他のトランザクションでの変更の影響を受けないように）ロックをかけます。

idx1 インデックスの現在の最大値は 300 で、検索した値が 500 なので "300より大きい値のインデックスがない" ことを保証するため（ファントムリードを発生させないようにするため）に 300 < x < supremum の範囲に排他ネクストキーロックをかけます。

3 の UPDATE 文でも同様に空振りするため 300 < x < supremum の範囲に排他ネクストキーロックをかけます。

これが 2 のロックと競合しないのかですが、MySQL ドキュメント に

ネクストキーロックは、インデックス行ロックとギャップロックを組み合わせたものです。

---

インデックス内の最大値を上回るギャップ、およびインデックス内の実際のどの値よりも大きい値を持つ「最小上限」の擬似レコードがロックされます。最小上限は実際のインデックスレコードではないため、事実上、このネクストキーロックによってロックされるのは、最大インデックス値のあとにあるギャップのみです。

---

InnoDB のギャップロックは、「単に抑制的」です。つまり、ほかのトランザクションによるギャップへの挿入が停止されるだけです。したがって、ギャップ X ロックの効果はギャップ S ロックと同じです。

とあるので、このネクストキーロックは実質的に最大インデックスの後ろの排他 (X) ギャップロックであり、しかも排他ギャップロックは共有 (S) ギャップロックと同じなので競合しないと理解しました。

次に 4 の INSERT で最大インデックスより大きな値への挿入をするために 300 < x < supremum の挿入インテンション排他ネクストキーロックを取得しようと試みますが、トランザクション (2) が同じ範囲の排他ネクストキーロックを持っているのでロックを取得できずロック待ちとなります。
※ 通常の排他ロック (X) と インテンション排他ロック (IX) は競合します。（ロックタイプ互換性マトリクス より）

そして、5 でも同様に挿入インテンション排他ネクストキーロックを取得しようと試みるものの、トランザクション (1) のロック待ちとなり、ここでデッドロックが発生します。

対応方法

ようやく、デッドロックの原因特定ができたので対応を考えるわけですが、トランザクションの分離レベルを READ COMMITTED に下げるという方法があるようです。

こうすることで ギャップロックを行わなくなり 今回のデッドロックは発生しなくなります。
ですが、その代わりにファントムリードやファジーリードが発生するようになるので、トランザクションの他のクエリや業務要件などによって判断する必要があります。

今回の場合は、データの追加ロジック的に見てファントムリードやファジーリードが発生しないので分離レベルを下げることも可能でしたが、対象のトランザクションを含むビジネスロジックを全体的に見直して、別トランザクションとすることになりました。

SQL Server (Transact-SQL) の場合

さて、もしもこのシステムのデータベースが MySQL ではなく SQL Server だったらどうなっていたのでしょうか。

結論としてはこのケースのデッドロックは発生していないでしょう。
実際に SQL Server で再現しようとしても再現できません。

理由としては、まずデフォルトのトランザクション分離レベルが違う（MySQL は REPEATABLE READ で SQL Server は READ COMMITTED）ということがありますが、これは設定次第で変えられます。
しかし、SQL Server でトランザクション分離レベルを REPEATABLE READ にしても、このデッドロックは発生しません。

それは InnoDB が REPEATABLE READ でもファントムリードが発生しないように拡張されている（それがギャップロック）からです。

SQL Server でも分離レベルを SERIALIZABLE にすればこのケースでデッドロックが発生しますが、先行するトランザクションの INSERT 文ではなく後発のトランザクションの UPDATE でロック待ちになるので、挙動は異なります。

まとめ

デッドロック調査を通して僕が学んだ InnoDB のロックについて、実際に調査した流れを再現しながらご紹介しました。
MySQL (InnoDB) も SQL Server (Transact-SQL) も、どちらが良いとかいう話ではなく、特徴や違いを理解して使わないといけないということですね。