ストアドプロシージャの中ではLOCK TABLESが利用できないのでネクストキーロックで代替する

4231 ワード
InnoDB MySQL MySQL テキストリンク

スコープ

  • MySQL / InnoDB

代替クエリ

テーブル全体の更新・挿入・削除をロックする場合は下記の通り。

SELECT id FROM tbl_name FOR UPDATE;

自動採番の挿入のみのテーブルであれば、以下でもカバー可能

SELECT id FROM tbl_name ORDER BY id DESC LIMIT 1 FOR UPDATE;

(※1)トランザクションの中で実行すること。よって、実際には以下のようになる。

START TRANSACTION;
SELECT id FROM tbl_name FOR UPDATE;
任意のクエリ
COMMIT; -- ここでロックが開放される

(※2)トランザクション分離レベルが REPEATABLE READ (InnoDBのデフォルト)であること。SERIALIZABLE の場合は、FOR UPDATEをしなくてもロックが掛かるし、 READ COMMITTED の場合は、上記のコマンドではロックを掛けることができない。

冒頭のコマンドでロックを掛けられる理由

冒頭のコマンドでは、SELECTで取得した行にしかロックが掛からず、新たな行の挿入は防げないように見えるが、そうではない。

挿入に対するロックを可能にしているのは「ネクストキーロック」というInnoDBの仕組みである。

以下では、公式リファレンスのロックに関する記述から、理由を紐解いていく。

ネクストキーロックとは？

ネクストキーロックそのものの説明は割愛する。ネクストキーロックがわからない方は、以下を参照してください。

ネクストキーロックの掛かる条件

ネクストキーロックは以下の2条件を両方満たす場合に掛かる。

(1) 特定のステートメントを実行した場合
(2) WHERE句による絞り込みが、一意なインデックスを指定していない場合

(1) 特定のステートメントを実行した場合

対象となるステートメントは、14.2.8 InnoDB のさまざまな SQL ステートメントで設定されたロックに全て記載されており、SELECT ... FOR UPDATE のほか、 UPDATE や DELETE でもネクストキーロックが掛かる。

(2) WHERE句による絞り込みが、一意なインデックスを指定していない場合

14.2.6 InnoDB のレコード、ギャップ、およびネクストキーロックでは、「一意のインデックスを使用して一意の行を検索することで行をロックするステートメントでは、ギャップロックは必要ありません。」と述べている。これは、裏返せば、一意でないクエリに対しては、ネクストキーロックが掛かることを意味する。冒頭のクエリは、一意な条件を指定していないので、ネクストキーロックが掛かる。

なお、英語版のリファレンスにはこの条件について、より明確に記載されている[14.7.3 Locks Set by Different SQL Statements in InnoDB]。

ネクストキーロックが存在する背景

ネクストキーロックの存在理由は、 REPEATABLE READ では防ぐことのできないファントムリードを防ぐこと、と考えるのが良さそうである。

14.2.7 ネクストキーロックによるファントム問題の回避では、『ファントムの発生を回避できるように、InnoDB では通常、インデックス行ロックとギャップロックを組み合わせたネクストキーロックと呼ばれるアルゴリズムが使用されます。』と述べられている。

ファントムリードとは、SELECTが 2回実行された場合に、1回目には返されなかった行が2回目には（他のスレッドからの挿入・削除によって）返される現象である。

冒頭のクエリはファントムリードが発生しうるクエリであり、背景からもネクストキーロックが掛かるのは自然かと思われる。