LoginSignup
12
20

More than 1 year has passed since last update.

@ham0215

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

Last updated at Posted at 2021-10-18

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

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

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

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

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

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

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

ロックを確認する方法

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

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

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

テーブルロックは 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ではそのようなロックのエスカレーションは発生しないようです。
テーブル全域にロックかかっているんだけど？というときは、テーブルがロックされているわけではなく、ロック範囲を絞り込むことができずに全部の行がロックされている可能性が高いです。
全レコードがロックされるのでテーブルロックのように見えますが、これも行ロックです。

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

次回

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

12
20
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
12
20