MySQLでロックする際、範囲指定の方法や指定するカラムによってロック範囲が変わります。
この記事ではいくつかのパターンを図解してみたいと思います。
前提条件
最初に前提条件を記載します。
この記事でMySQLを実行する際、MySQLのバージョン8.0.27を利用しています。
実際にロックを取得するのはlocks テーブルを使います。
ロックを取得する方法は様々ありますが、for updateを使い排他ロック(U)を取得してロック範囲を検証します。
- テーブル定義
| Name | Type | Nullable | Extra Definition | Comment |
|---|---|---|---|---|
| id | bigint | false | auto_increment | インデックスあり、ユニーク |
| not_unique | bigint | false | インデックスあり、ユニークではない | |
| not_index | bigint | false | インデックスなし |
- データ
| id | not_unique | not_index |
|---|---|---|
| 3 | 103 | 203 |
| 5 | 105 | 205 |
| 8 | 108 | 208 |
| 9 | 109 | 209 |
ユニークインデックスのレコードを指定するパターン
ユニークインデックスが設定されているidを指定してロックを取得します。
存在するidを1つ指定した場合
レコードid:5を指定してロックを取得します。
select * from locks where id = 5 for update;
この場合、id:5のレコードのみピンポイントでロックされます。
インデックスレコードをピンポイントでロックすることを「レコードロック」と呼びます。
存在しないidを1つ指定した場合
存在しないレコードid:6を指定してロックを取得します。
select * from locks where id = 6 for update;
この場合、指定したid:6の前にあるid:5から後にあるid:8の範囲がギャップロックされます。
細かいですが、前方のid:5は含まれず、id:8は含まれます。
ギャップロックしている間は当該範囲にはレコードが挿入できなくなります。
前後のレコードが離れていればその分ロック範囲が広がっていくので注意しましょう。
上記では前後にレコードがありましたが、もし前後にレコードが存在しない場合は上限いっぱいまでロックされてしまいます。
id:10を指定した場合、前にはid:9がありますが、後ろにはレコードがないため、id:9より後ろが全てギャップロックされます。
select * from locks where id = 10 for update;
id:2を指定した場合、後ろにはid:3がありますが、前にはレコードがないため、id:3以前が全てギャップロックされます。
select * from locks where id = 2 for update;
範囲を指定した場合
範囲でロックを取得します。
select * from locks where id between 3 and 8 for update;
範囲で取得した場合、範囲内に含まれるレコードは排他ロック(U)が取得され、レコードの間はギャップロックされます。
上記では境界値にレコードがありますが、下記のSQLのようにレコードが存在しない場合もありえます。
select * from locks where id between 4 and 7 for update;
境界値にレコードが存在しない場合、下限id:4はそれより前のレコードid:3の直前までギャップロックを行います(id:3は含まれません)。
上限id:7は後ろのレコードid:8までギャップロックを行います(id:8も含みます)。
もし、境界値のレコードがなく、その前後にもレコードがない場合は全範囲がギャップロックされます。
select * from locks where id between 2 and 10 for update;
ユニークではないインデックスのレコードを指定するパターン
ユニークではないインデックスが設定されているnot_uniqueを指定してロックを取得します。
なお、この章は図が少し複雑になるので最初に見方を説明します。
not_uniqueに同値を入れることができるためnot_uniqueとidの組み合わせで表現しています。
★はnot_uniqueが105で、idが5より小さいレコードを示しています。not_unique:105, id:4などです。
▲はnot_uniqueが105で、idが5のレコードを示しています。
■はnot_uniqueが105で、idが5より大きいレコードを示しています。not_unique:105, id:6などです。
●はnot_uniqueが105と108の間のギャップを示しています。not_unique:107などです。
存在するnot_uniqueを1つ指定した場合
レコードnot_unique:105を指定してロックを取得します。
select * from locks where not_unique = 105 for update;
ユニーク制約の付いていないカラムには同値を入れることができるため、現在存在しているnot_unique:105(id:5)のレコードだけロックしてもnot_unique:105のレコードの挿入を防ぐことはできません。
そのため、前後がギャップロックされます。
ギャップロックのされ方はidを指定した場合と同様で、前方は前に存在するレコード(not_unique:103, id:3)の直前まで、後方は後ろに存在するレコード(not_unique:108, id:8)までの範囲がギャップロックされます。前後にレコードがない場合は下限上限までギャップロックされるのも同様です。
図を見ていただくとわかると思いますが、not_unique:103であっても、id:3の後方になるnot_unique:103、id:6などの組み合わせは挿入できませんが、前方になるnot_unique:103、id:2などの組み合わせは挿入できます。
not_unique:108も同様でidが8より前はギャップロックされていますが、8より後ろはロックされていないため挿入することができます。
存在しないnot_uniqueを1つ指定した場合
存在しないレコードnot_unique:107を指定してロックを取得します。
select * from locks where not_unique = 107 for update;
存在しないnot_uniqueを指定した場合、これまでの説明で出てきたギャップロックと同様で、指定したnot_uniqueの前後のレコードまでギャップロックを取得します。
図は省略しますが、前後のレコードが存在しない場合は下限上限いっぱいまでギャップロックされるのも同様です。
範囲を指定した場合
範囲でロックを取得します。
select * from locks where not_unique between 103 and 108 for update;
範囲で取得した場合も考え方は同様です。
103も108もレコードは存在していますが、ユニーク制約がないため前後のレコードまでギャップロックされます。
103の場合は前にレコードがないため、103以下は全範囲がギャップロックされます。
108は後ろに109があるため、109(id:9)までギャップロックされます。
インデックスがないレコードを指定した場合
最後にインデックスを指定していないnot_indexを指定してロックを取得します。
select * from locks where not_index = 205 for update;
インデックスを指定していないカラムを指定してロックした場合、問答無用で全レコードと全ギャップがロックされます。
絶対にやらないようにしましょう。
参照
最後まで読んでいただきありがとうございます！
この記事では図解することを重視して細かい説明は省きましたが、以前にロック範囲に付いて検証した記事も公開しているので、もし興味がある方はこちらも見ていただけると嬉しいです！