MySQL とその互換 DB のロックの挙動を紹介する入門シリーズ、第３回は第２回で紹介したロックモニターを使って、業務ではよく使われているロック読取りについて解説します。
ロック読取りは、ゲームのバックエンドサーバーなど「同時に多数の処理をこなすけど、データとしての一貫性も重要」な場合に必須となるテクニックです。既に使っているという方も復習を兼ねてぜひご覧ください！
★ 第１回 トランザクション分離レベル
★ 第２回 ロックモニターの読み方
★ 第３回 ロック読取りも SELECT は止められない
★ 第４回 INSERT を止めるインテンションロック
★ 第５回 WHERE 条件と違うロック読取り
★ 第６回 performance_schema
★ 第７回ギャップロックと消えるロック
★ 第８回 ネクストキーロックと降順インデックス
★ 第９回 共有ロックでデッドロック
★ 第１０回 ロック読み取りは READ COMMITTED
★ 第１１回 Aurora と MySQL の違い
★ 第１２回 TiDB と MySQL の違い
第３回の解説動画
ロック読取りの必要性
第１回 トランザクション分離レベル で確かめたように、トランザクションを開始することで得られるデフォルトの 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 の前提で、ロック読取りの挙動を確かめます。以下の３行が格納されている 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 |
+-----+------+
この状態のまま２枚目 の端末で、オートコミットの１行クエリを使って、同じ 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;
ここでブロック
実際にどのようなロックがかけられているのか、第２回 ロックモニターの読み方 で解説したように 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 で使われているような単純な行ロックが使われていることがわかります。
次に不在の値に対してどのようなロックがかけられるのか実験します。１枚目の端末で一度 ROLLBACK でトランザクションを終了し、再度トランザクションを開始して存在しない k = 101 の行に排他ロックをかけます。
START TRANSACTION;
SELECT * FROM t1 WHERE k = 101 FOR UPDATE;
対象行が無い SELECT も Empty set という結果でクエリ自体は成功します。この状態で２枚目の端末で、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 はブロック出来ます。
共有ロックのロックモニター上の特別な取り扱い
今度は１枚目の端末でトランザクションを開始して 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)
不在値ですがギャップロックがかかっていますので２枚目の端末で k = 101 に INSERT をしてもブロックされます。
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
--------
このように ロックが存在しない かのような表示になっています！ここでさらに１枚目の端末のトランザクションの中で 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 を止められない
最後にロック読取りで重要なポイントをおさらいします。１枚目の端末でトランザクションを開始し、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 を止めることが出来ない
冒頭の方にありますライブ感のある解説動画の方もぜひご覧ください！
次回第４回は入門としては少しマニアックな内容ですが「INSERTを止めるインテンションロック」について解説いたします！
