ギャップロック、ネクストキーロックを起こして挙動を確認してみた

SHIFT Group 技術ブログ

2022年8月19日 16:19

はじめに

こんにちは、SHIFT の開発部門に所属しているmurasawaです。
今期より中途で入社、バックエンド関連の開発を担当して行きます。

現在、研修でデータベースやRestAPIについて基本的な事から学んでいます。学んだことをアウトプットし理解を深めていくとともに技術の共有として役に立てば幸いです。

今回、ファントムリードを起こさない工夫として実装されているギャップロック、ネクストキーロックについて調べ実際に起こしてみて理解を深めました。

使用環境 mysql:5.7.35

コンソール上で操作し、現象をおこします。
コンソールを２つ立ち上げ、片方をトランザクションA、もう片方をトランザクションBとします。

今回使用するテーブル、データは以下になります。

+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)         | YES  |     | NULL    |                |
| score | smallint(5) unsigned | YES  |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> select * from users;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
| 10 | suzuki   |  1000 |
| 20 | kato     |   700 |
| 30 | yamamoto |  1200 |
| 40 | yamasita |  1300 |
+----+----------+-------+
4 rows in set (0.01 sec)

mysql>

ギャップロック

まず、ギャップロックを単体で起こしてみます。

トランザクションA

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where id=17 for update; 
Empty set

mysql>

と実カラムのないidで検索したとき、

他のトランザクションから INSERT（あるいは DELETE）ができないように、
実レコードが存在しないインデックスの隙間（ギャップ）をロックします。

試しにid=17に関連する実レコードが存在しないインデックスの隙間id(11～19)にデータを挿入しようとすると

トランザクションB

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into users value(11,"katuragi",500);
1205 - Lock wait timeout exceeded; try restarting transaction

mysql> insert into users value(19,"katuragi",500);
1205 - Lock wait timeout exceeded; try restarting transaction

mysql>

挿入できませんでしたが、ギャップの範囲外id=20のカラムを更新しようとすると

トランザクションB

mysql> update users set name="KATO" where id=20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql>

更新できました。

トランザクションAでの

トランザクションA

select * from users where id=17 for update;

は実レコードがなくEmpty setが返ってきましたが、ギャップロックがかかっています。

では試しにギャップを作っているid=20を削除を試みてみます。

トランザクションB

mysql> delete from users where id = 20;
Query OK, 1 row affected (0.00 sec)

ロックは何もないので削除できました。

ではid=20を削除した状態でcommitして再度トランザクションを開始しましょう。

トランザクションB

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

さてギャップ範囲外のid=21の行にデータを挿入してみましょう。

トランザクションB

mysql> insert into users value(21,"katuragi",500);
1205 - Lock wait timeout exceeded; try restarting transaction
mysql>

ロックがかかってしまいました…

先ほどのトランザクションBでの

トランザクションB

mysql> delete from users where id = 20;

をコミットしたことで、実レコードのあるインデックスがid=20からid=30に変更されました。

それによりギャップがid(11～19)から(11～29)に代わり、 id=21がギャップロック範囲内となってしまいました。

ネクストキーロック

以上のギャップを作っている実レコードが削除された際にギャップが広がってしまうのを防ぐためにネクストキーロックがあります。

先ほどのギャップロック+ギャップの次の実レコードが存在する行を行ロックしたものがネクストキーロックです。

今回は範囲指定で検索を行います。

トランザクションA

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where id between 13 and 17 for update;
Empty set

mysql>

もちろんギャップロックはかかっています。

トランザクションB

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into users value(11,"katuragi",500);
1205 - Lock wait timeout exceeded; try restarting transaction

mysql> insert into users value(19,"katuragi",500);
1205 - Lock wait timeout exceeded; try restarting transaction

mysql>

このギャップの次の実レコードid=20をアップデートしようとすると

トランザクションB

mysql> update users set name="KATO" where id=20;
1205 - Lock wait timeout exceeded; try restarting transaction

ギャップ内ではないはずなのにロック(行ロック)がかかっています。
この状態がネクストキーロックです。

先ほどと違い、ギャップを作っている実レコードに行ロックがかかっているので勝手にギャップロックの範囲が広がるのを防ぐことができます。

検索条件が実レコードを含む場合

例えば検索条件がwhere id between 15 and 20のように実レコードid=20を含む場合、

トランザクションA

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where id between 13 and 20 for update;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 20 | kato |   700 |
+----+------+-------+
1 row in set (0.02 sec)

mysql>

ネクストキーロックは次の実レコードまで広がります。

トランザクションB

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into users value(28,"katuragi",500);
1205 - Lock wait timeout exceeded; try restarting transaction
mysql>

ギャップロック、ネクストキーロックが指定範囲外まで広がる理由

mysql> select * from users where id = 17 for update;

や

mysql> select * from users where id between 13 and 17 for update;

でid(11～19)までロックがかかる理由としては、実レコード(インデックス)がない13~17をロックするよりも、実レコードを基準にロックしたほうが確実であると考えられるので、開発の際の都合だと考えられます。

あくまで推測なので、一意見として参考にしていただければと思います。

参考

MySQLのギャップロックとネクストキーロック

ネクストキーロックとは

終わりに

ギャップロックやネクストキーロックが言葉を見るだけでは理解が及ばず、実際に起こしてみました。

実際に動かすことで、問題点や範囲などがわかり実運用の際に注意することができると思います。

実際の挙動を確認することで、設計や実運用で役立つ知識となると思うのでお勧めです。

このブロガーのほかの記事を読む

＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿

執筆者プロフィール：Satoshi Murasawa
前社ではRPAツールの技術サポート、開発を1年半経験。 SHIFTでは、バックエンドエンジニアとして入社し、node.jsやmysqlに触れはじめた。
DBに漠然とした興味があり、勉強して部の中でDB関連で役割を持つことができたらよいなと思っています。

お問合せはお気軽に
https://service.shiftinc.jp/contact/

SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/

SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/

最後まで読んでくださり、ありがとうございます！

御社サービスのご相談はお気軽に。

ソフトウェア品質のプロがお伺いします。