MySQL のロックについて解説する入門シリーズの第７回です。今回は挿入を止める以外に排他や共有のロックの機能もあると錯覚してしまいがちなギャップロックと INSERT に関するロック動作について解説します！
★ 第１回 トランザクション分離レベル
★ 第２回 ロックモニターの読み方
★ 第３回 ロック読取りも SELECT は止められない
★ 第４回 INSERT を止めるインテンションロック
★ 第５回 WHERE 条件と違うロック読取り
★ 第６回 performance_schema
★ 第７回ギャップロックと消えるロック
★ 第８回 ネクストキーロックと降順インデックス
★ 第９回 共有ロックでデッドロック
★ 第１０回 ロック読み取りは READ COMMITTED
★ 第１１回 AWS Aurora と MySQL の違い
★ 第１２回 PingCAP TiDB と MySQL の違い
ギャップロックとは
ギャップロックは MySQL InnoDB 特有のロック機構でデフォルトの REPEATALBE READ の分離レベルで使われます。存在する行と行の間、概念上の行間の隙間にかかるロックです。
ギャップロックはロックという名前は付いていますが排他や共有の機能は無く、「その場所への挿入をブロックする」という形でのみ働きます。
またギャップロックの多くは「昇順で前方のギャップロック + 行自体のレコードロック」である ネクストキーロック の一部としてセットになった形態で使われています。
排他ロック読取りでのギャップロック
k = 1,10,100 の３行が入った t1 テーブルでトランザクションを開始し、10 < k < 100 の範囲で排他ロックをかけます。
+-----+------+
| k | v |
+-----+------+
| 1 | 0 |
| 10 | 0 |
| 100 | 0 |
+-----+------+
[1]> start transaction;
Query OK, 0 rows affected (0.00 sec)
[1]> select * from t1 where k > 10 and k < 100 for update;
Empty set (0.00 sec)
隙間への排他ロックがどのように取得されているか 第６回で紹介 した performance_schema の data_locks テーブルへのビューで観察します。
+-------+------------+-----------+-----------+
| tx_id | index_name | lock_mode | lock_data |
+-------+------------+-----------+-----------+
| 16146 | PRIMARY | X,GAP | 100 | k = 100 行の前方ギャップ
+-------+------------+-----------+-----------+
ギャップに排他ロックがかかっています。このトランザクションが動いているままで２つ目の端末から k = 99 に INSERT してみます。
[2]> insert into t1 values (99,2);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
想定どおりブロックされました。次にギャップ部分ではない行部分はどうなるでしょうか？2つ目の端末で 全行 delete してみます。
[2]> delete from t1;
Query OK, 3 rows affected (0.01 sec)
[2]> select * from t1;
Empty set (0.00 sec)
全行 delete が問題なく終了しました。挿入を阻止する前方ギャップロックが k = 100 の行に存在していますが、k = 100 行自体の変更を阻止するロックが存在していないためです。
ところで前方ギャップロックは k= 100 の行にありましたがロックはどうなってしまったでしょうか？ performance_schema で確認します。
+-------+------------+-----------+------------------------+
| tx_id | index_name | lock_mode | lock_data |
+-------+------------+-----------+------------------------+
| 16146 | PRIMARY | X | supremum pseudo-record |
+-------+------------+-----------+------------------------+
もともと k = 100 行での前方ギャップロックだけが存在していましたが、いつのまにか supremum 行における排他のネクストキーロックに変更されていました！
このように取得済みのギャップロックは必要に応じて MySQL によって同じ効果を持つ別のロックにいつでも変更されます。
INSERT 時に取得されている排他ロック
最初の端末に戻り、同じトランザクションの中でさらに追加で k = 10 に INSERT してみます。
[1]> insert into t1 values (10, 2);
Query OK, 1 row affected (0.00 sec)
このトランザクションの中なので阻止するものは何もなく成功します。その後ギャップロックが１個あった状況がどうなったのか確認します。
+-------+------------+-----------+------------------------+
| tx_id | index_name | lock_mode | lock_data |
+-------+------------+-----------+------------------------+
| 16146 | PRIMARY | X,GAP | 10 | k = 10 行の前方ギャップ
| 16146 | PRIMARY | X | supremum pseudo-record | ∞ 行のネクストキーロック
+-------+------------+-----------+------------------------+
もともと supremum 行より前、つまり全範囲対象で１個のギャップロックがかかっていて、k = 10 に INSERT したことで、前の部分のギャップロックが自動で追加されました。一方 INSERT した k = 10 自体に対するロック が見当たりません。ロックが無いということは他の端末から INSERT 出来てしまうでしょうか？
[2]> insert into t1 values (10, 0);
ここでブロック
正しくブロックされました！ロックの状況を見ます。
+-------+------------+---------------+------------------------+
| tx_id | index_name | lock_mode | lock_data |
+-------+------------+---------------+------------------------+
| 16158 | PRIMARY | S,REC_NOT_GAP | 10 | <== ２つ目の端末でブロックされている INSERT
| 16146 | PRIMARY | X,GAP | 10 |
| 16146 | PRIMARY | X | supremum pseudo-record |
| 16146 | PRIMARY | X,REC_NOT_GAP | 10 | <== 持っていた筈の k = 10 の排他ロックが出現
+-------+------------+---------------+------------------------+
INSERT が衝突したことで見えていなかった持っている筈の k = 10 の排他ロックが出現しました！ UPDATE と同じで INSERT した変更点の排他ロックを持っていないのは不自然でしたので安心です。
performance_schema でも INSERT によって取得した排他ロックは他と衝突するまで表示されない ところは注意が必要です。
共有のロック読取りでのギャップロック
ここでトランザクションを一旦終了し、最初と同様 k = 1, 10, 100 の３行を用意し、あらためてトランザクションを開始して 10 < k < 100 のギャップに共有ロックをかけて performance_schema へのビューでロックを確認します。
[1]> start transaction;
Query OK, 0 rows affected (0.00 sec)
[1]> select * from t1 where k > 10 and k < 100 lock in share mode;
+-----------------+------------+-----------+-----------+
| tx_id | index_name | lock_mode | lock_data |
+-----------------+------------+-----------+-----------+
| 404824138226904 | PRIMARY | S,GAP | 100 |
+-----------------+------------+-----------+-----------+
表示はされていますが、トランザクションID が不自然に大きな数字となっています。このトランザクション内でさらに k = 1 に対する排他ロックを１行追加すると
[1]> select * from t1 where k = 1 for update;
+---+------+
| k | v |
+---+------+
| 1 | 0 |
+---+------+
1 row in set (0.00 sec)
+-------+------------+---------------+-----------+
| tx_id | index_name | lock_mode | lock_data |
+-------+------------+---------------+-----------+
| 11558 | PRIMARY | S,GAP | 100 | k = 100 行にかかっている前方ギャップロック
| 11558 | PRIMARY | X,REC_NOT_GAP | 1 | k = 1 行に追加でかけたレコードロック
+-------+------------+---------------+-----------+
両方のロックが同じトランザクションID 11558 で計上され最初の共有ギャップロック の ID が変化したことがわかります。
この挙動は INSERT 時の排他ロックと似ていて、共有ロックのみの場合、ID に不思議な数値が付与される ため注意が必要です。
まとめ
◉ ある行に前方ギャップロックだけがかかっている場合、その行自体の変更は許容され、さらに削除も出来て、ギャップロックは別形態に変化する
◉ INSERT 時の排他ロックは他と衝突するまで performance_schem の data_locks に登場しない
◉ 共有ロックだけの場合はトランザクションIDに特殊な数値が付与され、排他ロックが追加されると本来のトランザクションIDに変化する
今回はいずれも直感的に感じられるところと違う動作、ギャップロックをかけた行が削除出来たり、INSERT 時のあるはずの排他ロックが表示されなかったり、といったやや細かい動作について解説いたしました。
次回第８回は ORDER BY や降順インデックスによって同じ条件でもロックが変わるというところを解説します！あまり触れられていないテーマでもありますので是非お楽しみに！
