MySQLのロックについて公式ドキュメントを読みながら動作検証してみた〜テーブルレベルロック〜

データベースを使っていてロックの存在を知らない方はいないと思いますが、実際にプログラムからデータベースを操作する時に明示的にロックを意識することはほとんどありません。

たいていの場合、トランザクションだけ気をつけていたらDBMSが適切にロックを取得＆開放してくれます。

私もデッドロックなどロック関連の障害が発生した場合に調査のために学習するのですが、対応が終わってしばらくしたら細かい仕様は忘れてしまいますw

毎回忘れるのはもったいないので、ドキュメントを読み直して自分なりにまとめておこうと思い、この記事を書くことにしました。

当初、1つの記事にまとめようと思いましたが、書いているうちにボリュームが大きくなってしまったので下記に分割することにしました。

この記事は「テーブルレベルロック」です。

* [ロックを確認するためのテーブルや設定](https://qiita.com/ham0215/items/89fc277850d32f5b17cd)
* テーブルレベルロック (この記事)
* [行レベルロック: 共有ロック(S) / 排他ロック(X)](https://qiita.com/ham0215/items/b9efc718670b1d2d48c1)
* [行レベルロック: インテンションロック](https://qiita.com/ham0215/items/2f38a2949d9012074c3d)
* [行レベルロック: レコードロック / ギャップロック / ネクストキーロック / 他](https://qiita.com/ham0215/items/99679d499869365446ec)

# MySQLバージョン

執筆時点(2021年9月)の最新バージョン、`8.0.26`を利用します。

# 検証方法

今回の検証はmysqlコマンドを使って行います。

https://dev.mysql.com/doc/refman/5.6/ja/mysql-commands.html

トランザクションを開始するために`BEGIN`コマンドを使います。
トランザクションは`COMMIT` or `ROLLBACK`を実行するまで継続されます。

https://dev.mysql.com/doc/refman/5.6/ja/commit.html

なお検証には下記の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には"行レベルロック"と"テーブルレベルロック"が存在します。
名前から推測できる通り、"行レベルロック"とは行単位でロックし、"テーブルレベルロック"はテーブル全体をロックします。
詳細は下記ドキュメントをご覧ください。

https://dev.mysql.com/doc/refman/8.0/ja/internal-locking.html

# ロックを確認する方法

別途公開している「ロックを確認するためのテーブルや設定」の記事をご確認ください。

https://qiita.com/ham0215/items/89fc277850d32f5b17cd

# テーブルレベルロックを検証する

この記事ではテーブルレベルロックを検証します。
テーブルレベルロックは下記のドキュメントに記載されています。

https://dev.mysql.com/doc/refman/5.6/ja/lock-tables.html

テーブルロックは `LOCK TABLES tbl_name lock_type`を実行すると取得することができます。
tbl_nameには対象のテーブル名を、lock_typeにはロックの種類を指定します。
ロックは`UNLOCK TABLES`を実行することで解除できます。

lock_typeにはREADとWRITEの2種類があるようです。それぞれ検証してみましょう。

## READ [LOCAL] ロック

piyosテーブルに対してREADロックを取得します。
なお、LOCK TABLESはトランザクションを開始しなくても、UNLOCK TABLESを実行したり、ロックを取得したセッションを終了するまでロックが継続するようです。

```
mysql> LOCK TABLES piyos read;
Query OK, 0 rows affected (0.00 sec)

mysql> select OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, OWNER_THREAD_ID, OWNER_EVENT_ID from performance_schema.metadata_locks where OBJECT_SCHEMA = 'app_development' AND OBJECT_TYPE='TABLE';
+-------------+------------------+-------------+-----------------+----------------+
| OBJECT_NAME | LOCK_TYPE        | LOCK_STATUS | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+------------------+-------------+-----------------+----------------+
| piyos       | SHARED_READ_ONLY | GRANTED     |              51 |             89 |
+-------------+------------------+-------------+-----------------+----------------+
1 row in set (0.00 sec)
```

metadata_locksからpiyosテーブルに対してLOCK_TYPE=SHARED_READ_ONLYのロックがかかっていることがわかります。

> このロックを保持しているセッションは、テーブルを読み取ることができます (ただし、書き込みはできません)。

同一トランザクションで読み取りや書き込みを実行します。

```
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.01 sec)

mysql> insert into piyos (created_at, updated_at) values (NOW(), NOW());
ERROR 1099 (HY000): Table 'piyos' was locked with a READ lock and can't be updated

mysql> update piyos set num = 2 where id = 1;
ERROR 1099 (HY000): Table 'piyos' was locked with a READ lock and can't be updated

mysql> delete from piyos where id = 1;
ERROR 1099 (HY000): Table 'piyos' was locked with a READ lock and can't be updated
```

ドキュメントに記載されている通り、参照はできましたが書き込みはできませんでした。

> 複数のセッションが同時にテーブルに対する READ ロックを取得できます。

別のセッションでREADロックを取得してみます。

```
mysql> LOCK TABLES piyos read;
Query OK, 0 rows affected (0.00 sec)
```

READロックは問題なく取得できます。
別のセッションでWRITEロックを取得してみます。

```
mysql> LOCK TABLES piyos write;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```

WRITEロックは取得できませんでした。

> ほかのセッションは、READ ロックを明示的に取得することなく、テーブルを読み取ることができます。

別のトランザクションでREADロックを取得せずにSELECTしてみました。

```
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)
```

問題なく取得できました。

> LOCAL 修飾子を使用すると、ロックが保持されている間、ほかのセッションによる競合しない INSERT ステートメント (並列挿入) を実行できます。(セクション8.10.3「同時挿入」を参照してください。)ただし、ロックを保持している間、サーバーの外部にあるプロセスを使用してデータベースを操作しようとしている場合は、READ LOCAL を使用できません。InnoDB テーブルの場合、READ LOCAL は READ と同じです。

別のセッションでロックしていないhogesに対して更新処理を行います。

```
mysql> insert into hoges (created_at, updated_at) values (NOW(), NOW());
Query OK, 1 row affected (0.00 sec)

mysql> select * from hoges;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name  | created_at                 | updated_at                 |
+----+---------+-----+-------+----------------------------+----------------------------+
|  1 |       1 |   1 | hoge1 | 2021-10-01 08:52:04.043344 | 2021-10-01 08:52:04.043344 |
|  3 |       3 |   3 | hoge3 | 2021-10-01 08:52:04.051347 | 2021-10-01 08:52:04.051347 |
|  5 |       5 |   5 | hoge5 | 2021-10-01 08:52:04.069091 | 2021-10-01 08:52:04.069091 |
|  7 |       7 |   7 | hoge7 | 2021-10-01 08:52:04.076918 | 2021-10-01 08:52:04.076918 |
|  8 |       0 |   0 | NULL  | 2021-10-01 09:10:44.000000 | 2021-10-01 09:10:44.000000 |
+----+---------+-----+-------+----------------------------+----------------------------+
5 rows in set (0.00 sec)

mysql> update hoges set num = 8 where id = 8;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from hoges where id = 8;
Query OK, 1 row affected (0.01 sec)
```

UPDATE / INSERT / DELETE、すべて問題なく実行できました。
ちなみにpiyosをREADロックしているセッションでは、piyosへの更新だけではなく、他のテーブルの参照や更新もできません。
ロックしていないテーブルはREADもできないので、READロックを使う場合はREADしたいテーブルは全てロックする必要があります。

```
ERROR 1100 (HY000): Table 'hoges' was not locked with LOCK TABLES
mysql> insert into hoges (created_at, updated_at) values (NOW(), NOW());
ERROR 1100 (HY000): Table 'hoges' was not locked with LOCK TABLES
mysql> update hoges set num = 8 where id = 8;
ERROR 1100 (HY000): Table 'hoges' was not locked with LOCK TABLES
mysql> delete from hoges where  id = 8;
ERROR 1100 (HY000): Table 'hoges' was not locked with LOCK TABLES
```

### まとめ

READのテーブルロックは、自分を含む全てのトランザクションがロックしたテーブルに対して更新できなくなります。
特定のテーブルに対する全ての更新を禁止したいときに使えそうです。
ただ、書き込むわけでもないのにこのような状況にしたいパターンがあまり思いつかないので、明示的に使うことはほぼないと思います。

## [LOW_PRIORITY] WRITE ロック

続いてpiyosテーブルに対してWRITEロックを取得します。

```
mysql> LOCK TABLES piyos write;
Query OK, 0 rows affected (0.00 sec)

mysql> select OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, OWNER_THREAD_ID, OWNER_EVENT_ID from performance_schema.metadata_locks where OBJECT_SCHEMA = 'app_development' AND OBJECT_TYPE='TABLE';
+-------------+----------------------+-------------+-----------------+----------------+
| OBJECT_NAME | LOCK_TYPE            | LOCK_STATUS | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+----------------------+-------------+-----------------+----------------+
| piyos       | SHARED_NO_READ_WRITE | GRANTED     |              51 |            102 |
+-------------+----------------------+-------------+-----------------+----------------+
1 row in set (0.00 sec)
```

metadata_locksからpiyosテーブルに対してLOCK_TYPE=SHARED_NO_READ_WRITEのロックがかかっていることがわかります。

> このロックを保持しているセッションは、テーブルの読み取りおよび書き込みが可能です。

WRITEロックを取得したセッションから一通りの更新処理を試してみました。

```
mysql> insert into piyos (created_at, updated_at) values (NOW(), NOW());
Query OK, 1 row affected (0.01 sec)

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 |
|  8 |       0 |   0 | NULL  | 2021-10-01 09:18:58.000000 | 2021-10-01 09:18:58.000000 |
+----+---------+-----+-------+----------------------------+----------------------------+
5 rows in set (0.00 sec)

mysql> update piyos set num = 8 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)
```

UPDATE / INSERT / DELETE　全て成功しました。

> このロックを保持しているセッションだけがテーブルにアクセスできます。ロックが解放されるまで、ほかのどのセッションもアクセスできません。

他のセッションからCRUDを試してみました。

```
mysql> insert into piyos (created_at, updated_at) values (NOW(), NOW());
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> update piyos set num = 8 where id = 8;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> delete from piyos where id = 8;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> select * from piyos;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```

どの処理も失敗しました。READも失敗するのでかなり強いロックですね。

> WRITE ロックが保持されている間、テーブルに対するほかのセッションからのロック要求はブロックされます。

別のセッションからテーブルロックを取得してみます。

```
mysql> LOCK TABLES piyos write;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> LOCK TABLES piyos read;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```

READ / WRITE、どちらのロックも取得できませんでした。

なお、別セッションからはロックしていないテーブル(hoges)に対してはCRUDを実行することができます。

```
mysql> insert into hoges (created_at, updated_at) values (NOW(), NOW());
Query OK, 1 row affected (0.01 sec)

mysql> select * from hoges;
+----+---------+-----+-------+----------------------------+----------------------------+
| id | idx_num | num | name  | created_at                 | updated_at                 |
+----+---------+-----+-------+----------------------------+----------------------------+
|  1 |       1 |   1 | hoge1 | 2021-10-01 08:52:04.043344 | 2021-10-01 08:52:04.043344 |
|  3 |       3 |   3 | hoge3 | 2021-10-01 08:52:04.051347 | 2021-10-01 08:52:04.051347 |
|  5 |       5 |   5 | hoge5 | 2021-10-01 08:52:04.069091 | 2021-10-01 08:52:04.069091 |
|  7 |       7 |   7 | hoge7 | 2021-10-01 08:52:04.076918 | 2021-10-01 08:52:04.076918 |
|  9 |       0 |   0 | NULL  | 2021-10-01 09:24:56.000000 | 2021-10-01 09:24:56.000000 |
+----+---------+-----+-------+----------------------------+----------------------------+
5 rows in set (0.00 sec)

mysql> delete from hoges where id = 9;
Query OK, 1 row affected (0.01 sec)
```

READロックと同様でロックを取得しているセッションはロックしていないテーブルへアクセスすることはできません。

```
mysql> select * from hoges;
ERROR 1100 (HY000): Table 'hoges' was not locked with LOCK TABLES

mysql> insert into hoges (created_at, updated_at) values (NOW(), NOW());
ERROR 1100 (HY000): Table 'hoges' was not locked with LOCK TABLES
```

### まとめ

WRITEのテーブルロックは、自分以外のセッションからは更新だけではなく参照も禁止するかなり強いロックです。
テーブル全体のデータを厳密に扱いたい時に使うことができそうです。
ただ、厳密にデータを扱いたい場合でもテーブル全体ではなく、行単位で排他ロックすれば十分なことが多いため、WRITEロックも明示的に使うことはほぼないと思います。

## 行ロックがテーブルロックにエスカレーションされる？

「テーブルの○％以上のレコードを行ロックしたら行ロックからテーブルロックに変更される」という話を聞くことがありますが、MySQLではそのようなロックのエスカレーションは発生しないようです。
テーブル全域にロックかかっているんだけど？というときは、テーブルがロックされているわけではなく、ロック範囲を絞り込むことができずに全部の行がロックされている可能性が高いです。
全レコードがロックされるのでテーブルロックのように見えますが、これも行ロックです。

こちらの話はよく聞く勘違いなので覚えておく価値があると思います。
このことは下記ドキュメントに明記されています。

https://dev.mysql.com/doc/refman/8.0/ja/glossary.html#glos_lock_escalation

# 次回

テーブルレベルロックが確認できたので、次の記事では「行レベルロック: 共有ロック(S) / 排他ロック(X)」を検証していきます。

https://qiita.com/ham0215/items/b9efc718670b1d2d48c1