MySQL とその互換 DB のロックの挙動を紹介する入門シリーズ、第４回はギャップロックとインテンションロックによって INSERT をブロックする仕組みについて解説します。
第１回 トランザクション分離レベル で解説したように、MySQL の特徴でもある REPEATABLE READ によるファントムリードの防止に関わっているところですので、ぜひお手元でも観察してみてください！
★ 第１回 トランザクション分離レベル
★ 第２回 ロックモニターの読み方
★ 第３回 ロック読取りも SELECT は止められない
★ 第４回 INSERT を止めるインテンションロック
★ 第５回 WHERE 条件と違うレコードロック
★ 第６回 performance_schema でロックを測定
★ 第７回 ギャップロックがあっても更新される
★ 第８回 ネクストキーロックと降順インデックス
★ 第９回 共有ロックで発生しやすいデッドロック
★ 第１０回 ロック読み取りが READ COMMITTED になる詳細
★ 第１１回 AWS Aurora と MySQL の違い
★ 第１２回 PingCAP TiDB と MySQL の違い
第４回の解説動画
インテンションロックとは
MySQL (InnoDB) のロックはテーブルと行という２つの粒度があり、ロックの方式も普通のロックのほかインテンションロックという仕組みがあり使い分けています。
インテンションロックは行単位のロックの前にテーブル単位で先行して取得される形で使われています。実戦ではテーブル単位でのロックは使うことはないですが、 インテンションロックのもう一つの使われ方として、INSERT に先行して行単位で取得しています。
INSERT をブロックしている仕組みは MySQL InnoDB の特徴的なところになりますので、その挙動を詳しく見てみます。
テーブル単位のインテンションロック
行単位の INSERT における挙動の前に、テーブル単位でのインテンションロックの挙動について見てみます。
テーブル単位の排他ロック (X) や共有ロック (S) は LOCK TABLES によって取得できます。また、行単位の FOR UPDATE や LOCK IN SHARE MODE で X や S のロックを取得しようとする場合も、先行してテーブル単位で排他や共有のインテンションロック、IX や IS を取得します。
テーブルまたは行という同一レベルで、通常のロックとインテンションロックにおいて競合する (☓) しない (○) かの関係性は以下の表になります
| X | IX | S | IS | |
| X | ☓ | ☓ | ☓ | ☓ |
| IX | ☓ | ○ | ☓ | ○ |
| S | ☓ | ☓ | ○ | ○ |
| IS | ☓ | ○ | ○ | ○ |
特徴的なところとしてはインテンションの排他ロック IX は、インテンションロックとは衝突しない、という点になります。
INSERT 時のインテンションロック
今回の本題になりますが、INSERT 時に先行して取得されるインテンションロックについて見て行きます。
k = 1, 10, 100 の３行が入っている t2 テーブルを使います。
+-----+------+
| k | v |
+-----+------+
| 1 | 1 |
| 10 | 10 |
| 100 | 100 |
+-----+------+
トランザクションを開始し、select * from t2 where k > 1 and k < 10 for update で 1 < k < 10 のギャップにロックをかけます。
---TRANSACTION 6438, ACTIVE 71 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 8, OS thread handle 137085495346880, query id 46 localhost root starting
show engine innodb status
TABLE LOCK table `learning_mysql`.`t2` trx id 6438 lock mode IX
RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table `learning_mysql`.`t2` trx id 6438 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000001918; asc ;;
2: len 7; hex 82000001180110; asc ;;
3: len 4; hex 8000000a; asc ;;
トランザクション 6438 で前方のギャップに排他ロック lock_mode X locks gap beofre rec がかけられていることがわかります。 この状態で別端末でトランザクションを開始し、insert into t2 values (5, 0); を実行します。 トランザクションはブロックされてハングアップ状態になりますが、その間にロックモニターを観察すると
RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table `learning_mysql`.`t2` trx id 6439 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000001918; asc ;;
2: len 7; hex 82000001180110; asc ;;
3: len 4; hex 8000000a; asc ;;
k = 5 に INSERT しようとしているトランザクションは、k = 10 の前方ギャップに対して挿入インテンションロック insert intention を取得しようとし、既にトランザクション 6438 で X が取得されており、X と IX は衝突するので待ち waiting になっていることがわかります。
挿入インテンション以外と衝突しないギャップロック
前述のトランザクション 6438 が 1 < k < 10 の排他ギャップロックをかけたまま、 別端末で同じ場所に対して排他のロック読み取りを実行するとどうなるでしょうか？
mysql> select * from t2 where k = 5 for update;
Empty set (0.00 sec)
ブロックされることはなくクエリは実行されました！ロックモニターで観察すると
---TRANSACTION 6440, ACTIVE 5 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 9, OS thread handle 137085494290112, query id 52 localhost root
TABLE LOCK table `learning_mysql`.`t2` trx id 6440 lock mode IX
RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table `learning_mysql`.`t2` trx id 6440 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000001918; asc ;;
2: len 7; hex 82000001180110; asc ;;
3: len 4; hex 8000000a; asc ;;
---TRANSACTION 6438, ACTIVE 846 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 8, OS thread handle 137085495346880, query id 53 localhost root starting
show engine innodb status
TABLE LOCK table `learning_mysql`.`t2` trx id 6438 lock mode IX
RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table `learning_mysql`.`t2` trx id 6438 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000001918; asc ;;
2: len 7; hex 82000001180110; asc ;;
3: len 4; hex 8000000a; asc ;;
6438 と 6440 という２つのトランザクションの両方で k = 10 の行の前方の排他ギャップロック lock_mode X locks gap before rec が存在していることがわかります。このようにギャップロックは異なるトランザクションでいくつでも取得出来るようになっています！
まとめ
ギャップロックはインテンションロックとのみ衝突し、 他のギャップロック要求とは作用しないため同じ場所のギャップにいくつでも取得可能になっています。FOR UPDATE をつけていても該当の値がなければそれはギャップへのロックとなり、 そのギャップロックは INSERT の阻止でしか作用しないため要注意です！
次回「第５回 実戦で役立つロック読取り」はロックをかける主な方法である ロック読取り の READ COMMITTED と REPEATABLE READ で異なってくる細かい挙動について詳しく解説します！
実戦で役立つ知識ですのでご期待ください！
