InnoDBのロックについて
はじめに
業務で複数テーブルのレコードを一括削除するバッチを作成しました。
その際、不必要にロックが掛かってしまうことを避けるために調査したので備忘録としてまとめます。
初期データ
生まれた年と月を管理するテーブルを作成します。
CREATE TABLE `birth_day` (
`year` INT NOT NULL,
`month` INT NOT NULL,
PRIMARY KEY(`year`),
KEY `idx_month` (`month`)
);
INSERT INTO `birth_day` (`year`, `month`) VALUES (1990, 3);
INSERT INTO `birth_day` (`year`, `month`) VALUES (2000, 6);
INSERT INTO `birth_day` (`year`, `month`) VALUES (2010, 9);
この記事で出てくるロックの種類について
- レコードロック(X)
- ギャップなしレコードロック(X,REC_NOT_GAP)
- ギャップロック(X,GAP)
- ネクストキーロック
本記事ではトランザクション分離レベルをREPEATABLE-READとします。
また、セカンダリインデックスレコードを省略してインデックスレコードと記載しています。
ロック範囲について
InnoDBではUNIQUE制約の無いインデックスレコードを取得する際、次のロックを取得します。
- 指定したインデックスレコードとその手前のギャップ
- 指定したインデックスレコードの次のレコード間のギャップ
レコードロック
1つ目のロックの挙動をレコードロックと呼びます。
以下のクエリを実行したときに、｢LOCK_MODE｣にXのみ表示されているときはレコードロックを意味します。
SELECT LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks;
/* 実行結果 */
+---------------+-----------+
| LOCK_MODE | LOCK_DATA |
+---------------+-----------+
| IX | NULL |
| X | 6, 2000 | /* ← レコードロック */
| X,REC_NOT_GAP | 2000 |
| X,GAP | 9, 2010 |
+---------------+-----------+
レコードロックの挙動
例えば、month = 6をレコードをロックする際、
対象のインデックスレコード6とその手前のギャップをロックします。
今回だと6の前のレコードは3なのでそのギャップ(4や5など)をロックします。
以下のクエリでロックの挙動を確認します。
START TRANSACTION;
SELECT * FROM birth_day WHERE month = 6 FOR UPDATE;
START TRANSACTION;
/* NG */
/* インデックスレコード6の追加や更新はできない */
INSERT INTO birth_day (year, month) VALUES (1980, 6);
UPDATE birth_day set month = 10 where month = 6;
/* インデックスレコード6の手前のレコード間のギャップに含まれるためINSERTできない */
INSERT INTO birth_day (year, month) VALUES (1980, 5);
このように、指定したレコードとその手前のギャップがロックされるのが「レコードロック」の挙動です。
以下のクエリでロックの状況を確認してみます。
SELECT LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks;
/* 実行結果(一部省略) */
+---------------+-----------+
| LOCK_MODE | LOCK_DATA |
+---------------+-----------+
| X | 6, 2000 |
| X,REC_NOT_GAP | 2000 |
+---------------+-----------+
month = 6のレコードの｢LOCK_MODE｣にXと表示されているのが確認できます。
これが指定したインデックスレコードとその手前のギャップをロックしていることを表しています。
また、X,REC_NOT_GAPと書かれた｢LOCK_MODE｣もありますが、ここではyear = 2000のクラスタインデックスレコードに対してギャップなしレコードロックを取得していることを表しています。
レコードロックは指定したレコードや手前のギャップから外れるとレコード追加ができます。
/* OK */
/* month = 3のレコードまでのギャップから外れているので追加ができる */
INSERT INTO birth_day (year, month) VALUES (1980, 3);
また、ロック範囲の判定にはクラスタインデックスの値も使われます。(今回だとPRIMARY KEYに指定しているyearカラム)
例えば、monthの値に3を指定してもmonth = 3のレコードのPRIMARY KEYの値である1990より大きい値をyearに指定した場合はギャップに含まれるため追加できません。
/* NG */
INSERT INTO birth_day (year, month) VALUES (1991, 3);
ギャップロック
2つ目のロックの挙動をギャップロックと呼びます。
以下のクエリを実行したときに、｢LOCK_MODE｣にX,GAPと表示されているときはギャップロックを意味します。
SELECT LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks;
/* 実行結果 */
+---------------+-----------+
| LOCK_MODE | LOCK_DATA |
+---------------+-----------+
| IX | NULL |
| X | 6, 2000 |
| X,REC_NOT_GAP | 2000 |
| X,GAP | 9, 2010 | /* ← これがギャップロック */
+---------------+-----------+
ギャップロックの例
month = 6のロックを取得した時点での次のインデックスレコードは9です。
ギャップロックの場合、9自体のロックは取得しませんがその手前のギャップをロックします。
(X,GAP = X - X,REC_NOT_GAPのイメージ)
START TRANSACTION;
SELECT * FROM birth_day WHERE month = 6 FOR UPDATE;
START TRANSACTION;
/* NG */
/* インデックスレコード9の手前のギャップに含まれるためINSERTできない */
INSERT INTO birth_day (year, month) VALUES (2020, 8);
こちらもレコードロックと同じく、ギャップから外れるとレコードの追加ができます。
クラスタインデックスがロック範囲の判定に使用されるのも同様です。
/* OK */
INSERT INTO birth_day (year, month) VALUES (2020, 9);
/* NG */
INSERT INTO birth_day (year, month) VALUES (2001, 9);
ネクストキーロック
これまでの例で、month = 6を指定した際に発生する2つのロックを整理すると以下の通りでした。
-
6のレコードロック -
9のギャップロック
この「レコードロック + ギャップロック」の組み合わせを「ネクストキーロック」と呼びます。
指定したインデックスレコード自体とその前後のギャップがロックされることに注意が必要です。
ロック範囲を減らすためにできること
不要なロック範囲を最小限に抑えるためには、以下の方法が有効です。
- クラスタインデックスで対象レコードを絞り込んで削除する
- セカンダリインデックスを使用する場合、可能であればUNIQUE制約を付与してから絞り込む
また、上記の対策をしても=やIN以外の演算子を使用してしまうと期待した通りのロック範囲にはなりません。
実際の対策
今回の業務では、複数テーブルを同一トランザクション内で削除する必要がありました。
そのため、あるテーブルの削除が完了しても他のテーブルの削除が完了するまでロックが解放されないという課題がありました。
UNIQUE制約を付与できないテーブルも存在したので、セカンダリインデックスからレコードを取得後に再度クラスタインデックスでレコードを取得し直してから削除することでロック範囲を減らしました。
参考文献
こちらの書籍ではロックについてのより詳しい挙動やその他の説明について詳しく書かれています。
まだ理解できていない部分があったり、今後必要になりそうな知識が載っていたりするので何度も読み返すことになりそうです。
Discussion