[InnoDB]ネクストキーロックとは、あるいはSELECT FOR UPDATEの注意点

3036 ワード
InnoDB MySQL ネクストキーロック MySQL テキストリンク

スコープ

MySQL / InnoDB

ネクストキーロックとは

例えば、以下のコマンドを実行すると、100以上のidを挿入・更新できなくなりそうだが、そうとは限らない。

SELECT id FROM tbl_name WHERE id > 99 FOR UPDATE;

理解するヒントは、InnoDBは、式にロックを掛けるのではなく、「行」と「ギャップ」にロックを掛けるという点にある。

「行」とは実際にテーブルに保存されている行のことである。「ギャップ」とは、行と行の間を表す空間である。

実際のInoDBの挙動としては、「SELECTした行」と「その行の前後のギャップ」にロックを掛けるのである（行ロック・ギャップロック）。行ロックとギャップロックを合わせて「ネクストキーロック」という。以下具体例を使って説明する。

具体例

テーブルに以下のデータが入っているケースを考える。

+------------+
| id         |
+------------+
| 105        |
| 95         |
+------------+

このケースで冒頭のクエリを実行した場合、ロックが掛かる範囲は以下の通りである。

  • id=105 の行
  • id=105 の行の前後のギャップ、すなわち
    • id=95 ~ 105 のギャップ
    • id=105 ~ inf のギャップ、すなわち id=105 より大きい全ての空間

よって、 id=105 の行は更新できないし、 id=106 の挿入もできない。が、それに加えて、 id=96 のような値も挿入できなくなる。

冒頭の式の挙動の回答としては、具体的にどのような値の挿入がロックされるかは、テーブルのレコードによる、ということになる。式では WHERE id > 99 と指定しているが、それより小さい値を挿入できなくなることもあるということである。

これがネクストキーロックというものの特性である。

補足情報

  • id=95の行にはロックがかからないので、この行を更新することはできる
  • ネクストキーロックが掛かる条件については公式情報を参照してもらうのがよいが、ざっくり言うと、一意の行を指定してロックを掛ける場合を除き全てのクエリで掛かる
    • 例えば上記の例の場合、 WHERE id=105 で指定するとネクストキーロックは掛からず、id=105の行のみにロックが掛かる
    • 一方、 WHERE id=99 で指定すると、 id=95~105 の間にギャップロックがかかり、例えば id=100 などを挿入できなくなる
  • トランザクション分離レベルを READ COMMITTED に変更すると、ネクストキーロックは無効になる
    • ネクストキーロックは、トランザクション分離レベルに密接した概念であり、REPEATABLE READ(InnoDBのデフォルト)では防げないファントムリードを防ぐためにある。
  • 以上のルールは、SELECT FOR UPDATEだけでなく、トランザクション中で実行したUPDATE, DELETE文でも同様である

資料