はじめに
みなさんこんにちはメリークリスマス🎄
ついにアドベントカレンダー最終日！！！
現在SODAでwebエンジニアをしているtoshikiです。(3記事目で謎の自己紹介)
CTOからflexispotのデスクを譲り受ける代わりにテックブログ3記事執筆するという約束を果たすべく、SODAのAdvent Calendar 2023で3枠担当することになり、この記事をもって無事プレゼントの配達が完了しました🎅(sorenani)
今日はクリスマス当日ということで、自分自身理解が曖昧だったMySQLのストレージエンジンであるInnoDBのロック周りの挙動をMySQLの公式ドキュメントを読みつつ調査してまとめてみました。
この記事でわかるInnoDBのロックの種類
- 共有ロックと排他ロック
- インテンションロック
- ギャップロック
- ネクストキーロック
検証環境
MySQLのVersion
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.34 |
+-----------+
1 row in set (0.00 sec)
検証に使うテーブルのSchema
CREATE TABLE `users` (
`id` INT AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`age` INT NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_age` (`age`)
);
初期データ
mysql> select * from users;
+----+-------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+-------+-----+---------------------+---------------------+
| 1 | Alice | 10 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
| 5 | Bob | 20 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
| 7 | Carol | 20 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
| 10 | Dave | 30 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
| 12 | Eve | 40 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
| 13 | Frank | 50 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
+----+-------+-----+---------------------+---------------------+
6 rows in set (0.00 sec)
ロック状況の確認方法
MySQLのversion8.0から追加されたperformance_schema.data_locksを使用して確認していきます。
このテーブルはロックの保持、リクエスト状況を記録しています。
ロック待ち状況については、performance_schema.data_lock_waitsで確認できます。
data_locksテーブルのschema
mysql> desc data_locks;
+-----------------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------+------+-----+---------+-------+
| ENGINE | varchar(32) | NO | PRI | NULL | |
| ENGINE_LOCK_ID | varchar(128) | NO | PRI | NULL | |
| ENGINE_TRANSACTION_ID | bigint unsigned | YES | MUL | NULL | |
| THREAD_ID | bigint unsigned | YES | MUL | NULL | |
| EVENT_ID | bigint unsigned | YES | | NULL | |
| OBJECT_SCHEMA | varchar(64) | YES | MUL | NULL | |
| OBJECT_NAME | varchar(64) | YES | | NULL | |
| PARTITION_NAME | varchar(64) | YES | | NULL | |
| SUBPARTITION_NAME | varchar(64) | YES | | NULL | |
| INDEX_NAME | varchar(64) | YES | | NULL | |
| OBJECT_INSTANCE_BEGIN | bigint unsigned | NO | | NULL | |
| LOCK_TYPE | varchar(32) | NO | | NULL | |
| LOCK_MODE | varchar(32) | NO | | NULL | |
| LOCK_STATUS | varchar(32) | NO | | NULL | |
| LOCK_DATA | varchar(8192) | YES | | NULL | |
+-----------------------+-----------------+------+-----+---------+-------+
15 rows in set (0.00 sec)
この記事では以下のカラムを使って検証していきます。
-
OBJECT_NAME
- ロックされたテーブル名
-
INDEX_NAME
- (存在する場合は)ロックされたインデックスの名前で、それ以外の場合はNULL
-
LOCK_TYPE
- ロックタイプ
- ストレージエンジン依存でInnoDBの場合に許可される値は以下
- 行レベルロックの場合は
RECORD - テーブルレベルロックの場合は
TABLE
- 行レベルロックの場合は
-
LOCK_MODE
- ロックのリクエスト方法
- ストレージエンジン依存でInnoDBの場合に許可される値以下で、
AUTO_INC,UNKNOWN以外のロックモードはギャップロック(存在する場合)を示す-
S[,GAP](共有ロック) -
X[,GAP](排他ロック) -
IS[,GAP](共有インテンションロック) -
IX[,GAP](排他インテンションロック) AUTO_INCUNKNOWN
-
-
LOCK_STATUS
- ロックリクエストのステータス
- ストレージエンジン依存でInnoDBの場合に許可される値は以下
-
GRANTED(ロックが保持されている) -
WAITING(ロックが待機されている)
-
-
LOCK_DATA
- ロックに関連付けられているデータ(存在する場合)
- ストレージエンジン依存でInnoDBの場合、
LOCK_TYPEがRECORDの場合は値が表示され、それ以外の場合はNULL値が表示される- 主キーインデックスロックの場合は、主キーの値が表示される
- センカンダリインデックスロックの場合は、インデックス値と主キー値が表示される
-- 具体例
+-------------+------------+-----------+-----------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+-----------+-------------+-----------+
| users | NULL | TABLE | IS | GRANTED | NULL |
| users | idx_age | RECORD | S,GAP | GRANTED | 20, 5 |
+-------------+------------+-----------+-----------+-------------+-----------+
data_locksについての公式ドキュメント
共有ロック(S)と排他ロック(X)
共有ロックと排他ロックは、InnoDBで行レベルロックに対して標準実装されている2つのロックモードです。
以降の説明でトランザクションは簡略化のためT1(トランザクション1),T2(トランザクション2)のように表します。
共有ロック(S)
共有ロックはロックされている行に対して、他のトランザクションが排他ロック(X)を取得するのをブロックします。(共有ロック(S)は即時付与される)
まずはT1で共有ロックを取得して、T2でT1が共有ロックを取得したレコードに対してupdateを実行するとLock wait timeout exceededエラーが発生することを確認できます。
-- T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users where id=1 for share;
+----+-------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+-------+-----+---------------------+---------------------+
| 1 | Alice | 10 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
+----+-------+-----+---------------------+---------------------+
1 row in set (0.00 sec)
-- T2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update users set age=11 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
T1でid=1のレコードに対して共有ロックを取得した後のdata_locksは以下のようになります。
1つ目のレコードは共有インテンションロック(IS)を取得してることを示しており、SELECT ... FOR SHAREを発行すると、InnoDBではまずテーブルに対してISロックを取得してから、レコードに対して共有ロック(S)を取得します。(インテンションロックについては後述します)
2つ目のレコードがid=1のレコードに対しての共有ロックを取得したことを示していて、REC_NOT_GAPというはギャップロックが発生してないことを示しています。
mysql> SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+---------------+-------------+-----------+
| users | NULL | TABLE | IS | GRANTED | NULL |
| users | PRIMARY | RECORD | S,REC_NOT_GAP | GRANTED | 1 |
+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)
T2でupdate実行した直後のdata_locksは以下のようになります。
上2つのレコードが追加されており、1つ目のレコードはテーブルに対して排他インテンションロック(IX)を取得したことを示しています。
InnoDBではトランザクションでテーブルに対してUPDATEやSELECT ... FOR UPDATEのような排他的な処理をする場合に、まず排他インテンションロック(IX)を取得します。(インテンションロックについては後述します)
2つ目のレコードがid=1のレコードに対しての排他ロックをリクエストしたことを示していて、LOCK_STATUSがWAITINGなので待ちが発生してることが分かります。
mysql> SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+---------------+-------------+-----------+
| users | NULL | TABLE | IX | GRANTED | NULL |
| users | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 1 |
| users | NULL | TABLE | IS | GRANTED | NULL |
| users | PRIMARY | RECORD | S,REC_NOT_GAP | GRANTED | 1 |
+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)
また以下のようにT1で共有ロックを取った場合でもT2から同じ行に対して共有ロックを取得することが可能です。
-- T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users where id=1 for share;
+----+-------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+-------+-----+---------------------+---------------------+
| 1 | Alice | 10 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
+----+-------+-----+---------------------+---------------------+
1 row in set (0.00 sec)
-- T2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users where id=1 for share;
+----+-------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+-------+-----+---------------------+---------------------+
| 1 | Alice | 10 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
+----+-------+-----+---------------------+---------------------+
1 row in set (0.00 sec
この時のperformance_schema.data_locksは以下のようなになっていて、LOCK_MODEカラムがS,REC_NOT_GAPとなっているレコードが2つ確認できました。
-- data_locks
mysql> SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| db | users | NULL | TABLE | IS | GRANTED | NULL |
| db | users | PRIMARY | RECORD | S,REC_NOT_GAP | GRANTED | 1 |
| db | users | NULL | TABLE | IS | GRANTED | NULL |
| db | users | PRIMARY | RECORD | S,REC_NOT_GAP | GRANTED | 1 |
+---------------+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)
排他ロック(X)
排他ロックはロックされている行に対して、他のトランザクションがSとXロックを取得することをブロックします。
まずはT1で排他ロックを取得して、T2で同じ行に対してSELECT ... FOR SHAREがブロックされることを確認します。
-- T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users where id=1 for update;
+----+-------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+-------+-----+---------------------+---------------------+
| 1 | Alice | 10 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
+----+-------+-----+---------------------+---------------------+
1 row in set (0.00 sec)
-- T2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users where id=1 for share;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
排他ロックを取得した時のdata_locksは以下のようになります。
LOCK_MODEがXのレコードが作成されています。
mysql> SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+---------------+-------------+-----------+
| users | NULL | TABLE | IX | GRANTED | NULL |
| users | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.01 sec)
共有ロックをリクエストした時のdata_locksは以下のようになります。
S,REC_NOT_GAPのレコードのLOCK_STATUSがWAITINGなので、ブロックされていることが確認できました。
mysql> SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+---------------+-------------+-----------+
| users | NULL | TABLE | IX | GRANTED | NULL |
| users | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
| users | NULL | TABLE | IS | GRANTED | NULL |
| users | PRIMARY | RECORD | S,REC_NOT_GAP | WAITING | 1 |
+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)
次はT1で排他ロックを取得して、T2で同じ行に対してSELECT ... FOR updateするとロック待ちエラーが発生することが確認できました。
-- T2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
T2で排他ロックリクエスト直後のdata_locksは以下のようになります。
上から2つ目のX,REC_NOT_GAPのLOCK_STATUSがWAITINGなので、ブロックされてることが確認できました。
mysql> SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+---------------+-------------+-----------+
| users | NULL | TABLE | IX | GRANTED | NULL |
| users | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 1 |
| users | NULL | TABLE | IX | GRANTED | NULL |
| users | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)
インテンションロック
インテンションロックはテーブルに適用されるロックの一種です。
InnoDBでは、レコードロックとテーブルロックの共存を許可する複数粒度ロックがサポートされていて、これを可能にするのがインテンションロックです。
インテンションロックを設定する目的は、トランザクションがテーブルにレコードロックを取得することを示すことです。
インテンションロックの種類
共有インテンションロック(IS)
トランザクションがテーブルの個々の行に共有ロック(S)を設定することを示す
排他インテンションロック(IX)
トランザクションがテーブル内の個々の行に排他ロック(X)を設定することを示す
テーブルレベルロックの競合マトリックス
| X | IX | S | IS | |
|---|---|---|---|---|
| X | ❌ | ❌ | ❌ | ❌ |
| IX | ❌ | ⭕️ | ❌ | ⭕️ |
| S | ❌ | ❌ | ⭕️ | ⭕️ |
| IS | ❌ | ⭕️ | ⭕️ | ⭕️ |
❌: 競合する
⭕️: 競合しない
マトリックスから分かるようにテーブル排他ロック(X)が付与されている場合は、他のすべてのロックモードは競合するのでロック待ちになります。
テーブル共有ロック(S)が付与されてる場合は、テーブル共有ロック(S)とトランザクションからの共有インテンションロック(IS)のみ許可されます。
トランザクションが排他インテンションロック(IX)を取得してる場合は、排他インテンションロック(IX)と共有インテンションロック(IS)のみ許可されます。
トランザクションが共有インテンションロック(IS)を取得してる場合は、テーブル排他ロック(X)以外許可されます。
ギャップロック
ギャップロックには以下の2種類があります。
- インデックスレコード間のギャップのロック
- 最初のインデックスレコードの前または最後のインデックスレコードの後のギャップのロック
自分自身もロックの挙動の中でも理解しずらい部分だったので、それぞれ別々にわけて挙動を確認していきます。
インデックスレコード間のギャップのロック
primaryやunique keyのように単一のカラムで一意になるケースのギャップロック
T1でidが5~7の範囲検索で排他ロック(X)を取得した後に、T2で5~7のギャップであるid=6のデータinsertするとエラーが発生することが確認できます。
-- T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users where id between 5 and 7 for update;
+----+-------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+-------+-----+---------------------+---------------------+
| 5 | Bob | 20 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
| 7 | Carol | 20 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
+----+-------+-----+---------------------+---------------------+
2 rows in set (0.00 sec)
-- T2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into users(id,name,age) values(6,"6",6);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
T1で範囲検索の排他ロック(X)を取得した時のdata_locksを確認すると以下のようになります。
LOCK_MODEがX,REC_NOT_GAPとXのレコードが確認できます。
mysql> SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+---------------+-------------+-----------+
| users | NULL | TABLE | IX | GRANTED | NULL |
| users | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 5 |
| users | PRIMARY | RECORD | X | GRANTED | 7 |
+-------------+------------+-----------+---------------+-------------+-----------+
3 rows in set (0.00 sec)
X,REC_NOT_GAPは排他レコードロック(X)を表し、XはX,REC_NOT_GAPとX,GAPの組み合わせを表しており、後述するネクストキーロックを示しています。
公式ドキュメントによるとネクストキーロックは以下のように説明されております。
インデックスレコードのレコードロックと、インデックスレコードの前のギャップのギャップロックの組み合わせです。
今回でいうとidが5~7の範囲で排他レコードロック(X)を取得したときに、id=5には排他レコードロックがかかり、id=7には排他レコードロック(X)とギャップであるid=6にGAPロックがかかっていることが分かります。
LOCK_MODEで表現される値が何を示しているかは以下のブログを読むと分かりやすいです。
indexのように単一のカラムで重複するデータがあるケースのギャップロック
T1でindexを貼っているageカラムに対して排他ロックをかけた場合にどうなるかを確認しましょう。
-- T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users where age=20 for update;
+----+-------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+-------+-----+---------------------+---------------------+
| 5 | Bob | 20 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
| 7 | Carol | 20 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
+----+-------+-----+---------------------+---------------------+
2 rows in set (0.00 sec)
-- data_locks
mysql> SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+---------------+-------------+-----------+
| users | NULL | TABLE | IX | GRANTED | NULL |
| users | idx_age | RECORD | X | GRANTED | 20, 5 |
| users | idx_age | RECORD | X | GRANTED | 20, 7 |
| users | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 5 |
| users | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 7 |
| users | idx_age | RECORD | X,GAP | GRANTED | 30, 10 |
+-------------+------------+-----------+---------------+-------------+-----------+
6 rows in set (0.00 sec)
data_locksを確認すると何やらたくさんのレコードが出てきました。。
primaryインデックスに対すロックとしては、id=5,7にそれぞれ排他レコードロック(X)が付与されています。
こちらは分かりやすいですね。
id=5,7にだけ排他レコードロック付与されてる状態なので、id=6とかでinsertできるはずです。
-- T2でid=6のデータをinsertできることを確認
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into users(id,name,age) values(6,"6",6);
Query OK, 1 row affected (0.00 sec)
idx_ageインデックスに対するロックとしては、age=20(id=5,7)にネクストキーロックが、age=30(id=10)にギャップロックが付与されています。
頭が混乱してきたので、一つずつ分解して考えます。
age=20(id=5,7)にネクストキーロックということは、age=20(id=5,7)のレコードにレコードロックとその前のギャップにギャップロックが付与されている状態です。
検証データを見るとageが20の前は10(id=1)なので、ageが10~19のレコードはinsertできないはずです。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into users(id,name,age) values(2,"2",10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(2,"2",11);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(2,"2",12);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(2,"2",13);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(2,"2",14);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(2,"2",15);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(2,"2",16);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(2,"2",17);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(2,"2",18);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(2,"2",19);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ageが10~19の範囲はいずれもinsertでエラーになることを確認できました。
次にage=30(id=10)にギャップロックを確認していきます。
ageが30の前は20なので、ギャップブロックされる範囲は以下になりそうです
- ageが20~29のレコード
- ageが30かつidが9までのレコード
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into users(id,name,age) values(2,"2",21);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(2,"2",25);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(2,"2",30);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(11,"11",30);
Query OK, 1 row affected (0.00 sec)
ギャップロック対象範囲はinsertでエラーが出るのに対して、範囲外のid=11,age=30のレコードはinsertできました。
age=20への排他ロックでなぜage=30のギャップロックがかかるかというと、age=30のギャップロックがなかった場合を考えると分かりやすいかと思います。
age=30のギャップロックがなかった場合、以下のisertが成功してしまい、排他ロックを取得したage=20へ新規レコードが作成されてしまいそうです。
これを防ぐために、age=20の1つ後ろのインデックスレコードであるage=30のレコードをギャップロックしていそうでした。
insert into users(id,name,age) values(8,"8",20);
-- usersテーブルの状況
mysql> select * from users;
+----+-------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+-------+-----+---------------------+---------------------+
| 1 | Alice | 10 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
| 5 | Bob | 20 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
| 7 | Carol | 20 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
| 10 | Dave | 30 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
| 12 | Eve | 40 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
| 13 | Frank | 50 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
+----+-------+-----+---------------------+---------------------+
6 rows in set (0.00 sec)
-- select * from users where age=20 for update;した時のdata_locks
mysql> SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+---------------+-------------+-----------+
| users | NULL | TABLE | IX | GRANTED | NULL |
| users | idx_age | RECORD | X | GRANTED | 20, 5 |
| users | idx_age | RECORD | X | GRANTED | 20, 7 |
| users | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 5 |
| users | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 7 |
| users | idx_age | RECORD | X,GAP | GRANTED | 30, 10 |
+-------------+------------+-----------+---------------+-------------+-----------+
6 rows in set (0.00 sec)
最初のインデックスレコードの前または最後のインデックスレコードの後のギャップのロック
最初のインデックスレコードの前のギャップロック
T1でidが4~7の範囲で排他ロックを取得する場合に、usersテーブルにはid=4のレコードは存在しておらず、idが1~4の範囲でギャップロックがかかることです。
-- T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users where id between 4 and 7 for update;
+----+-------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+-------+-----+---------------------+---------------------+
| 5 | Bob | 20 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
| 7 | Carol | 20 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
+----+-------+-----+---------------------+---------------------+
2 rows in set (0.00 sec)
-- T2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into users(id,name,age) values(2,"2",2);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(3,"3",3);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(4,"4",4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
T1でidが4~7の範囲で排他ロックを取得した時のdata_locksは以下のようになっています。
id=5,7のレコードにネクストキーロックがかかっていることが確認できました。
mysql> SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+------------+-----------+-----------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+-----------+-------------+-----------+
| users | NULL | TABLE | IX | GRANTED | NULL |
| users | PRIMARY | RECORD | X | GRANTED | 5 |
| users | PRIMARY | RECORD | X | GRANTED | 7 |
+-------------+------------+-----------+-----------+-------------+-----------+
3 rows in set (0.00 sec)
最後のインデックスレコードの後のギャップのロック
T1でidが12~14の範囲で排他ロックを取得する場合に、usersテーブルにはid=14のレコードは存在しておらず、id=14以降のレコードがすべてギャップロックされることです。(怖い)
-- T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users where id between 12 and 14 for update;
+----+-------+-----+---------------------+---------------------+
| id | name | age | created_at | updated_at |
+----+-------+-----+---------------------+---------------------+
| 12 | Eve | 40 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
| 13 | Frank | 50 | 2023-12-23 10:34:27 | 2023-12-23 10:34:27 |
+----+-------+-----+---------------------+---------------------+
2 rows in set (0.00 sec)
-- T2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into users(id,name,age) values(14,"14",14);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(15,"15",15);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(50,"50",50);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into users(id,name,age) values(500,"500",500);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
T1でidが12~14の範囲で排他ロックを取得した時のdata_locksは以下のようになっています。
id=12のレコードには排他レコードロックが、id=13のレコードにはネクストキーロックがかかっていることが確認できました。
mysql> SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+------------+-----------+---------------+-------------+------------------------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+---------------+-------------+------------------------+
| users | NULL | TABLE | IX | GRANTED | NULL |
| users | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 12 |
| users | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record |
| users | PRIMARY | RECORD | X | GRANTED | 13 |
+-------------+------------+-----------+---------------+-------------+------------------------+
4 rows in set (0.00 sec)
X,INSERT_INTENTION → conceptually same as above, but only happens for the “supremum pseudo-record” which is imaginary record “larger than any other record on the page” so that the gap “before” “it” is actually “gap after the last record”.
↑のように記載があるので、LOCK_DATAがsupremum pseudo-recordのレコードが恐らく最後のインデックスレコードの後のギャップロックを示してると思われます。
ギャップロックまとめ
ギャップロックについて書いていてやはり難しいなと思いました。
公式ドキュメントには以下のようにギャップロックをする目的が書いてあり、それは他のトランザクションからのギャップへのinsertをブロックしたいということでした。これを念頭に考えると少しわかりやすくなる気がしました。
InnoDB のギャップロックは「「純粋に阻害」」です。つまり、その唯一の目的は、他のトランザクションがギャップに挿入されないようにすることです。
ネクストキーロック
ネクストキーロックは公式ドキュメントの説明にあるようにレコードロックとその前のギャップのギャップロックの組み合わせのことさしているだけで、ギャップロックの箇所で具体例を書いているのでそちらを参照してください。
インデックスレコードのレコードロックと、インデックスレコードの前のギャップのギャップロックの組み合わせです。
まとめ
公式ドキュメントでは上記で説明できてない以下3つロックに関する情報も解説されてるので、気になる方は是非確認してみてください。
- インテンションロックの挿入
- AUTO-INC ロック
- 空間インデックスの述語ロック
やはり一番理解しづらいのはギャップロックだと思いますが、サービス運用中に意図せず障害を引き起こす可能性が高いのもやはりギャップロックなのかなと思いました。
トランザクションで排他ロックを取得するときに存在しないidを指定してしまい、広範囲でギャップロックが発生して書き込み待ちが発生するみたいなのはあるあるなのかもしれないと思いました。
何はともあれアドベントカレンダーお疲れ様でした！！！
株式会社SODAの開発組織がお届けするZenn Publicationです。 是非Entrance Bookもご覧ください！ → recruit.soda-inc.jp/engineer
Discussion