データベースを使っていてロックの存在を知らない方はいないと思いますが、実際にプログラムからデータベースを操作する時に明示的にロックを意識することはほとんどありません。
たいていの場合、トランザクションだけ気をつけていたらDBMSが適切にロックを取得＆開放してくれます。
私もデッドロックなどロック関連の障害が発生した場合に調査のために学習するのですが、対応が終わってしばらくしたら細かい仕様は忘れてしまいますw
毎回忘れるのはもったいないので、ドキュメントを読み直して自分なりにまとめておこうと思い、この記事を書くことにしました。
当初、1つの記事にまとめようと思いましたが、書いているうちにボリュームが大きくなってしまったので下記に分割することにしました。
この記事は「行レベルロック: 共有ロック(S) / 排他ロック(X)」です。
- ロックを確認するためのテーブルや設定
- テーブルレベルロック
- 行レベルロック: 共有ロック(S) / 排他ロック(X) (この記事)
- 行レベルロック: インテンションロック
- 行レベルロック: レコードロック / ギャップロック / ネクストキーロック / 他
MySQLバージョン
執筆時点(2021年9月)の最新バージョン、8.0.26を利用します。
検証方法
今回の検証はmysqlコマンドを使って行います。
トランザクションを開始するためにBEGINコマンドを使います。
トランザクションはCOMMIT or ROLLBACKを実行するまで継続されます。
なお検証には下記のhoges, fugas, piyosテーブルを使用します。
hoges
インデックス有無によるロック範囲の確認用にインデックスありの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 |
|---|---|
| PRIMARY | PRIMARY KEY (id) USING BTREE |
| idx_num | KEY idx_num (idx_num) USING BTREE |
fugas
hogesと関連させるため、hoge_idを持ち、外部キー制約を設定しています。
他はhogesと同じ構成です。
| Name | Type | Default | Nullable | Extra Definition |
|---|---|---|---|---|
| id | bigint | false | auto_increment | |
| hoge_id | bigint | false | ||
| 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 |
|---|---|
| hoge_id | KEY hoge_id (hoge_id) USING BTREE |
| idx_num | KEY idx_num (idx_num) USING BTREE |
| PRIMARY | PRIMARY KEY (id) USING BTREE |
- 外部キー
| Name | Type | Definition |
|---|---|---|
| hoge_id_on_fugas | FOREIGN KEY | FOREIGN KEY (hoge_id) REFERENCES hoges (id) |
piyos
hogesと同じ構成です。こちらは他のテーブルと関連していません。
| 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 hoges;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 1 | 1 | 1 | hoge1 | 2021-09-22 07:42:03.146892 | 2021-09-22 07:42:03.146892 |
| 3 | 3 | 3 | hoge3 | 2021-09-22 07:42:03.154283 | 2021-09-22 07:42:03.154283 |
| 5 | 5 | 5 | hoge5 | 2021-09-22 07:42:03.162000 | 2021-09-22 07:42:03.162000 |
| 7 | 7 | 7 | hoge7 | 2021-09-22 07:42:03.176757 | 2021-09-22 07:42:03.176757 |
+----+---------+-----+-------+----------------------------+----------------------------+
4 rows in set (0.00 sec)
mysql> select * from fugas;
+----+---------+---------+-----+-------+----------------------------+----------------------------+
| id | hoge_id | idx_num | num | name | created_at | updated_at |
+----+---------+---------+-----+-------+----------------------------+----------------------------+
| 1 | 1 | 1 | 1 | fuga1 | 2021-09-22 07:42:03.197563 | 2021-09-22 07:42:03.197563 |
| 3 | 3 | 3 | 3 | fuga3 | 2021-09-22 07:42:03.205208 | 2021-09-22 07:42:03.205208 |
| 5 | 5 | 5 | 5 | fuga5 | 2021-09-22 07:42:03.212828 | 2021-09-22 07:42:03.212828 |
| 7 | 7 | 7 | 7 | fuga7 | 2021-09-22 07:42:03.220833 | 2021-09-22 07:42:03.220833 |
+----+---------+---------+-----+-------+----------------------------+----------------------------+
4 rows in set (0.00 sec)
mysql> select * from piyos;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 1 | 1 | 1 | piyo1 | 2021-10-01 08:50:52.882406 | 2021-10-01 08:50:52.882406 |
| 3 | 3 | 3 | piyo3 | 2021-10-01 08:50:52.891249 | 2021-10-01 08:50:52.891249 |
| 5 | 5 | 5 | piyo5 | 2021-10-01 08:50:52.898149 | 2021-10-01 08:50:52.898149 |
| 7 | 7 | 7 | piyo7 | 2021-10-01 08:50:52.904952 | 2021-10-01 08:50:52.904952 |
+----+---------+-----+-------+----------------------------+----------------------------+
4 rows in set (0.00 sec)
ロックの種類
MySQLには"行レベルロック"と"テーブルレベルロック"が存在します。
名前から推測できる通り、"行レベルロック"とは行単位でロックし、"テーブルレベルロック"はテーブル全体をロックします。
詳細は下記ドキュメントをご覧ください。
ロックを確認する方法
別途公開している「ロックを確認するためのテーブルや設定」の記事をご確認ください。
行レベルロックを検証する
この記事では行レベルロックを検証します。
行レベルロックは下記のドキュメントに記載されています。
行レベルロックは検証すべき点が多いので3つに分割して検証します。
- 共有ロック(S) / 排他ロック(X)
- インテンションロック
- レコードロック / ギャップロック / ネクストキーロック / 他
この記事では「共有ロック(S) / 排他ロック(X)」を検証します。
InnoDB では、2 つのロックタイプ (共有 (S) ロックと排他 (X) ロック) がある標準の行レベルロックが実装されます。
・ 共有 (S) ロックでは、ロックを保持するトランザクションによる行の読み取りが許可されます。
・ 排他 (X) ロックでは、ロックを保持するトランザクションによる行の更新または削除が許可されます
2種類のロックがあるようなのでそれぞれ検証していきます。
共有 (S) ロック
まずは共有 (S) ロックから確認します。
Sは"ロックを保持するトランザクションによる行の読み取りが許可されます。"だそうです。
どういうことでしょうか？
共有ロックはSELECT ... FOR SHAREで取得することができます。
SELECT ... FOR SHAREについては次のように記載されています。
ほかのセッションもその行を読み取ることができますが、トランザクションがコミットするまで変更することはできません。 これらの行のいずれかがコミットされていない別のトランザクションによって変更された場合、クエリーはそのトランザクションが終了するまで待機してから、最新の値を使用します。
実際に試してみます。行レベルロックはトランザクションを開始しておかないと即時解除されてしまうのでbeginを使っています。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from piyos where id >= 3 and id <= 5 for share;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 3 | 3 | 3 | piyo3 | 2021-10-01 08:52:04.151293 | 2021-10-01 08:52:04.151293 |
| 5 | 5 | 5 | piyo5 | 2021-10-01 08:52:04.160298 | 2021-10-01 08:52:04.160298 |
+----+---------+-----+-------+----------------------------+----------------------------+
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 |
+-------------+-----------+---------------+-------------+-----------+
| piyos | TABLE | IS | GRANTED | NULL |
| piyos | RECORD | S,REC_NOT_GAP | GRANTED | 3 |
| piyos | RECORD | S | GRANTED | 5 |
+-------------+-----------+---------------+-------------+-----------+
3 rows in set (0.02 sec)
1番目のLOCK_MODE=ISや2番目のREC_NOT_GAPは後ほど説明するのでここでは無視します。
LOCK_DATEにはPrimary keyが入っているので、id in (3, 5)のテーブルに共有ロック（LOCK_MODE=S)が取得されていることがわかります。
ほかのセッションもその行を読み取ることができますが、トランザクションがコミットするまで変更することはできません。
まずはこれを検証してみます。別のコンソールでmysqlコマンドを実行して検証します。
mysql> select * from piyos;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 1 | 1 | 1 | piyo1 | 2021-10-01 08:52:04.143776 | 2021-10-01 08:52:04.143776 |
| 3 | 3 | 3 | piyo3 | 2021-10-01 08:52:04.151293 | 2021-10-01 08:52:04.151293 |
| 5 | 5 | 5 | piyo5 | 2021-10-01 08:52:04.160298 | 2021-10-01 08:52:04.160298 |
| 7 | 7 | 7 | piyo7 | 2021-10-01 08:52:04.167119 | 2021-10-01 08:52:04.167119 |
+----+---------+-----+-------+----------------------------+----------------------------+
4 rows in set (0.00 sec)
mysql> update piyos set num = 2 where id = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update piyos set num = 4 where id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
READはできていて、ロック範囲外のデータのUPDATEもできますが、ロックされているデータのUPDATEはできないことがわかります。
これらの行のいずれかがコミットされていない別のトランザクションによって変更された場合、クエリーはそのトランザクションが終了するまで待機してから、最新の値を使用します。
次にこちらを検証してみます。
最初に共有ロックを取得しているトランザクションで更新を行います。
mysql> update piyos set num = 4 where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
次に別のコンソールでSELECT ... FOR SHAREを使って共有ロックを取得します。
mysql> select * from piyos where id = 3;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 3 | 3 | 3 | piyo3 | 2021-10-01 08:52:04.151293 | 2021-10-01 08:52:04.151293 |
+----+---------+-----+-------+----------------------------+----------------------------+
1 row in set (0.00 sec)
mysql> select * from piyos where id = 3 for share;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from piyos where id = 5 for share;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 5 | 5 | 5 | piyo5 | 2021-10-01 08:52:04.160298 | 2021-10-01 08:52:04.160298 |
+----+---------+-----+-------+----------------------------+----------------------------+
1 row in set (0.00 sec)
FOR SHAREをつけない場合、変更前の古い情報が取得できます。
FOR SHAREをつけた場合、別トランザクションで変更されているレコードは取得することができずタイムアウトになります。
なお、FOR SHAREをつけた場合でも、別トランザクションで変更されていない場合は取得できます。
ドキュメントを読み進めると、下記のように記載されています。
トランザクション T1 が行 r に対する共有 (S) ロックを保持している場合、別のトランザクション T2 からの行 r に対するロック要求は次のように処理されます。
・ T2 による S ロックに対するリクエストは、すぐに付与できます。 結果として、T1 と T2 の両方が r 上で S ロックを保持します。
・ T2 による X ロックに対するリクエストは、すぐに付与できません。
こちらを検証します。
1つ目の点は先程検証した通り、別のトランザクションで変更されていない場合は共有ロックを取得することができます。
data_locksを確認するとLOCK_MODE=S, LOCK_DATE=5のレコードが2つあるので２箇所から共有ロックが取得できていることがわかります。
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 | TABLE | IX | GRANTED | NULL |
| piyos | TABLE | IS | GRANTED | NULL |
| piyos | RECORD | S,REC_NOT_GAP | GRANTED | 3 |
| piyos | RECORD | S | GRANTED | 5 |
| piyos | RECORD | X,REC_NOT_GAP | GRANTED | 3 |
+-------------+-----------+---------------+-------------+-----------+
7 rows in set (0.01 sec)
次に排他ロックを取得してみます。なお、排他ロックはSELECTにFOR UPDATEをつけたり、UPDATEなど更新処理を行うことで取得することができます。
mysql> select * from piyos where id = 5 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
こちらもドキュメント通り、排他ロックは取得できませんでした。
まとめ
共有ロックは、別のセッションで更新されているレコードは取得することができません。
また、共有ロックを取得している場合、他のセッションから更新することができません。
このことから共有ロックを取得することで、ロックしているデータが他のセッションで変更されておらず、ロックを取得している間は変更されないことを保証することができます。
共有ロックが使われる操作
共有ロックはどのように使われるのでしょうか？
最も代表的なパターンは、更新クエリーを発行する際にwhere句などで別のテーブルをjoinした場合です。
例えば、fugas.id = 1に関連しているhogesのnameを更新するクエリーを発行してみます。
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> update hoges h inner join fugas f on h.id = f.hoge_id set h.name = 'update name' where f.id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
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 |
+-------------+-----------+---------------+-------------+-----------+
| hoges | TABLE | IX | GRANTED | NULL |
| fugas | TABLE | IS | GRANTED | NULL |
| fugas | RECORD | S,REC_NOT_GAP | GRANTED | 1 |
| hoges | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+-------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)
hogesテーブルは実際に更新を行うため、排他ロック(X)が取得されていますが、更新は行わないfugasテーブルは共有ロックが取得されています。
共有ロックを取得することで他のトランザクションが値を更新しておらず、ロックを取得している間は変更されないことを保証することができます。
これにより、Updateを行っている最中にfugasテーブルのid=1のレコードが更新されて、Updateの対象が変わってしまうことを防いでいます。
排他 (X) ロック
次は排他ロックについて検証します。
再掲ですが、ドキュメントには下記のように記載されています。
InnoDB では、2 つのロックタイプ (共有 (S) ロックと排他 (X) ロック) がある標準の行レベルロックが実装されます。
・ 共有 (S) ロックでは、ロックを保持するトランザクションによる行の読み取りが許可されます。
・ 排他 (X) ロックでは、ロックを保持するトランザクションによる行の更新または削除が許可されます
排他ロックはレコードを更新・削除したり、SELECT ... FOR UPDATEで取得することができます。
SELECT ... FOR UPDATEについては次のように記載されています。
他のトランザクションは、これらの行の更新、SELECT ... FOR SHARE の実行、または特定のトランザクション分離レベルでのデータの読取りをブロックされます。
実際に試してみます。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from piyos where id >= 3 and id <= 5 for update;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name | created_at | updated_at |
+----+---------+-----+-------+----------------------------+----------------------------+
| 3 | 3 | 3 | piyo3 | 2021-10-01 08:52:04.151293 | 2021-10-01 08:52:04.151293 |
| 5 | 5 | 5 | piyo5 | 2021-10-01 08:52:04.160298 | 2021-10-01 08:52:04.160298 |
+----+---------+-----+-------+----------------------------+----------------------------+
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,REC_NOT_GAP | GRANTED | 3 |
| piyos | RECORD | X | GRANTED | 5 |
+-------------+-----------+---------------+-------------+-----------+
3 rows in set (0.00 sec)
SELECT ... FOR UPDATEを使用してid in (3, 5)に排他ロックを取得します。
performance_schema.data_locksの結果からも排他ロックが取得できていることがわかります。
・ 排他 (X) ロックでは、ロックを保持するトランザクションによる行の更新または削除が許可されます
まずはこちらを検証します。
mysql> update piyos set num = 4 where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
記載されている通り、同一トランザクション内では問題なく更新することができました。
他のトランザクションは、これらの行の更新、SELECT ... FOR SHARE の実行、または特定のトランザクション分離レベルでのデータの読取りをブロックされます。
次にこちらを検証します。
mysql> update piyos set num = 4 where id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from piyos where id >= 3 and id <= 5 for share;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
記載されている通り、更新処理や共有ロックを取得することはできませんでした。
なお、または特定のトランザクション分離レベルでのデータの読取りをブロックされます。と記載されていますが、分離レベルの話も検証しだすとキリがなくなりそうだったので今回は省略します。
ちなみに、トランザクション分離レベルとはトランザクションの挙動を定義しているもので、変更することでトランザクションの挙動を変更することができます。
気になる方は下記のドキュメントをご覧ください。
まとめ
排他ロックは、ロックしたレコードを他セッションから更新することを禁止します。また共有ロックを取得することも禁止します。
他のセッションから更新できないという挙動は共有ロックと同様ですが、共有ロックの場合は他のセッションからロックを取得することができるため他のセッションで先に更新されてしまう可能性があります。
排他ロックの場合は他のセッションからロックを取得することも禁止しているのでより強いロックとなっています。
排他ロックが使われる操作
排他ロックは共有ロックに比べてイメージしやすいと思います。
代表的なパターンは、更新したり削除したりする場合です。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into piyos (created_at, updated_at) values (NOW(), NOW());
Query OK, 1 row affected (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 | IX | GRANTED | NULL |
+-------------+-----------+-----------+-------------+-----------+
1 row in set (0.01 sec)
mysql> update piyos set num = 4 where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
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,REC_NOT_GAP | GRANTED | 3 |
+-------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)
mysql> delete from piyos where id = 5;
Query OK, 1 row affected (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,REC_NOT_GAP | GRANTED | 3 |
| piyos | RECORD | X,REC_NOT_GAP | GRANTED | 5 |
+-------------+-----------+---------------+-------------+-----------+
3 rows in set (0.00 sec)
insertの場合はトランザクションを確定するまで他のトランザクションから行は見えないので行ロックは取得されません。
updateやdeleteの場合、当該レコードが排他ロックされていることがわかります。
次回
行レベルロックの共有ロック(S) / 排他ロック(X) が確認できたので、次の記事では「行レベルロック: インテンションロック」を検証していきます。