MySQL(InnoDB)のネクストキーロックの仕組みと範囲を図解する

MySQL(InnoDB) のロックにはレコードロックとかギャップロックとかネクスキーロックとかありますが、結構ややこしくて、クエリで条件文が与えられた時にそれがどのようなロックになるのかをイメージし辛い問題が自分の中でありました。ので、実験してみた(MySQL8.0.12、REPEATABLE READ)結果を図で書き残します。なお、結果は SELECT FOR UPDATE を使って排他ロックをとる方法で試したものですが、ロックの範囲を知る上では、排他ロックか共有ロックかとかは関係ないかと思います。

前提として、以下のような id カラムのみを持つインデックスレコードへのロックを考えます。レコードには10 ~ 40 までの 5 飛びの値が存在します。それぞれのインデックスレコードの間にはギャップ(gap)が存在します。また、最初のレコードの前と最後のレコードの後には、論理的な最小値への gap と論理的なの最大値への gap が存在します(どう表現すればいいかよくわからない)。

f:id:norikone:20180911122218p:plain

で、ここに記載のように、

  • レコードロック
    • インデックスレコード単体のロック
  • ギャップロック
    • gap のロック
  • ネクスキーロック
    • レコードロック及びその直前の gap のロック

とします。

MySQL(InnoDB)のロックの範囲

存在する一意なid

f:id:norikone:20180911122229p:plain

条件文で、存在する一意な id が指定された場合には、その id を持つインデックスレコードに対してレコードロックが発生します。なお、黒の矢印は条件文が指定するポイントを示しています。

存在しない一意なid

f:id:norikone:20180911122246p:plain

単一の id 指定時にそのレコードが存在しない場合には、その id が入るべき gap へのギャップロックが発生します。

存在しない範囲境界

f:id:norikone:20180911122256p:plain

このケースでは、指定された値が入るべき gap へのギャップロックと、その次のインデックスレコードへのレコードロックが発生します。インデックスレコードへのレコードロックと、その直前の gap へのギャップロックという表現もできます。上記の定義通りですね。これがネクスキーロックの動作です。

また、ここからはイメージ的にわかりやすいよう、ロック時のインデックスの走査を図示しています(なんとなくのイメージを掴むためなので厳密性は考慮していません。例えば、gap は実際にはレコードのように実体として表現されていないはずなので、走査が gap から開始されるというのは少し変です。また、ネクスキーロック時にレコードへのロックが先か、gap へのロックが先かは定かではないです)。InnoDB のロックではそのようにインデックスを辿りながら、出会ったレコードをロックしていきます。それから、図では走査の矢印を最後だけ逆向きにしてネクスキーロックを表現していますが、多分すべての走査地点で、次のキーに進むたびに手前の gap をロック(ネクスキーロック)しているのだと思います。ですから、図では便宜上赤い部分をネクスキーロックとしていますが、実際にはすべてのロック点でこの動作が発生していそうということです。つまり、ネクスキーロックをしながら1つずつキーをスキャンしていくということです。

で、上のケースでは、25 をロックしなくても本来は OK なのですが、InnoDB ではそこまでロックしてしまいます。gap ロックをかける際には必ず次のレコードへのロックも必要なのでしょうか? しかし、「存在しない一意なid」のケースを見てみると、gap ロックを単体でかけること可能だということを示しています。このあたりはよく理解していませんが恐らく、一度インデックス走査を開始すると、次のキーを辿り、条件判定して且つロックしてからではないとその直前にある gap にロックが掛けられなくなっているのかもしれません。

まあ仮にそうだったとしたら、次のように逆向きに走査すれば 25 はロックされませんね。

f:id:norikone:20180911122310p:plain

ですから、ロック時の走査は逆向きには辿れないのかもしれません。

存在しない範囲境界(不等号逆版)

f:id:norikone:20180911122320p:plain

一つ前ののケースの不等号逆版です。このケースでは、20 が余分にロックされていないため、必要最小限のロックだと言えそうです(21 や 22 が余分にロックされてしまうと言えますが、MySQL では gap を分断するロックは難しい)。

ここまでのケースの動きを考えると、このケースでインデックスの走査を逆向きに辿ると 20 までロックされることになるはずです。先程の例とこの動作を考えると、やはりロック時のインデックスの走査は左から右に辿るようになっている、と推測できます。つまり何が言いたいかというと、不等号の向きによって(一つ前のケースでの 25 のような)余分なロックが発生したりしなかったりするのは、恐らくインデックスの走査の向きによるものだということです。

存在する範囲境界

f:id:norikone:20180911122330p:plain

これが割と厄介だと思っています。id <= 25 という条件を普通に考えれば、25 までロックされて、その後の gap や 30 はロックされないだろうと思いがちだからです。この条件なら、本来 26 への挿入や 30 の更新は許可されるべきです。このケースの動作はかなり謎めいていますが、結果から察するに、インデックス走査の終了条件が「条件文を満たさない場合」になっているのかもしれません。そうであれば、25 まで辿ってもまだ id <= 25 という条件は満たすので、次の 30 に進み、ここで条件が満たされなくなり、ネクスキーロックによってその前の gap もロックされる、と説明できるからです。

ちなみにここでも、もし逆走できれば 30 やその前の gap はロックされないはずです。なので個人的には、ロック時のインデックスの走査は「左から右に、条件を満たさないインデックスレコードまで走る」という風に理解しておけばいいかなあと思ったりしています。

存在する範囲境界(逆版)

f:id:norikone:20180911122340p:plain

このケースでは、当該値からロックを開始します。「存在しない範囲境界(不等号逆版)」と異なり、このケースでは手前の gap をロックしません。なんでもかんでもネクスキーロックするわけではないということですね。レコードロックでスタートして、それ以降はネクスキーロックという感じでしょうか。

存在しない2点の範囲境界

f:id:norikone:20180911122354p:plain

これまでに書いたケースで説明できる動作になっています。

存在する2点の範囲境界

f:id:norikone:20180911122404p:plain

こちらもこれまでに書いたケースの組み合わせです。

範囲が分割されている場合

f:id:norikone:20180911122413p:plain

範囲が分割されている場合でも同様に動作します。

おわり

一通り書いてみましたが、間違いがあるかもしれませんのでご注意。ドキュメントやネット上の情報、手元での実験だけでは不明確な部分もあり、ソース嫁という話ではあるのですが、若干のめんどくさみがあるのでもし気が向くことがあれば見てみようと思います。おわり。