mysqlのネクストキーロックと挿入インテンションギャップロックのデッドロックを確認する

概要

先日mysqlを利用したアプリケーションにおいてデッドロックが発生しました。
あちゃぱーと思いつつもせっかくなので自分の中で消化しきれいなかった部分をこれを機に再確認してみることに。

この記事ではmysqlのデフォルトの分離レベル(Repeatable Read)においての レコードロック / ネクストキーロック / ギャップロック / 挿入インテンションギャップロック というハイカラな単語と結びつけながら自分なりに解釈したものを解説します。
と、赤字で書きましたが、始めに詫びを入れておきます。
ロックの解釈はドキュメントを読むだけで詳細に把握するのは非常に難しく、もしかしたら間違っていることを言っているかもしれません。そしたら本当に申し訳ありません。

解決したい問題

はじめに問題を共有したほうがやる気もでるしわかりやすい。
というわけで具体的に問題をものすごく単純にしたテーブルを用意しました。
こちらをつかって問題を再確認したいと思います。

テーブル

はい。めちゃくちゃ単純化しました。主キーとさらにインデックスを付けた属性だけが存在するテーブルです。わかりやすい。

CREATE TABLE test (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  secondaryId bigint(20) unsigned NOT NULL,
  PRIMARY KEY (id),
  KEY idxSecondaryId (secondaryId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

データ

データを用意します。一応ギャップロックとかいう話も出てくるのでギャップが存在するように飛び飛びのデータを入れてみます。これまたわかりやすい。

INSERT INTO test (id, secondaryId) VALUES (10, 10), (20, 20), (30, 30), (40, 40);

デッドロック

さてここで用意ができましたので、問題を再現してみたいと思います。
ここでトランザクション1(Trx1)とトランザクション2(Trx2)という二人のお友達に協力してもらおうと思います。
上から順に時系列で記載します。左側にどちらのトランザクションが実施したかという情報を記載します。

Trx1: begin;
Trx2: begin;
Trx1: DELETE FROM test WHERE secondaryId = 80;
Trx2: DELETE FROM test WHERE secondaryId = 90;
Trx1: INSERT INTO test VALUES (80, 80); // ここでTrx1がロックが取得できずに待ち状態になります
Trx2: INSERT INTO test VALUES (90, 90); // ここでTrx2もロックを取得できずに待ち状態となり、Trx1とのデッドロック状態となります

ということで

これがどういう仕組でデッドロックするか理解している方、これ以上この記事を見る必要はないです。

さておき、問題が確認できました。
実際にはアプリケーションで特定のキーによって管理されたデータを一回削除して再度データを入れ直す、というような処理を実装していました。
データ構造は全く違いますが、問題の本質はこれと一緒になります。
さて、この状況で中で一体何が起こっているのか見ていきましょう。

基本

まずここに上げる公式マニュアルを合わせてくまなく目を通しましょう。
合わせてよくまとまっているサイトなどもあると思うので、嘘か本当か見極めつつ検索して参考にしましょう。
なお本記事では各種ロックがどういうものかという基本的なことは一切説明しません。

さらに言葉の定義をちゃんとイメージできた方がいいと思うのでいくつかここで補足します。

トランザクションとロック

「ロックした」とかいう表現が意外と通じるのであるのであたかも「ロック」というのが動作とか状態のように解釈されがちだと感じますが、自分なりに解釈した結果ですと「ロック」とはアクセスする権利とイメージするとしっくり来ると思います。

例えばあるトランザクションのsql実行が別のトランザクションによって待ち状態になったとしましょう。
この時まさに「ロックした」といってもいいんですが、もっと正確に言うと「ロックを取得しようとしたが取得できずに待ち状態になった」というのが正しいです。

つまりロックというのは単なるアクセス権なのです。
ものすごく単純にいうと、この辺の話はトランザクションという登場人物がいて、レコードを操作する際にロック(アクセス権)を確保したり開放したりして自分の作業の影響範囲を定義しているのです。
でロックが先に取得されていたら、ロックが解放されるまで終わるまで待つ。ロックが開放されたら自分がロックを取得してレコードを操作する。シンプルにそれだけの話です。

ロックとは

でロックにも3つほど種類が存在します。冒頭にも挙げた レコードロック / ギャップロック / ネクストキーロック です。
マニュアルを読むとなるほどこれらの性質はなんとなくわかります。ですが どういうときにどのロックが使用されるのか というところがいまいちマニュアルだけだと分からない。
というわけでここが本記事の肝になります。

調査

というわけでじゃあ具体的にどういうことが起こっているの?という調査をします。

調査環境は osx で MariaDB を使用します。バージョンは下記。

MariaDB [test]> select version();
+-----------------+
| version()       |
+-----------------+
| 10.1.21-MariaDB |
+-----------------+

そして状態を確認するために下記のコマンドを実施しておきます。
これによってトランザクションの状態を出力する際にロックの詳細も一緒に出力してくれるようになります。

set global innodb_status_output_locks = ON;

どういうロックがかかっているのか

では先の例に沿って実行していきます。下記のdelete文を発行した時点で各トランザクションがどういうロックの取得を行っているのか確認してみます。

Trx1: begin;
Trx1: DELETE FROM test WHERE secondaryId = 80;

この時点で下記のコマンドによってトランザクションの状態を確認します。
モニターテーブルを作成してそちらを参照しても良いです。

show engine innodb status\G

するといろいろ出力されるのですが、トランザクションのロック取得状況を表す箇所を抜粋します。

---TRANSACTION 82897, ACTIVE 101 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 899, OS thread handle 0x700010b45000, query id 4569881 localhost root cleaning up
Trx #rec lock waits 7 #table lock waits 0
Trx total rec lock wait time 28 SEC
Trx total table lock wait time 0 SEC
TABLE LOCK table test.test trx id 82897 lock mode IX lock hold time 101 wait time before grant 0
RECORD LOCKS space id 5931 page no 4 n bits 72 index idxSecondaryId of table test.test trx table locks 1 total table locks 1  trx id 82897 lock_mode X lock hold time 101 wait time before grant 0

確認すると

  1. テーブルに対するインテンション排他ロック
  2. idxSecondaryIdインデックスに対する排他ネクストキーロック

が取得されているようです。テーブルに対するインテンションなロックはこの場合にはデッドロックを引き起こさないので無視します。
では続いてトランザクション2にも同じ手順に沿って実施してもらいましょう。

Trx2: begin;
Trx2: DELETE FROM test WHERE secondaryId = 90;

どうように show engin innodb status を実施すると

---TRANSACTION 82898, ACTIVE 1 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 945, OS thread handle 0x700010e57000, query id 4569884 localhost root cleaning up
Trx #rec lock waits 2 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC
TABLE LOCK table test.test trx id 82898 lock mode IX lock hold time 1 wait time before grant 0
RECORD LOCKS space id 5931 page no 4 n bits 72 index idxSecondaryId of table test.test trx table locks 1 total table locks 2  trx id 82898 lock_mode X lock hold time 1 wait time before grant 0

トランザクション1と同様のロックを取得していることが確認できます。

ここで一つ注目したいのはこれら2つのステートメントの実施はロック待ち状態が発生することなく実施することができました。
つまり delete文によって取得した2つの異なるインデックス値に対するネクストキーロックの範囲は重なっていない ということがわかりました。

ネクストキーロックとは何なのだろう

これに気づいた時結構驚きました。
だってネクストキーロックって、指定の値に対してレコードロックとさらにその前のインデックスとのギャップを含むと説明があるではありませんか。
テストデータの末尾の idxSeconaryId は 40 になっています。
すなわち Trx1 の delete文は (40, 80] の範囲のネクストキーロックを取得し、 Trx2 の delete文は (40, 90] の範囲のネクストキーロックを取得するのではないかと考えました。

わかりやすいように図を用意しましょう。力作です。
横軸が idxSecondaryId のインデックスの並びで、ボックスが取得しようとしているロックの範囲を表しています。

fig1

しかしながら双方のロックが取得しているということは、この仮定が誤っているという結論になります。
この解釈は後で述べることにして、とりあえず先に進みます。

デッドロックの確認

ではロック取得待ちを引き起こすトランザクション1のステートメントを実施してみましょう

Trx1: INSERT INTO test VALUES (80, 80); // ここでTrx1がロックが取得できずに待ち状態になります

ここでロック取得待ちが発生します。トランザクション1のロック取得状態を確認してみましょう。

---TRANSACTION 82897, ACTIVE 2059 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 899, OS thread handle 0x700010b45000, query id 4569893 localhost root update
INSERT INTO test VALUES (80, 80)
Trx #rec lock waits 8 #table lock waits 0
Trx total rec lock wait time 28 SEC
Trx total table lock wait time 0 SEC
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5931 page no 4 n bits 72 index idxSecondaryId of table test.test trx table locks 1 total table locks 2  trx id 82897 lock_mode X insert intention waiting lock hold time 2 wait time before grant 0
------------------
TABLE LOCK table test.test trx id 82897 lock mode IX lock hold time 2059 wait time before grant 0
RECORD LOCKS space id 5931 page no 4 n bits 72 index idxSecondaryId of table test.test trx table locks 1 total table locks 2  trx id 82897 lock_mode X lock hold time 2059 wait time before grant 0
RECORD LOCKS space id 5931 page no 4 n bits 72 index idxSecondaryId of table test.test trx table locks 1 total table locks 2  trx id 82897 lock_mode X insert intention waiting lock hold time 2 wait time before grant 0

一つロックが増えていますね。これは挿入インテンションギャップロックというやつです。
そしてこれがトランザクション2の取得している idxSecondaryId = 90 に対するネクストキーロックによりブロックされているという状況になります。

そして最後にトランザクション2の方でも insert を実行してみましょう。

Trx2: INSERT INTO test VALUES (90, 90); // ここでTrx2もロックを取得できずに待ち状態となり、Trx1とのデッドロック状態となります
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

デッドロックが起こったことが確認できました。

考察

状況を整理して客観的に見たときに、下記のような条件が成り立っています。

  1. 2つのトランザクションがはじめにdelete文により取得したネクストキーロックはお互いのロック領域に重なっていない
  2. その後発行するinsert文が取得しようとする挿入インテンションギャップロックはお互いのネクストキーロックに重なっている

逆にこれが成り立つような状況を想像できればネクストキーロック、挿入インテンションギャップロックが取得しようとするロックの範囲を推測できます。

で自分なりに解釈した結論が下記のようになります

ネクストキーロックについて

ネクストキーロックはインデックス値の前のギャップも取得することもあるが、ギャップを取得しないこともある。つまりこの場合はレコードロックの範囲と等しい
これは具体的には Trx1 の発行する delete文は idxSecondaryId = 80 のレコードロックの範囲に等しく、 Trx2 の発行する delete文は idxSecondaryId = 90 のレコードロックの範囲に等しい

挿入インテンションギャップロックについて

挿入インテンションギャップロックが取得しようとするギャップは、挿入するレコードが該当するギャップに等しい範囲が対象となる。つまりこの場合は末尾のレコードである idxSecondaryId = 40 以降に存在する無限のギャップが対象となる
これは具体的には Trx1, Trx2 の発行する insert文の idxSecondaryId がそれぞれ 80, 90であり、Trx1, Trx2ともに idxSecondaryId = 40 以降のギャップロックを取得しようとする

わかりにくいと思うので図にします。これまた会心の出来です。
矢印はレコードロックを表しています。

fig2

こんな形になるんではなかろうかと。
記事に記載されていること以外にも色々試行錯誤したのですが、すべての結果を説明できるので自分としてはこんな形であろうと解釈しました。
(本当は実装レベルで把握できれば良いんだろうけれども。)

対応

今回の対応としては削除時に主キーのレコードロックのみを取得して削除するような実装に変更しました。

参考

InnoDB のレコード、ギャップ、およびネクストキーロック
InnoDB のロックモード
InnoDB のさまざまな SQL ステートメントで設定されたロック


「mysqlのネクストキーロックと挿入インテンションギャップロックのデッドロックを確認する」への1件のフィードバック

  1. 同じような問題にぶち当たった過去があります。DELETE&INSERT時のロックの種類も同様に調査し、そして、、結局のところ謎が更に深まり、妥協という感じで決着しました。おそらく試されたと思いますが、TX分離レベルを、READ COMMITTEDにすることで、ギャップロックを無効化(=つまり、排他ネクストキーロックも)できるような記述があり、小躍りして試してみましたが、セカンダリインデックスでDELETE実行後にINSERTを複数プロセスで実行したところ、ロックの嵐でした。。。
    そして、頭道さんの対応方法が泣けます。。私も同じような対応方法を検討しましたが、私の場合は、セカンダリインデックスで数百万件のレコード削除だったので、ID指定で1件ずつでも、あるいは自前で生成した静的IN句で数千件ずつでも埒が明かなかったので、DELETE&INSERTではなくて、ひたすらINSERTを並列動作で許容し、切りのいいタイミングで、削除バッチにて古い断面分を削除するような作りに変えて対応しました。

    ロックは鬼門です、本当に。

    ちなみに、本件の場合、先のSQLでギャップロックがかからなかったのは、おそらくですが、実質ユニークキーだったことも関係しているのではないかと。ユニークキー(例えばPK)に対する、【ユニークアクセス】なレコードロックは=インデックスレコードロックであり、ギャップを握らないのはご存知かと思いますが、存在しないレコードに対するインデックス走査時でもユニーク性がある場合は、ネクストキーロックが行われない場合がある?、、のかなと。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">