データベースを使っていてロックの存在を知らない方はいないと思いますが、実際にプログラムからデータベースを操作する時に明示的にロックを意識することはほとんどありません。
たいていの場合、トランザクションだけ気をつけていたらDBMSが適切にロックを取得＆開放してくれます。
私もデッドロックなどロック関連の障害が発生した場合に調査のために学習するのですが、対応が終わってしばらくしたら細かい仕様は忘れてしまいますw
毎回忘れるのはもったいないので、ドキュメントを読み直して自分なりにまとめておこうと思い、この記事を書くことにしました。
当初、1つの記事にまとめようと思いましたが、書いているうちにボリュームが大きくなってしまったので下記に分割することにしました。
この記事は「行レベルロック: レコードロック / ギャップロック / ネクストキーロック / 他」です。
- ロックを確認するためのテーブルや設定
- テーブルレベルロック
- 行レベルロック: 共有ロック(S) / 排他ロック(X)
- 行レベルロック: インテンションロック
- 行レベルロック: レコードロック / ギャップロック / ネクストキーロック / 他 (この記事)
MySQLバージョン
執筆時点(2021年9月)の最新バージョン、8.0.26を利用します。
検証方法
今回の検証はmysqlコマンドを使って行います。
トランザクションを開始するためにBEGINコマンドを使います。
トランザクションはCOMMIT or ROLLBACKを実行するまで継続されます。
なお検証には下記のpiyosテーブルを使用します。
piyos
インデックス有無によるロック範囲の確認用にインデックスありのidx_numとインデックスなしのnumカラムを持っています。
他のテーブルと関連していません。
| Name | Type | Default | Nullable | Extra Definition |
|---|---|---|---|---|
| id | bigint | false | auto_increment | |
| idx_num | int | 0 | false | |
| num | int | 0 | false | |
| name | varchar(255) | true | ||
| created_at | datetime(6) | false | ||
| updated_at | datetime(6) | false |
- インデックス
| Name | Definition |
|---|---|
| idx_num | KEY idx_num (idx_num) USING BTREE |
| PRIMARY | PRIMARY KEY (id) USING BTREE |
検証のため下記のデータを入れています。
mysql> select * from piyos;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 3 | 40 | 50 | piyo3 | 2021-10-20 01:18:10.474960 | 2021-10-20 01:18:10.474960 |
| 5 | 30 | 60 | piyo5 | 2021-10-20 01:18:10.486576 | 2021-10-20 01:18:10.486576 |
| 8 | 30 | 70 | piyo8 | 2021-10-20 01:18:10.494597 | 2021-10-20 01:18:10.494597 |
| 9 | 10 | 80 | piyo9 | 2021-10-20 01:18:10.502085 | 2021-10-20 01:18:10.502085 |
+----+---------+-----+-------+----------------------------+----------------------------+
4 rows in set (0.01 sec)
ロックの種類
MySQLには"行レベルロック"と"テーブルレベルロック"が存在します。
名前から推測できる通り、"行レベルロック"とは行単位でロックし、"テーブルレベルロック"はテーブル全体をロックします。
詳細は下記ドキュメントをご覧ください。
ロックを確認する方法
別途公開している「ロックを確認するためのテーブルや設定」の記事をご確認ください。
行レベルロックを検証する
この記事では行レベルロックを検証します。
下記のドキュメントに記載されています。
行レベルロックは検証すべき点が多いので3つに分割して検証します。
- 共有ロック(S) / 排他ロック(X)
- インテンションロック
- レコードロック / ギャップロック / ネクストキーロック / 他
この記事では「レコードロック / ギャップロック / ネクストキーロック / 他」を検証します。
レコードロック
レコードロックの説明は下記の通り。
レコードロックは、インデックスレコードのロックです。 たとえば、SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;では、t.c1 の値が 10 の場合、他のトランザクションによる行の挿入、更新または削除が防止されます。
ドキュメントの例のようにインデックスが設定されているレコードをロックすると、そのレコードのみがロックされます。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from piyos where id = 5 for share;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 5 | 30 | 60 | piyo5 | 2021-10-20 00:28:16.536989 | 2021-10-20 00:28:16.536989 |
+----+---------+-----+-------+----------------------------+----------------------------+
1 row 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 |
+-------------+-----------+---------------+-------------+-----------+
| piyos | TABLE | IS | GRANTED | NULL |
| piyos | RECORD | S,REC_NOT_GAP | GRANTED | 5 |
+-------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)
この場合、id=5のレコードのみ共有ロックされいるため、前後にレコードを挿入したり更新したりすることができます。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into piyos (id, idx_num, num, created_at, updated_at) values (4, 4, 4, NOW(), NOW());
Query OK, 1 row affected (0.00 sec)
mysql> insert into piyos (id, idx_num, num, created_at, updated_at) values (6, 6, 6, NOW(), NOW());
Query OK, 1 row affected (0.00 sec)
mysql> update piyos set num = '777' where id = 8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update piyos set num = '3' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update piyos set num = '555' where id = 5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ギャップロック
ギャップロックの説明は下記の通り。
これと次に出てくるネクストキーロックが難しいところだと思います。
説明が長いので分解して動作検証していきます。
ギャップロックは、インデックスレコード間のギャップのロック、または最初のインデックスレコードの前または最後のインデックスレコードの後のギャップのロックです。 たとえば、SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;では、範囲内の既存のすべての値間のギャップがロックされているため、カラムにそのような値がすでに存在するかどうかにかかわらず、他のトランザクションが 15 の値をカラム t.c1 に挿入できなくなります。
インデックスレコード間のギャップのロック
まずは「インデックスレコード間のギャップのロック」を検証します。
piyosを範囲ロックします。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from piyos where id between 3 and 6 for share;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 3 | 40 | 50 | piyo3 | 2021-10-20 01:18:10.474960 | 2021-10-20 01:18:10.474960 |
| 5 | 30 | 60 | piyo5 | 2021-10-20 01:18:10.486576 | 2021-10-20 01:18:10.486576 |
+----+---------+-----+-------+----------------------------+----------------------------+
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 |
+-------------+-----------+---------------+-------------+-----------+
| piyos | TABLE | IS | GRANTED | NULL |
| piyos | RECORD | S,REC_NOT_GAP | GRANTED | 3 |
| piyos | RECORD | S | GRANTED | 5 |
| piyos | RECORD | S,GAP | GRANTED | 8 |
+-------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)
指定した範囲はidが3〜6です。
その範囲にはid in (3, 5)の2レコードしかないのですが、id = 8までロックされていることがわかります。
ギャップロックは指定した範囲を包括するインデックスレコードの間がロックされます。
そのため、下限の3はインデックスレコードid=3があるため下限はid=3となっていますが、上限の6はインデックスレコードがないため6以上のレコードであるid=8までがギャップロックされています。
試しに別トランザクションからギャップロック範囲内外にデータを挿入してみます。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into piyos (id, idx_num, num, created_at, updated_at) values (4, 4, 4, NOW(), NOW());
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into piyos (id, idx_num, num, created_at, updated_at) values (7, 7, 7, NOW(), NOW());
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into piyos (id, idx_num, num, created_at, updated_at) values (2, 2, 2, NOW(), NOW());
Query OK, 1 row affected (0.01 sec)
mysql> insert into piyos (id, idx_num, num, created_at, updated_at) values (10, 10, 10, NOW(), NOW());
Query OK, 1 row affected (0.00 sec)
ギャップロックされている3~8の範囲内である4や7はタイムアウトになりましたが、範囲外の2や10は挿入することができました。
なお、ギャップロックは通常の共有ロックとは違い更新や削除することができます。
mysql> update piyos set num = '777' where id = 5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update piyos set num = '777' where id = 8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from piyos where id = 8;
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
id=5は共有ロックのため、更新できませんがギャップロックであるid=8は更新したり削除したりできます。
ギャップロックされていたid=8を削除した場合、ギャップロックの範囲は次のインデックスレコードid=9まで拡大されます。
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 |
+-------------+-----------+---------------+-------------+-----------+
| piyos | TABLE | IS | GRANTED | NULL |
| piyos | RECORD | S,REC_NOT_GAP | GRANTED | 3 |
| piyos | RECORD | S | GRANTED | 5 |
| piyos | RECORD | S,GAP | GRANTED | 9 |
+-------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)
data_locksでギャップロックを確認する方法
なお、ギャップロックの有無はdata_locksのLOCK_MODEで確認することができます。
下記に先程のdata_locksを再掲します。
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 |
+-------------+-----------+---------------+-------------+-----------+
| piyos | TABLE | IS | GRANTED | NULL |
| piyos | RECORD | S,REC_NOT_GAP | GRANTED | 3 |
| piyos | RECORD | S | GRANTED | 5 |
| piyos | RECORD | S,GAP | GRANTED | 8 |
+-------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)
LOCK_DATA=3のLOCK_MODEはS,REC_NOT_GAPとなっています。
REC_NOT_GAPという文字から想像できると思いますがギャップロックされていないという意味です。
ただ、この情報だけだと３から見て前後どちらのギャップを指しているかわかりませんよね。
これはネクストキーロックの説明時に出てくるのですが、ギャップとはインデックスレコードの前を指していると覚えておくと良いです。
ということで、3より前にギャップロックはないということがわかります。
=>３に共有ロックとギャップロック
LOCK_DATE=5はREC_NOT_GAPと書かれていません。
これは5より前のインデックスレコードまでをギャップをロックしているということです。
=>５は共有ロック。４, 5にギャップロック
LOCK_DATE=8はS,GAPと書かれています。
これはギャップロックによりID=8と８より前のインデックスレコードまでのギャップをロックしているということです。
=>6, 7, 8にギャップロック
これらの情報をまとめると３, 5が共有ロックされており、3〜8がギャップロックされているということが読み取れます。
最初のインデックスレコードの前のギャップ
次は「最初のインデックスレコードの前のギャップ」を検証します。
piyosを範囲ロックします。
今回は最小idである3以下の値である2を下限にしています。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from piyos where id between 2 and 5 for share;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 3 | 40 | 50 | piyo3 | 2021-10-20 01:29:30.086868 | 2021-10-20 01:29:30.086868 |
| 5 | 30 | 60 | piyo5 | 2021-10-20 01:29:30.096378 | 2021-10-20 01:29:30.096378 |
+----+---------+-----+-------+----------------------------+----------------------------+
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 |
+-------------+-----------+-----------+-------------+-----------+
| piyos | TABLE | IS | GRANTED | NULL |
| piyos | RECORD | S | GRANTED | 3 |
| piyos | RECORD | S | GRANTED | 5 |
+-------------+-----------+-----------+-------------+-----------+
3 rows in set (0.00 sec)
data_locksを確認すると、ID=3のLOCK_MODEにREC_NOT_GAPと記載されていないので、3より前のレコードがギャップロックされていることがわかります。
ここの重要なポイントは、betweenではid=2以上を指定していますが、ギャックロックは1つ前のインデックスレコードまでがロック範囲となるため、最初のインデックスであるid=3より前は全てロックされているいうことです。
piyosに挿入してみます。
mysql> insert into piyos (id, idx_num, num, created_at, updated_at) values (2, 2, 2, NOW(), NOW());
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into piyos (id, idx_num, num, created_at, updated_at) values (1, 1, 1, NOW(), NOW());
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
上記の通り、3より前のレコードは全てロックされているため、betweenの範囲内のid=2だけでなく、範囲外のid=1もタイムアウトしてしまいます。
最後のインデックスレコードの後のギャップ
次は「最後のインデックスレコードの後のギャップ」を検証します。
piyosを範囲ロックします。
先ほどと異なり、最大idである9以上の値である10を上限にしています。
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from piyos where id between 5 and 10 for share;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 5 | 30 | 60 | piyo5 | 2021-10-20 01:29:30.096378 | 2021-10-20 01:29:30.096378 |
| 8 | 30 | 70 | piyo8 | 2021-10-20 01:29:30.104749 | 2021-10-20 01:29:30.104749 |
| 9 | 10 | 80 | piyo9 | 2021-10-20 01:29:30.113754 | 2021-10-20 01:29:30.113754 |
+----+---------+-----+-------+----------------------------+----------------------------+
3 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 |
+-------------+-----------+---------------+-------------+------------------------+
| piyos | TABLE | IS | GRANTED | NULL |
| piyos | RECORD | S,REC_NOT_GAP | GRANTED | 5 |
| piyos | RECORD | S | GRANTED | supremum pseudo-record |
| piyos | RECORD | S | GRANTED | 8 |
| piyos | RECORD | S | GRANTED | 9 |
+-------------+-----------+---------------+-------------+------------------------+
5 rows in set (0.01 sec)
data_locksを確認します。
LOCK_DATE=5, 8, 9のところを見ると「５の前にはギャップロックなし」「8, 9の前はギャップロックあり」と読めます。
そして、LOCK_DATE=supremum pseudo-recordがあります。これはインデックスレコードの上限を表す疑似レコードです。無限みたいなものですね。
supremum pseudo-recordのLOCK_MODEもREC_NOT_GAPが記載されていないため、上限から1つ前のインデックスレコード(id=9)までをギャップロックしているということになります。これは、9以降の全レコードをギャップロックしているという意味になります。
それではpiyosに挿入してみます。
mysql> insert into piyos (id, idx_num, num, created_at, updated_at) values (7, 7, 7, NOW(), NOW());
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into piyos (id, idx_num, num, created_at, updated_at) values (100, 100, 10, NOW(), NOW());
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
上記の通り、id=9からsupremum pseudo-recordまで全てロックされているため、betweenの範囲内のid=7だけでなく、範囲外のid=100もタイムアウトしてしまいます。
一意のインデックスを使用して一意の行を検索することで行をロックするステートメントでは、ギャップロックは必要ありません。 (これには、検索条件に複数カラムの一意のインデックスの一部のカラムのみが含まれるケースは含まれません。この場合は、ギャップロックが発生します。) たとえば、id カラムに一意のインデックスが設定されている場合、次のステートメントで使用されるのは id の値が 100 の行に対するインデックスレコードロックだけとなり、ほかのセッションがそのレコードの前にあるギャップに行を挿入するかどうかは問題ではなくなります。
これは今までの検証で何度も行ったidをピンポイントで指定した場合のことですね。idなど一意のインデックスレコードをピンポイントで指定した場合はギャップは発生しません。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from piyos where id = 3 for share;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 3 | 40 | 50 | piyo3 | 2021-10-20 01:29:30.086868 | 2021-10-20 01:29:30.086868 |
+----+---------+-----+-------+----------------------------+----------------------------+
1 row 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 |
+-------------+-----------+---------------+-------------+-----------+
| piyos | TABLE | IS | GRANTED | NULL |
| piyos | RECORD | S,REC_NOT_GAP | GRANTED | 3 |
+-------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)
id にインデックスが設定されていなかったり、一意でないインデックスが設定されていたりすると、このステートメントで先行するギャップがロックされます。
一意でないインデックス(idx_num)を使ってロックしてみます。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from piyos where idx_num = 30 for share;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 5 | 30 | 60 | piyo5 | 2021-10-20 02:12:13.277402 | 2021-10-20 02:12:13.277402 |
| 8 | 30 | 70 | piyo8 | 2021-10-20 02:12:13.284300 | 2021-10-20 02:12:13.284300 |
+----+---------+-----+-------+----------------------------+----------------------------+
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 |
+-------------+-----------+---------------+-------------+-----------+
| piyos | TABLE | IS | GRANTED | NULL |
| piyos | RECORD | S | GRANTED | 30, 5 |
| piyos | RECORD | S | GRANTED | 30, 8 |
| piyos | RECORD | S,REC_NOT_GAP | GRANTED | 5 |
| piyos | RECORD | S,REC_NOT_GAP | GRANTED | 8 |
| piyos | RECORD | S,GAP | GRANTED | 40, 3 |
+-------------+-----------+---------------+-------------+-----------+
6 rows in set (0.01 sec)
idx_num=30はid=5と8のレコードです。
data_locksを見ると、下記の情報がわかります。
- idx_num=30(id=5)のレコードは共有ロック、その前のレコードはギャップロックされている
- idx_num=30(id=8)のレコードは共有ロック、その前のレコードはギャップロックされている
- id=5のレコードが共有ロックされている(ギャップロックなし)
- id=8のレコードが共有ロックされている(ギャップロックなし)
- idx_num=40(id=3)のレコードがギャップロックされている
「このステートメントで先行するギャップがロックされます」という説明は、idx_num=30より前がギャップロックされていることから確認できます。
一方、30の後ろのレコードである40までギャップロックされていることがわかります。
40までまでギャップロックされる理由は30が一意ではないためです。
一意ではないレコードは値が同じであっても厳密には順序があります。
文字だけでは分かりづらいので、下記図を使って説明します。
もしidx_num=30までしかギャップロックしなかった場合、ギャップロックは該当レコードよりも前方のことを指すため、idx_num=30だけどid=8より後ろ(赤色)の部分はギャップロックされていないことになります。
この赤色をカバーするために、1つ後ろのidx_num=40のレコードまでギャップロックの範囲が広がっています。
上記の挙動どおりになっているか動作確認します。
mysql> insert into piyos (idx_num, num, created_at, updated_at) values (5, 5, NOW(), NOW());
Query OK, 1 row affected (0.00 sec)
mysql> insert into piyos (idx_num, num, created_at, updated_at) values (15, 5, NOW(), NOW());
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into piyos (idx_num, num, created_at, updated_at) values (39, 5, NOW(), NOW());
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into piyos (idx_num, num, created_at, updated_at) values (41, 5, NOW(), NOW());
Query OK, 1 row affected (0.01 sec)
ロック範囲内のidx_num=15や39はタイムアウトになりますが、範囲外の5や41は挿入することができます。
さまざまなトランザクションによってギャップ上に競合するロックを保持できることも、ここで注目するべき点です。 たとえば、トランザクション A はギャップ上に共有ギャップロック (ギャップ S ロック) を保持できる一方で、トランザクション B は同じギャップ上に排他ギャップロック (ギャップ X ロック) を保持します。 競合するギャップロックが許可される理由は、レコードがインデックスからパージされる場合に、さまざまなトランザクションによってレコード上に保持されたギャップロックをマージする必要があるためです。
こちらを検証します。
まず、共有ギャップロックを取得します。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from piyos where id between 5 and 6 for share;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 5 | 30 | 60 | piyo5 | 2021-10-20 02:12:13.277402 | 2021-10-20 02:12:13.277402 |
+----+---------+-----+-------+----------------------------+----------------------------+
1 row 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 |
+-------------+-----------+---------------+-------------+-----------+
| piyos | TABLE | IS | GRANTED | NULL |
| piyos | RECORD | S,REC_NOT_GAP | GRANTED | 5 |
| piyos | RECORD | S,GAP | GRANTED | 8 |
+-------------+-----------+---------------+-------------+-----------+
3 rows in set (0.01 sec)
次に別のトランザクションから共有ギャップロックされているid=8に対して、排他ギャップロックを取得してみます。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from piyos where id = 6 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 |
+-------------+-----------+---------------+-------------+-----------+
| piyos | TABLE | IX | GRANTED | NULL |
| piyos | RECORD | X,GAP | GRANTED | 8 |
| piyos | TABLE | IS | GRANTED | NULL |
| piyos | RECORD | S,REC_NOT_GAP | GRANTED | 5 |
| piyos | RECORD | S,GAP | GRANTED | 8 |
+-------------+-----------+---------------+-------------+-----------+
5 rows in set (0.00 sec)
id=8に対してX,GAPを取得することができました。
InnoDB のギャップロックは「「純粋に阻害」」です。つまり、その唯一の目的は、他のトランザクションがギャップに挿入されないようにすることです。 ギャップロックは共存できます。 あるトランザクションによって取得されたギャップロックによって、別のトランザクションが同じギャップに対してギャップロックを取得することが妨げられることはありません。 共有ギャップロックと排他ギャップロックに違いはありません。 これらは互いに競合せず、同じ機能を実行します。
供給ギャップロックと排他ギャップロックに違いはないようです。これらは競合もせず、ギャップにレコードが挿入されないようにするために存在しています。
挿入以外の更新や削除ができることは少し前に検証したのでここでは省略します。
ネクストキーロック
次のキーロックは、インデックスレコードのレコードロックと、インデックスレコードの前のギャップのギャップロックの組み合わせです。
「次のキーロック」はネクストキーロックのことですね。固有名詞も翻訳されてしまった分かりづらいｗ
この後もちょいちょい出てくるので読み替えてください。
InnoDB は、テーブルインデックスを検索またはスキャンするときに、生成されたインデックスレコード上に共有ロックまたは排他ロックを設定するという方法で、行レベルロックを実行します。 したがって、行レベルロックは、実際にはインデックスレコードロックです。 インデックスレコードに対する次のキーロックは、そのインデックスレコードの前の 「gap」 にも影響します。 つまり、ネクストキーロックは、インデックスレコードロックと、そのインデックスレコードの前のギャップに対するギャップロックとを組み合わせたものです。 あるセッションがインデックス内のレコード R 上に共有ロックまたは排他ロックを持っている場合は、別のセッションがインデックスの順番で R の直前にあるギャップに新しいインデックスレコードを挿入できません。
長々と書かれていますが、ネクストキーロックとは「インデックスレコードのレコードロックと、インデックスレコードの前のギャップのギャップロックの組み合わせです」の一文が全てです。
ネクストキーロックというと名前からレコードロックしたレコードと、その次(後ろ)のレコードの話のように感じますが、レコードロックしたレコードと前のギャップのギャップロックの組み合わせのことを言います。
あるインデックスに値 10、11、13、20 が含まれているとします。 このインデックスで使用可能な次のキーロックは、次の間隔を対象としています。丸カッコは間隔エンドポイントの除外を示し、角カッコはエンドポイントの包含を示します:
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
最後の間隔ではネクストキーロックによって、インデックス内の最大値を上回るギャップ、およびインデックス内の実際のどの値よりも大きい値を持つ「最小上限」の擬似レコードがロックされます。 最小上限は実際のインデックスレコードではないため、事実上、このネクストキーロックによってロックされるのは、最大インデックス値のあとにあるギャップのみです。
先ほど説明したとおり、ネクストキーロックはレコードロックとギャップロックの組み合わせのことを言っているだけです。
ドキュメントに記載されていることは、すでにギャップロックの章で説明＆検証済みのため省略します。
インテンションロックの挿入
タイトルが分かりづらいですが、英語だと"Insert Intention Locks"となっているため、インテンションロックを挿入するわけではなく、Insert時のインテンションロックと考えたほうが理解しやすいです。
挿入意図ロックは、行の挿入前に INSERT 操作によって設定されるギャップロックのタイプです。 このロックは、同じインデックスギャップに挿入する複数のトランザクションは、そのギャップ内の同じ場所に挿入しなければ相互に待機する必要がないように、意図的に挿入することを示しています。 値が 4 と 7 のインデックスレコードが存在すると仮定します。 5 と 6 の値をそれぞれ挿入しようとする個別のトランザクションでは、挿入された行の排他ロックを取得する前に、挿入意図ロックを使用して 4 と 7 のギャップがロックされますが、行が競合していないため相互にブロックされません。
「挿入意図ロック」は「insert時のインテンションロック」のことです。
ギャップロックされている範囲にレコードをInsertできないことはギャップロックのところで検証しましたが、ここではその仕組みをどのように実現しているのかを説明してます。
次の例は、挿入されたレコードの排他ロックを取得する前に挿入意図ロックを取得するトランザクションを示しています。 この例には、A と B の 2 つのクライアントが登場します。
クライアント A は、2 つのインデックスレコード (90 および 102) を含むテーブルを作成し、100 を超える ID を持つインデックスレコードに排他ロックを設定するトランザクションを開始します。 排他ロックには、レコード 102 の前にギャップロックが含まれます:
mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);
mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id |
+-----+
| 102 |
+-----+
クライアント B はトランザクションを開始して、ギャップにレコードを挿入します。 トランザクションは、排他ロックの取得を待機している間、挿入意図ロックを取得します。
mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);
上記の通り、ドキュメントにも例が載っていますが、data_locksテーブルを見ながら上記に近い検証をしてみます。
まずは排他ロックを取得します。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from piyos where id > 6 for update;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 8 | 30 | 70 | piyo8 | 2021-10-20 02:12:13.284300 | 2021-10-20 02:12:13.284300 |
| 9 | 10 | 80 | piyo9 | 2021-10-20 02:12:13.292335 | 2021-10-20 02:12:13.292335 |
+----+---------+-----+-------+----------------------------+----------------------------+
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 |
+-------------+-----------+-----------+-------------+------------------------+
| piyos | TABLE | IX | GRANTED | NULL |
| piyos | RECORD | X | GRANTED | supremum pseudo-record |
| piyos | RECORD | X | GRANTED | 8 |
| piyos | RECORD | X | GRANTED | 9 |
+-------------+-----------+-----------+-------------+------------------------+
4 rows in set (0.00 sec)
id=８の前にもギャップロックがかかっているので、id=5以降の全レコードにギャップロックされています。
次に別のトランザクションから、ギャップの範囲内id=７にレコードを挿入してみます。ギャップロックで検証したとおり、こちらは失敗します。
mysql> insert into piyos (id, idx_num, num, created_at, updated_at) values (7, 7, 7, NOW(), NOW());
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
上記の通りタイムアウトするのですが、タイムアウトする前data_locks確認すると下記のようになっています。
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 |
+-------------+-----------+------------------------+-------------+------------------------+
| piyos | TABLE | IX | GRANTED | NULL |
| piyos | RECORD | X,GAP,INSERT_INTENTION | WAITING | 8 |
| piyos | TABLE | IX | GRANTED | NULL |
| piyos | RECORD | X | GRANTED | supremum pseudo-record |
| piyos | RECORD | X | GRANTED | 8 |
| piyos | RECORD | X | GRANTED | 9 |
+-------------+-----------+------------------------+-------------+------------------------+
6 rows in set (0.00 sec)
２つ目のレコードがLOCK_MODE＝X,GAP,INTSERT_INTENTIONとなっています。LOCK_STATUS=WAITINGのため、ロックの取得を待機しています(取得できていない)。
これがこの章で言っているInsert時のインテンションロックです。
レコードをInsertするとき、事前にInsertするレコードの位置にギャップロックを取得しようとします。
当該レコードがギャップロックされていなければ、このロックが取得できるためレコードがInsertできるのですが、当該箇所がギャップロックされている場合はロックできず、タイムアウトしています。
ギャップに対してInsertするときにタイムアウトするのはInsert時のインテンションロックという仕様のためだということがわかりました。
AUTO-INC ロック
AUTO-INC ロックは、AUTO_INCREMENT カラムを含むテーブルに挿入されるトランザクションによって取得される特別なテーブルレベルロックです。 もっとも単純なケースでは、あるトランザクションがテーブルに値を挿入している場合に、ほかのトランザクションはそのテーブルへのそれぞれの挿入を待機する必要があるので、最初のトランザクションによって挿入された行が、連続する主キー値を受け取ります。
こちらはオートインクリメントを設定しているカラムを持っているテーブルに対するロックです。
意味はわかりやすく、あるトランザクションで挿入するときにオートインクリメントの値を取得している間は他のトランザクションからはオートインクリメントの値を取得できないようにするロックです。
これも検証して可視化したかったのですが、オートインクリメントロックを遅延させる方法が思いつかなかったので検証できませんでした。。。
空間インデックスの述語ロック
ドキュメントには空間インデックスの述語ロックの説明がありますが、前提知識が必要なことと他と比べて使用頻度が低いため、この記事では省略します。
まとめ
最後まで読んでいただきありがとうございます！
テーブルレベルロックと行レベルロックの2つのドキュメントを検証するだけだと思って書き始めましたが結構な量になってしまいました。
この記事はスキマ時間に書いていたので、書き始めてから1ヶ月ほどかかってしまいましたｗ
ただ、記事にすることで今まで以上にMySQLのロックについて詳しくなれた気がするので良かったと思います！
読んでいただいた方の参考に少しでもなれれば幸いです！