初めに

この記事はミライトデザイン Advent Calendar 2024 8日目の記事になります。

前日は mozumasu さんの記事でした。

勉強や仕事を効率化できそうな AI の紹介から、 AWS profile の管理方法など幅広く紹介してくれていて、どなたが読んでも新しい発見がありそうな記事でした！

まだ読んでない方は是非読んでみてください。

この記事について

MySQL で、知らないとイメージと違うロックの取り方をするため、ロック待ちやデットロックが発生しやすくなってしまうギャップロックとネクストキーロックについて調べた。

ロックとかトランザクション分離レベルとかがよくわからない場合はこちらもどうぞ。

ギャップロック

■ 分離レベルによって発生する

ギャップロックは、 MySQL(InnoDB) のデフォルトのトランザクション分離レベル REPEATABLE READ で発生する。

（分離レベルを READ COMMITTED に変更すると発生しない。）

■ 目的

ギャップロックの主な目的の一つは、REPEATABLE READ でファントムリードを防止するため。

■ ギャップロックとは

MySQL のリファレンスには次のように書いてある。

ギャップロックは、インデックスレコード間のギャップのロック、または最初のインデックスレコードの前または最後のインデックスレコードの後のギャップのロックです。

ギャップロックは次の３つの範囲でロックを取ると言っている。

インデックスレコード間のギャップのロック
最初のインデックスレコードの前のギャップのロック
最後のインデックスレコードの後のギャップのロック

ピンと来なかったので、確認してみる。

■ 動作を確認してみる

Docker で MySQL の環境構築

docker container run \
    --name db \
    --rm \
    --detach \
    --publish 33306:3306 \
    --env MYSQL_ROOT_PASSWORD=root \
    --env MYSQL_USER=user \
    --env MYSQL_PASSWORD=password \
    --env MYSQL_DATABASE=study_gap_lock \
    mysql:8

# コンテナにアクセス
docker exec -it db bash


# mysql へアクセス
mysql -u root -proot study_gap_lock

テーブルとデータ作成

mysql> CREATE TABLE products (
    id INT PRIMARY KEY,
    price INT NOT NULL
);

mysql> INSERT INTO products VALUES (10, 100);
mysql> INSERT INTO products VALUES (20, 200);
mysql> INSERT INTO products VALUES (30, 300);

mysql> SELECT * FROM products;
+----+-------+
| id | price |
+----+-------+
| 10 |   100 |
| 20 |   200 |
| 30 |   300 |
+----+-------+
3 rows in set (0.00 sec)

トランザクション分離レベルを確認しておく。

mysql> SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.01 sec)

デフォルトの REPEATABLE-READ になっている。
ギャップロックは発生するはず。

この記事での記載方法。
T1 と T2 は別タブで実行した別トランザクションの結果を記載する。

-- T1
-- ターミナルのタブ１で実行した SQL を記載

-- T2
-- ターミナル２のタブ２で実行した SQL を記載

次のロックがどういう意味なのか確認していきたい。

インデックスレコード間のギャップのロック
最初のインデックスレコードの前のギャップのロック
最後のインデックスレコードの後のギャップのロック

インデックスレコード間のギャップのロック

-- T1
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

-- インデックスがある id = 10 と id = 20 を指定
mysql> SELECT * FROM products WHERE id BETWEEN 10 AND 20 FOR UPDATE;
+----+-------+
| id | price |
+----+-------+
| 10 |   100 |
| 20 |   200 |
+----+-------+
2 rows in set (0.01 sec)

この時のロックの状態を確認しておく。

-- T1
mysql> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-------------+-----------+---------------+-------------+-----------+
| products    | TABLE     | IX            | GRANTED     | NULL      |
| products    | RECORD    | X,REC_NOT_GAP | GRANTED     | 10        |
| products    | RECORD    | X             | GRANTED     | 20        |
+-------------+-----------+---------------+-------------+-----------+
3 rows in set (0.00 sec)

まず、１行目。

| products    | TABLE     | IX            | GRANTED     | NULL      |

インテンションロック、このテーブルに排他ロックをとるってことを明示的に示しているらしい。インテンションロックについては別の機会に調べる。

2行目。

| products    | RECORD    | X,REC_NOT_GAP | GRANTED     | 10        |

id = 10 に対して排他ロック（X）をとっている。REC_NOT_GAP があるのでギャップロックはとっていない。

３行目。

| products    | RECORD    | X             | GRANTED     | 20        |

id = 20 に対して、排他ロックをとっている。REC_NOT_GAP がないのでギャップロックをとっている。

id = 20 以降にロックは取られていないので、ギャップロップが取られている範囲は次のようになっているはず。

10 < 間の値 <= 20

インデックスレコード間のギャップのロック をとっていることがわかる。

この時の挙動も確認しておく。

今のテーブルのデータをおさらいしておくとこんな感じ

+----+-------+
| id | price |
+----+-------+
| 10 |   100 |
| 20 |   200 |
| 30 |   300 |
+----+-------+

-- T2

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

-- id = 9 はギャップロップの範囲外のため OK
mysql> INSERT INTO products VALUES (9, 90);

-- id = 10 はギャップロップはかかっていないが、排他ロックがかかっているため NG
mysql> SELECT * FROM products WHERE id = 10 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- id = 11 というデータは存在していないが、ギャップロックがかかっているため NG
mysql> INSERT INTO products VALUES (11, 110);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- id = 19 もデータは存在していないが、ギャップロックのため NG
mysql> INSERT INTO products VALUES (19, 190);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- id = 20 はギャップロック + 排他ロックのため NG
mysql> SELECT * FROM products WHERE id = 20 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- id = 21 はギャップロップの範囲外のため OK
mysql> INSERT INTO products VALUES (21, 210);

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)


-- T1 
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

最初のインデックスレコードの前のギャップのロック

-- T1
mysql> BEGIN;

-- インデックスの最小 id = 10 未満を指定
mysql> SELECT * FROM products WHERE id BETWEEN 9 AND 20 FOR UPDATE;
+----+-------+
| id | price |
+----+-------+
| 10 |   100 |
| 20 |   200 |
+----+-------+
2 rows in set (0.01 sec)

-- ロックの確認
mysql> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+-----------+-----------+-------------+-----------+
| OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+-----------+-----------+-------------+-----------+
| products    | TABLE     | IX        | GRANTED     | NULL      |
| products    | RECORD    | X         | GRANTED     | 10        |
| products    | RECORD    | X         | GRANTED     | 20        |
+-------------+-----------+-----------+-------------+-----------+
3 rows in set (0.00 sec)

ロックの状態から次のことがわかる。

| products    | RECORD    | X         | GRANTED     | 10        |

REC_NOT_GAP がないため、 id = 10 はギャップロックと排他ロックをとっている。

| products    | RECORD    | X         | GRANTED     | 20        |

REC_NOT_GAP がないため、id = 20 もギャップロックと排他ロックをとっている。

id = 20 以降記載がないため、ロックを取得していない。

つまり、ロックは次のような状態になっているはず。

小さい値すべて <= 10 < 間の値 <= 20

最初のインデックスレコードの前のギャップのロック と インデックスレコード間のギャップのロック をとっているのがわかる。

ロックの挙動を確認する。

-- T2
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

-- id = 1 はギャップロップの範囲内のため NG（最初のインデックスレコードの前のギャップのロック）
mysql> INSERT INTO products VALUES (1, 10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- id = 9 はギャップロップの範囲内のため NG（最初のインデックスレコードの前のギャップのロック）
mysql> INSERT INTO products VALUES (9, 90);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- id = 10 はギャップロックと排他ロックをとっているため NG
mysql> SELECT * FROM products WHERE id = 10 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- id = 15 はギャップロックの範囲内のため NG (インデックスレコード間のギャップのロック)
mysql> INSERT INTO products VALUES (15, 150);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- id = 20 はギャップロックと排他ロックをとっているため NG
mysql> SELECT * FROM products WHERE id = 20 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- id = 21 はギャップロックの範囲外のため OK
mysql> INSERT INTO products VALUES (21, 210);
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

-- T1
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

最後のインデックスレコードの後のギャップのロック

-- T1
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

-- 最大のインデックス id = 30 より大きい値を指定
mysql> SELECT * FROM products WHERE id BETWEEN 20 AND 31 FOR UPDATE;
+----+-------+
| id | price |
+----+-------+
| 20 |   200 |
| 30 |   300 |
+----+-------+
2 rows in set (0.00 sec)

mysql> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+-----------+---------------+-------------+------------------------+
| OBJECT_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA              |
+-------------+-----------+---------------+-------------+------------------------+
| products    | TABLE     | IX            | GRANTED     | NULL                   |
| products    | RECORD    | X,REC_NOT_GAP | GRANTED     | 20                     |
| products    | RECORD    | X             | GRANTED     | supremum pseudo-record |
| products    | RECORD    | X             | GRANTED     | 30                     |
+-------------+-----------+---------------+-------------+------------------------+
4 rows in set (0.00 sec)

id = 20 は排他ロック、ギャップロックはとっていない
supremum pseudo-record はインデックスの最後の値を表す擬似レコードらしい
- つまり、id = 30 より大きいすべてのインデックス（30 < ∞）にギャップロックがかかっている
id = 30 はギャップロックと排他ロックをとっている

この場合ロックの範囲は次のようになる。

20 < 間の値 < 30 < ∞

ロックの挙動を確認する。

-- T2
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

-- id = 20 より小さい値はギャップロックの範囲外
mysql> SELECT * FROM products WHERE id = 10 FOR UPDATE;
+----+-------+
| id | price |
+----+-------+
| 10 |   100 |
+----+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO products VALUES (19, 190);
Query OK, 1 row affected (0.00 sec)

-- id = 20 は排他ロックをとっているため NG
mysql> SELECT * FROM products WHERE id = 20 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- id = 20 と id = 30 の間の値もギャップロックの範囲のため NG
mysql> INSERT INTO products VALUES (25, 250);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- id = 30 はギャップロックと排他ロックをとっているため NG
mysql> SELECT * FROM products WHERE id = 30 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- id = 30 より大きい値すべてにギャップロックがかかっているため NG
mysql> INSERT INTO products VALUES (31, 310);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> INSERT INTO products VALUES (100, 1000);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> INSERT INTO products VALUES (1000, 10000);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

-- T1
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

ギャップロックが次の範囲のロックを取ることはわかった。

インデックスレコード間のギャップのロック
最初のインデックスレコードの前のギャップのロック
最後のインデックスレコードの後のギャップのロック

ギャップロックがかかる場面をいくつか試してみる

存在しない ID を指定してもギャップロックがかかる

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM products WHERE id = 8 FOR UPDATE;
Empty set (0.00 sec)

mysql> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+-----------+-----------+-------------+-----------+
| OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+-----------+-----------+-------------+-----------+
| products    | TABLE     | IX        | GRANTED     | NULL      |
| products    | RECORD    | X,GAP     | GRANTED     | 10        |
+-------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)


mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM products WHERE id < 8 FOR UPDATE;
Empty set (0.00 sec)

mysql> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+-----------+-----------+-------------+-----------+
| OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+-----------+-----------+-------------+-----------+
| products    | TABLE     | IX        | GRANTED     | NULL      |
| products    | RECORD    | X,GAP     | GRANTED     | 10        |
+-------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

（ギャップロックをとっている場合に LOCK_MODE が X,GAP と明示的に表示される場合と X と省略されて表示されることがあるっぽい。）

範囲指定しても当然かかる

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM products WHERE id >= 10 FOR UPDATE;
+----+-------+
| id | price |
+----+-------+
| 10 |   100 |
| 20 |   200 |
| 30 |   300 |
+----+-------+
3 rows in set (0.00 sec)

mysql> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+-----------+---------------+-------------+------------------------+
| OBJECT_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA              |
+-------------+-----------+---------------+-------------+------------------------+
| products    | TABLE     | IX            | GRANTED     | NULL                   |
| products    | RECORD    | X,REC_NOT_GAP | GRANTED     | 10                     |
| products    | RECORD    | X             | GRANTED     | supremum pseudo-record |
| products    | RECORD    | X             | GRANTED     | 20                     |
| products    | RECORD    | X             | GRANTED     | 30                     |
+-------------+-----------+---------------+-------------+------------------------+
5 rows in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

ただの感想

MySQL のデフォルトのトランザクション分離レベルである REPEATABLE READ はインデックスを使っていてもロックを取得する範囲がかなり多い。

MySQL の分離レベルを REPEATABLE READ のままではなくて、 READ COMMITTED に変更した方がいい。みたいな話をたまに聞くけど意味が少しわかった気がする。

おまけ : ネクストキーロック

ギャップとよくセットで聞くネクストキーロック。

インデックスレコードのレコードロックと、インデックスレコードの前のギャップのギャップロックの組み合わせです

初めなんのことかわからなかったけど、どうやら書いてある通りの意味っぽい。

終わり

明日は tomo さんが MySQL の実行計画についての記事を書いてくれるそうです。
個人的にも気になる内容なので楽しみです。