第５回は REPEATABLE READ と READ COMMITTED の分離レベルの違いによって変わったり、WHERE 条件で感じる直感的な範囲とは一致しない範囲でかかるなど、MySQL のロック読取りの挙動について解説します。
ロック読取りは実戦でよく使われている重要な手法で、細かい挙動も重要なポイントになります。動画と合わせて是非ご覧ください！
★ 第１回 トランザクション分離レベル
★ 第２回 ロックモニターの読み方
★ 第３回 ロック読取りも SELECT は止められない
★ 第４回 INSERT を止めるインテンションロック
★ 第５回 WHERE 条件と違うロック読取り
★ 第６回 performance_schema でロックを測定
★ 第７回 ギャップロックがあっても更新される
★ 第８回 ネクストキーロックと降順インデックス
★ 第９回 共有ロックで発生しやすいデッドロック
★ 第１０回 ロック読み取りが READ COMMITTED になる詳細
★ 第１１回 AWS Aurora と MySQL の違い
★ 第１２回 PingCAP TiDB と MySQL の違い
READ COMMITTED と REPEATABLE READ で異なるロック読取り
InnoDB が使う行単位のロックは主にギャップロックと特定行へのロック、その２つが組み合わさったネクストキーロックの３つで構成されています。
一般的には レコードロック という用語があります。広義ではテーブルではなく行にかかる意味でギャップロックなど全て包含して レコードロック (Record Lock) と呼ばれ、一方でギャップは含まず行だけにロックがあることを 狭義の意味でのレコードロックとしているケースがあります。
今回は レコードロック = 単純に行にかかっているだけ狭義のレコードロック とし、FOR UPDATE や LOCK IN SHARE MODE によってかけることが出来る ロック読み取り (Locking Read) が、READ COMMITTED と REPEATABLE READ でどのように違ってくるかを最初に見て行きます。
セッション内での後続トランザクションの分離レベルを READ COMMITTED にする
GLOBAL でセットすると間違いはないですが、クライアントの停止起動が必要になり不便なので、今回はセッション内で後続のトランザクションに対してのみ影響する SESSION 範囲で READ COMMITED を設定します。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED |
+-------------------------+
今回も使う t1 テーブルは以下の３行を持ちます
+-----+------+
| k | v |
+-----+------+
| 1 | 0 |
| 10 | 0 |
| 100 | 0 |
+-----+------+
指定した値の行がある場合のレコードロックの分離レベルによる違い
実戦の場では「他のトランザクションが Read するのは問題ないが、内容は変更して欲しくなく、新規の行を追加するのは問題ない」というケースがよくあります。
その意図を模倣して現在の最大値である k <= 100 の範囲で FOR UPDATE による排他のロック読取りを行います
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t1 WHERE k <= 100 FOR UPDATE;
+-----+------+
| k | v |
+-----+------+
| 1 | 0 |
| 10 | 0 |
| 100 | 0 |
+-----+------+
3 rows in set (0.00 sec)
ロックモニターで確認すると、k = 1, 10, 100 で排他の（単純な）レコードロック lock_mode X locks rec but not gap がかけられていることがわかります
TABLE LOCK table `learning_mysql`.`t1` trx id 5391 lock mode IX
RECORD LOCKS space id 6 page no 4 n bits 80 index PRIMARY of table `learning_mysql`.`t1` trx id 5391 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 64
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000000d2d; asc -;;
2: len 7; hex 020000011b06e6; asc ;;
3: len 4; hex 80000000; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 64
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000000f11; asc ;;
2: len 7; hex 01000001290366; asc ) f;;
3: len 4; hex 80000000; asc ;;
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 ;;
試しにこの状態で、同じく READ COMMITED を設定した２つ目の端末から k = 99 の行に INSERT してみます
mysql> INSERT INTO t1 VALUES (99,2);
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM t1;
+-----+------+
| k | v |
+-----+------+
| 1 | 0 |
| 10 | 0 |
| 99 | 2 |
| 100 | 0 |
+-----+------+
成功してしまいました！これが範囲指定でのブロックが出来ない、ファントムリードになってしまう （MySQL での）READ COMMITTED の特徴になります。
一旦トランザクションは終了し、SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ で分離レベルを REPEATABLE READ に変更します。
再度トランザクションを開始し、1つめの端末で k <= 100 でロックします。
mysql> SELECT * FROM t1 WHERE k <= 100 FOR UPDATE;
+-----+------+
| k | v |
+-----+------+
| 1 | 0 |
| 10 | 0 |
| 99 | 2 |
| 100 | 0 |
+-----+------+
4 rows in set (0.00 sec)
２枚目の端末で k = 98 に INSERT してみます
mysql> INSERT INTO t1 VALUES (98, 2);
ブロックされました
さらに k = 101 の行も
mysql> INSERT INTO t1 VALUES (101, 2);
これもブロック
REPEATABLE READ によって 10 と 99 の間の 98 の挿入がブロックされたのは期待通りですが、k <= 100 の範囲でしたが 101 の行の追加もブロックされました！ロックモニターで詳細を確認してみます。
RECORD LOCKS space id 6 page no 4 n bits 80 index PRIMARY of table `learning_mysql`.`t1` trx id 5394 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;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 64
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000000d2d; asc -;;
2: len 7; hex 020000011b06e6; asc ;;
3: len 4; hex 80000000; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 64
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000000f11; asc ;;
2: len 7; hex 01000001290366; asc ) f;;
3: len 4; hex 80000000; asc ;;
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 ;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 64
0: len 4; hex 80000063; asc c;;
1: len 6; hex 000000001510; asc ;;
2: len 7; hex 81000001080110; asc ;;
3: len 4; hex 80000002; asc ;;
かかっているロックは全て lock_mode X のネクストキーロックで、SELECT のスキャン方向における（この場合 ASC）前方のギャップへのロックが付属しています。ロックがかかっている行は
0x1
0xa
0x63
0x64
supremum
の５行です。supremum に 100 < k < ∞ の前方ギャップを含むネクストキーロックがかかっているため k = 101 への INSERT がブロックされたことがわかります。
WHERE 条件の外にもロックがかかる
前述の例で WHERE 条件で指定した k <= 100 の範囲外である筈の k > 100 にもロックがかかっていました。
k <= 100 の条件に対して、直感的には k = 100 のネクストキーロックを取得すれば前方ギャップも含むため事足りる様に感じます。実際には MySQL は REPEATABLE READ を維持するために、WHERE の条件範囲を超えた キーの後方のギャップ も取得します。
k < 100 の条件の場合は k = 100 で前方ギャップのみが 取得されそれが末尾となります。
このように条件式の不等号によってロックの数や範囲も変わってくるため注意が必要です。
まとめ
★ SELECT * FROM t1 WHERE k <= 100 FOR UPDATE など範囲指定のロックをかけた場合、READ COMMITTED は WHERE 条件式内の 隙間への INSERT をブロック出来ない。
★ REPEATABLE READ の場合は、条件に等号が入ってる場合、直感に反して条件式の範囲外のギャップロックも取得され、範囲外への INSERT をブロックする。
★ いずれの場合も WHERE 句の直感に沿った範囲のブロックにはなっていない ので注意が必要
以上細かい所ですが、ロック読取りは実戦で大変よく使われる技術ですので解説いたしました！
次回は範囲に対してのギャップロックがあっても更新や削除は止まらない件について解説いたしますのでご期待ください！
