インフィニットループ 技術ブログ

2024年06月13日 (木)

著者 : nob

MySQL/Aurora/TiDBロック入門 – 第3回ロック読取りも SELECT は止められない【解説動画付】

MySQL とその互換 DB のロックの挙動を紹介する入門シリーズ、第3回は第2回で紹介したロックモニターを使って、業務ではよく使われているロック読取りについて解説します。

ロック読取りは、ゲームのバックエンドサーバーなど「同時に多数の処理をこなすけど、データとしての一貫性も重要」な場合に必須となるテクニックです。既に使っているという方も復習を兼ねてぜひご覧ください!

第1回 トランザクション分離レベル
第2回 ロックモニターの読み方
第3回 ロック読取りも SELECT は止められない
第4回 INSERT を止めるインテンションロック
★ 第5回 WHERE 条件と違うロック読取り
★ 第6回 performance_schema でロックを測定
★ 第7回 ギャップロックがあっても更新される
★ 第8回 ネクストキーロックと降順インデックス
★ 第9回 共有ロックで発生しやすいデッドロック
★ 第10回 ロック読み取りが READ COMMITTED になる詳細
★ 第11回 AWS Aurora と MySQL の違い
★ 第12回 PingCAP TiDB と MySQL の違い

第3回の解説動画

ロック読取りの必要性

第1回 トランザクション分離レベル で確かめたように、トランザクションを開始することで得られるデフォルトの REPEATABLE READ の分離レベルだけでは、「更新したときと同様に、特定の行に関しても自分側トランザクションが終わるまで他者の更新を禁止したい」というニーズを満たすのは難しいです。

そこで使われるのが ロック読取り (Locking Read) と呼ばれるロック指示語付の SELECT 文です。

トランザクション分離レベルに頼らずロック読取りを使うことで、必要な行で他のトランザクションをブロックします。ゲームのバックエンドと呼ばれるサーバーではこのロック読取りを使って互いに必要最小限なブロックをし合い、更新を含んだトランザクションであっても同時並行に処理します。

排他 (exclusive) ロックと共有 (share) ロック

★ 排他ロックをかけるクエリ:SELECT * FROM table WHERE key = value FOR UPDATE
★ 共有ロックをかけるクエリ:SELECT * FROM table WHERE key = value LOCK IN SHARE MODE

排他ロック共有ロック
排他ロック
共有ロック
排他と共有の可否関係表

ロック読取りの挙動を確かめる

MySQL のデフォルト REPEATABLE READ の前提で、ロック読取りの挙動を確かめます。以下の3行が格納されている t1 テーブルでトランザクションを開始し、k = 100 の行で FOR UPDATE により排他ロックをかけます。

+-----+------+
| k   | v    |
+-----+------+
|   1 |    0 |
|  10 |    0 |
| 100 |    0 |
+-----+------+

START TRANSACTION;
SELECT * FROM t1 WHERE k = 100 FOR UPDATE;

+-----+------+
| k   | v    |
+-----+------+
| 100 |    0 |
+-----+------+

この状態のまま2枚目 の端末で、オートコミットの1行クエリを使って、同じ k = 100 行に対して FOR UPDATE が付いた排他ロックの SELECT を実行するとブロックされます。LOCK IN SHARE MODE を付けた共有ロックでも同様にブロックされます。

SELECT * FROM t1 WHERE k = 100 FOR UPDATE;
ここでブロック

SELECT * FROM t1 WHERE k = 100 LOCK IN SHARE MODE;
ここでブロック

実際にどのようなロックがかけられているのか、第2回 ロックモニターの読み方 で解説したように SHOW ENGINE INNODB STATUS\G によるロックモニターで観測してみるとこのようになっています。

RECORD LOCKS space id 6 page no 4 n bits 80 index PRIMARY of table `learning_mysql`.`t1` trx id 4368 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 64
 0: len 4; hex 80000064; asc    d;;
 1: len 6; hex 000000000d27; asc      ';;
 2: len 7; hex 810000010e0110; asc        ;;
 3: len 4; hex 80000000; asc     ;;

k = 0x64 の行に対して排他ロックの単純な行ロック locks rec but not gap がかけられていることがわかります。InnoDB はギャップロックやネクストキーロックを特色としていますが、存在する値の行に対してかけられている ロック読取り (Locking Read) については READ COMMITTED で使われているような単純な行ロックが使われていることがわかります。

次に不在の値に対してどのようなロックがかけられるのか実験します。1枚目の端末で一度 ROLLBACK でトランザクションを終了し、再度トランザクションを開始して存在しない k = 101 の行に排他ロックをかけます。

START TRANSACTION;
SELECT * FROM t1 WHERE k = 101 FOR UPDATE;

対象行が無い SELECT も Empty set という結果でクエリ自体は成功します。この状態で2枚目の端末で、k = 101 の行に同じ排他ロックをかけると

mysql> SELECT * FROM t1 WHERE k = 101 FOR UPDATE;
Empty set (0.00 sec)

同様に Empty set ですがブロックされず通ってしまいました。ブロックされないなら INSERT も出来るでしょうか?

mysql> INSERT INTO t1 VALUES (101, 2);
ここでブロック

不在の値に排他ロックを掛けた場合、別のトランザクションからの同一値に対する排他ロックも成立してしまいますが INSERT はブロック されました。どのようなロックがかかっているのか詳細を見ていきましょう。

RECORD LOCKS space id 6 page no 4 n bits 80 index PRIMARY of table `learning_mysql`.`t1` trx id 4369 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

k = 100 はこのテーブルでは最大値ですので、ギャップに相当する不在の 101 に対してかけた排他ロックは、仮想の上限値の行 supremum に対しての排他のネクストキーロック lock_mode X としてかかっていることがわかります。

ネクストキーロックは前方のギャップロックと単純な行ロックの組み合わせでセットになっています。ギャップロックは排他や共有のロックとしては働かず INSERT をブロックするのみのロック として働きます。この例でも同じギャップに対してのロック読取りに作用することはありませんが、100 < k < supremum の範囲に対しての INSERT はブロック出来ます。

共有ロックのロックモニター上の特別な取り扱い

今度は1枚目の端末でトランザクションを開始して k = 101 の不在行に共用ロックをかけてみます。

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

mysql> SELECT * FROM t1 WHERE k = 101 LOCK IN SHARE MODE;
Empty set (0.00 sec)

不在値ですがギャップロックがかかっていますので2枚目の端末で k = 101INSERT をしてもブロックされます。

mysql> INSERT INTO t1 VALUES (101,2);
ここでブロック

INSERT がブロックされたことから、たしかにギャップロックは存在していますが、この状態でロックモニターを確認すると

mysql> SHOW ENGINE INNODB STATUS\G
****省略****
---TRANSACTION 418355921880712, not started
0 lock struct(s), heap size 1128, 0 row lock(s)

****ここのあるはずのロックの表示が無い****
--------
FILE I/O
--------

このように ロックが存在しない かのような表示になっています!ここでさらに1枚目の端末のトランザクションの中で k = 1 の行に FOR UPDATE で排他ロックをかけて確認すると

mysql> SELECT * FROM t1 WHERE k = 1 FOR UPDATE;
+---+------+
| k | v    |
+---+------+
| 1 |    0 |
+---+------+

****省略****

RECORD LOCKS space id 6 page no 4 n bits 72 index PRIMARY of table `learning_mysql`.`t1` trx id 10014 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

RECORD LOCKS space id 6 page no 4 n bits 72 index PRIMARY of table `learning_mysql`.`t1` trx id 10014 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 64
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000271b; asc     ' ;;
 2: len 7; hex 010000011a056f; asc       o;;
 3: len 4; hex 80000000; asc     ;;

下の方のロック表示は追加でかけた k = 1 に対しての排他の行ロック lock_mode X locs rec but not gap ですが、上の方は先程はなかった supremum 行に対しての共有のネクストキーロック lock mode S が突然出現しました!

トランザクション中に排他ロックが含まれず共有ロックだけだった場合、その共有ロックが他のトランザクションと衝突もしていない場合、これら「共有ロックが存在しているだけ」な場合はロックモニターは何も表示しない、という挙動になりますので注意が必要です。

ロック読取りは SELECT を止められない

最後にロック読取りで重要なポイントをおさらいします。1枚目の端末でトランザクションを開始し、k = 1 の行で FOR UPDATE のロック読取りで排他ロックをかけます。

[1]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

[1]> SELECT * FROM t1 WHERE k = 1 FOR UPDATE;
+---+------+
| k | v    |
+---+------+
| 1 |    0 |
+---+------+
1 row in set (0.00 sec)

2枚目の端末でトランザクションを開始し k = 1 を SELECT してみます。

[2]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

[2]> SELECT * FROM t1 WHERE k = 1;
+---+------+
| k | v    |
+---+------+
| 1 |    0 |
+---+------+
1 row in set (0.00 sec)

見れました! このように、排他や共有といったロック読取りはロック読取り同士が互いに排他し合うものであって、ロックに関して無指定の ただの SELECT に関してはブロックしたり処理を押し止めることは出来ません

書いてしまうと当たり前とも言えることかもしれないですが、トランザクションの中で複数のロック読取りを駆使している複雑な処理を記述している場合、FOR UPDATE もただの SELECT は止められない という基本は大事な事なので強調させていただきました!

まとめ

今回は実戦では必ず使われているロック読取り挙動の詳細をロックモニターを使って解説しました。

● 存在しない行へのロック読取りはギャップロックとなって INSERT だけを止める
● トランザクション内に共有ロックだけの場合はロックモニターに表示されない
● FOR UPDATE をつけた SELECT であっても、素の SELECT を止めることが出来ない

冒頭の方にありますライブ感のある解説動画の方もぜひご覧ください!

次回第4回は入門としては少しマニアックな内容ですが「INSERTを止めるインテンションロック」について解説いたします!

ブログ記事検索

このブログについて

このブログは、札幌市・仙台市の「株式会社インフィニットループ」が運営する技術ブログです。 お仕事で使えるITネタを社員たちが発信します!