はじめに
こんにちは、ポーラ・オルビスホールディングスで内製開発エンジニアをやっている高田です。
以前書いたいろんな分離レベルの記事で、以下のように触れました。
MySQLではスナップショット読み取りで独立性を確保し、ロック読み取りをする場合はさらにギャップロックやネクストキーロックといったロックの仕様を用いてファントムリードを防止している
今回の記事では、このネクストキーロックの挙動について実際にMySQLを触りながら検証していこうと思います。
MySQLのネクストキーロックの挙動
SessionA上で範囲選択のロック読み取りを実行していきます。
SessionB上ではSessionA上で選択した範囲に該当するレコードのINSERTと該当しないレコードのINSERTを行って挙動を確認します。
選択した範囲に該当するレコードのINSERT
以下の通り実施しました。
検証用テーブルの状態
mysql> select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, INDEX_NAME from INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA='testdb';
+--------------+------------+-------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | INDEX_NAME |
+--------------+------------+-------------+-----------------+
| testdb | items | id | PRIMARY |
| testdb | items | value | idx_items_value |
+--------------+------------+-------------+-----------------+
mysql> SELECT * FROM items;
+----+-------+
| id | value |
+----+-------+
| 1 | 90 |
| 2 | 200 |
| 3 | 300 |
+----+-------+
SessionA
mysql> START TRANSACTION;
mysql> SELECT * FROM items WHERE value < 100 FOR UPDATE;
+----+-------+
| id | value |
+----+-------+
| 1 | 90 |
+----+-------+
SessionAではFOR UPDATEでロック読み取りを行います。
SessionB
mysql> INSERT INTO items (id, value) VALUES (4, 99);
SessionBで実行したINSERTは、SessionAでCOMMITするまで完了しませんでした。
(COMMITしないままでいるとタイムアウトエラーとなりました)
選択した範囲に該当しないレコードのINSERT
「選択した範囲に該当するレコードのINSERT」の実施後からスタートし、以下の通り実施しました。
検証用テーブルの状態
mysql> select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, INDEX_NAME from INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA='testdb';
+--------------+------------+-------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | INDEX_NAME |
+--------------+------------+-------------+-----------------+
| testdb | items | id | PRIMARY |
| testdb | items | value | idx_items_value |
+--------------+------------+-------------+-----------------+
mysql> SELECT * FROM items ORDER BY id;
+----+-------+
| id | value |
+----+-------+
| 1 | 90 |
| 2 | 200 |
| 3 | 300 |
| 4 | 99 |
+----+-------+
SessionA
mysql> START TRANSACTION;
mysql> SELECT * FROM items WHERE value < 100 FOR UPDATE;
+----+-------+
| id | value |
+----+-------+
| 1 | 90 |
| 4 | 99 |
+----+-------+
SessionB
mysql> INSERT INTO items (id, value) VALUES (5, 999);
SessionAでvalueが100未満のレコードをFOR UPDATEでSELECTしている状態で、SessionBにてその条件を満たさないレコードをINSERTする場合はSessionAのCOMMITを待たずにINSERTが完了します。
わかったこと
MySQLのネクストキーロックの挙動として、以下が確認できました：
- 単なるテーブルロックではなく、選択の範囲内・範囲外でロック状態が異なること
- インデックスを設定したカラムの範囲選択において、既存のレコードだけでなく、その範囲内のギャップ（まだ存在しないレコードの挿入位置）に対してもロックがかかること
- 範囲外のレコード（value=999）の挿入はブロックされないこと
この仕組みにより、ロック読み取り時にファントムリードを防止できることが確認できました。
また個人の所感にはなりますが、スナップショットと違ってロックを設定する挙動なので、セッションの特性に応じて適切なロック戦略を取りたいと思うほか、ネクストキーロックの仕様を理解し、適切なインデックス設計を行うことで、高い独立性と並行性を両立できると感じました。