search
LoginSignup
32

More than 1 year has passed since last update.

posted at

updated at

MySQLの行ロックを図解

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のレコードのみピンポイントでロックされます。
インデックスレコードをピンポイントでロックすることを「レコードロック」と呼びます。

スクリーンショット 2021-11-26 11.19.59.png

存在しないidを1つ指定した場合

存在しないレコードid:6を指定してロックを取得します。

select * from locks where id = 6 for update;

この場合、指定したid:6の前にあるid:5から後にあるid:8の範囲がギャップロックされます。
細かいですが、前方のid:5は含まれず、id:8は含まれます。
ギャップロックしている間は当該範囲にはレコードが挿入できなくなります。
前後のレコードが離れていればその分ロック範囲が広がっていくので注意しましょう。

スクリーンショット 2021-11-26 11.09.42.png

上記では前後にレコードがありましたが、もし前後にレコードが存在しない場合は上限いっぱいまでロックされてしまいます。

id:10を指定した場合、前にはid:9がありますが、後ろにはレコードがないため、id:9より後ろが全てギャップロックされます。

select * from locks where id = 10 for update;

スクリーンショット 2021-11-26 11.15.04.png

id:2を指定した場合、後ろにはid:3がありますが、前にはレコードがないため、id:3以前が全てギャップロックされます。

select * from locks where id = 2 for update;

スクリーンショット 2021-11-26 11.15.11.png

範囲を指定した場合

範囲でロックを取得します。

select * from locks where id between 3 and 8 for update;

範囲で取得した場合、範囲内に含まれるレコードは排他ロック(U)が取得され、レコードの間はギャップロックされます。

スクリーンショット 2021-11-26 11.23.40.png

上記では境界値にレコードがありますが、下記のSQLのようにレコードが存在しない場合もありえます。

select * from locks where id between 4 and 7 for update;

境界値にレコードが存在しない場合、下限id:4はそれより前のレコードid:3の直前までギャップロックを行います(id:3は含まれません)。
上限id:7は後ろのレコードid:8までギャップロックを行います(id:8も含みます)。

スクリーンショット 2021-11-26 11.29.11.png

もし、境界値のレコードがなく、その前後にもレコードがない場合は全範囲がギャップロックされます。

select * from locks where id between 2 and 10 for update;

スクリーンショット 2021-11-26 11.33.19.png

ユニークではないインデックスのレコードを指定するパターン

ユニークではないインデックスが設定されているnot_uniqueを指定してロックを取得します。

なお、この章は図が少し複雑になるので最初に見方を説明します。

スクリーンショット 2021-11-26 12.02.32.png

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より後ろはロックされていないため挿入することができます。

スクリーンショット 2021-11-26 11.56.24.png

存在しないnot_uniqueを1つ指定した場合

存在しないレコードnot_unique:107を指定してロックを取得します。

select * from locks where not_unique = 107 for update;

存在しないnot_uniqueを指定した場合、これまでの説明で出てきたギャップロックと同様で、指定したnot_uniqueの前後のレコードまでギャップロックを取得します。
図は省略しますが、前後のレコードが存在しない場合は下限上限いっぱいまでギャップロックされるのも同様です。

スクリーンショット 2021-11-26 12.26.57.png

範囲を指定した場合

範囲でロックを取得します。

select * from locks where not_unique between 103 and 108 for update;

範囲で取得した場合も考え方は同様です。
103も108もレコードは存在していますが、ユニーク制約がないため前後のレコードまでギャップロックされます。
103の場合は前にレコードがないため、103以下は全範囲がギャップロックされます。
108は後ろに109があるため、109(id:9)までギャップロックされます。

スクリーンショット 2021-11-26 12.35.10.png

インデックスがないレコードを指定した場合

最後にインデックスを指定していないnot_indexを指定してロックを取得します。

select * from locks where not_index = 205 for update;

インデックスを指定していないカラムを指定してロックした場合、問答無用で全レコードと全ギャップがロックされます。
絶対にやらないようにしましょう。

スクリーンショット 2021-11-26 11.40.49.png

参照

最後まで読んでいただきありがとうございます!
この記事では図解することを重視して細かい説明は省きましたが、以前にロック範囲に付いて検証した記事も公開しているので、もし興味がある方はこちらも見ていただけると嬉しいです!

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
32