ねじろぐ @drillbits

MySQL + InnoDB で SELECT ... FOR UPDATE 使ったときのメモ

mysql

登場人物

どりるび：SIer時代はDBチームにDBを任せ、その後はゆるふわKVSを使いつづけることでRDBとの戦いを避けてきた。罪深い。

あらすじ

のっぴきならない事情で、一意制約がかけられないにも関わらずテーブルに同一のデータを存在させたくないってことがあった。

例示であり実際のコードや所属する団体とはアレです。

• user
  • カラムはid（AUTO_INCREMENTなプライマリキー）とscreen_nameとis_deleted
  • screen_nameの重複したデータは作りたくないが、例外としてis_deleted＝TrueなものはOK
• こんなかんじ

ID	screen_name	is_deleted
1	ibusem	False
2	d_osamu	True
3	d_osamu	True
4	d_osamu	False

で

これだとアプリケーションレベルで存在チェックをかけた後にインサートすることになるのだけど、ユーザーを登録する同じ内容のリクエストA・Bが同時にきたとき、

1. リクエストAで存在チェック
2. リクエストBで存在チェック
3. リクエストAがインサート
4. リクエストBがインサート

で同じスクリーンネームかつ退会フラグFalseのデータが2つできる。これを防止するために、存在チェックで SELECT ... FOR UPDATE というやつを使った。これで SELECT すると、対象の行は別トランザクションから読み取れなくなる（待ち状態になる）。

ただ

これって UPDATE じゃなくてインサートなんで、インサートのときは（存在チェックにかからなければ）どの行をロックするの？　というのを調べた。

1. セッションAで、制約のかかってないカラムで SELECT FOR UPDATE して INSERT

BEGIN;
SELECT * FROM user WHERE screen_name = 'ibusem' AND is_deleted = 0 FOR UPDATE;
# Empty set
INSERT INTO user VALUES ('ibusem', 0);

2. セッションBで、同じ条件で SELECT FOR UPDATE

BEGIN;
SELECT * FROM user WHERE screen_name = 'ibusem' AND is_deleted = 0 FOR UPDATE;
# この状態で待たされる

3. セッションAでコミット

COMMIT;

4. すると、セッションBで SELECT の結果が返ってくる

+----+-------------+------------+
| id | screen_name | is_deleted |
+----+-------------+------------+
|  1 | ibusem      |          0 |
+----+-------------+------------+
# 1 row in set

5. 2の状態で、違う条件でも SELECT FOR UPDATE してみる

BEGIN;
SELECT * FROM user WHERE screen_name = 'd_osamu' AND is_deleted = 0 FOR UPDATE;
# この場合も、同じようにこの状態で待たされる

6. 次に、セッションAで制約のかかっているカラムで SELECT FOR UPDATE

BEGIN;
SELECT * FROM user WHERE id = 1 FOR UPDATE;
# Empty set

7. セッションBで、同じ条件で SELECT FOR UPDATE

BEGIN;
SELECT * FROM user WHERE id = 1 FOR UPDATE;
# この状態で待たされる

8. また、カラムは同じだがセッションBとは違う値で SELECT FOR UPDATE

BEGIN;
SELECT * FROM user WHERE id = 2 FOR UPDATE;
+----+-------------+------------+
| id | screen_name | is_deleted |
+----+-------------+------------+
|  2 | d_osamu     |          0 |
+----+-------------+------------+
# 1 row in set
# 待たされずに結果が返る

9. また、条件を指定せずに SELECT FOR UPDATE

BEGIN;
SELECT * FROM user FOR UPDATE;
# この状態で待たされる

つまり

• 一意に特定できる条件を指定せずに SELECT FOR UPDATE した場合はすべての行をロックする
• 一意に特定できる条件を指定して SELECT FOR UPDATE したばあいは特定された行のみロックする

ということっぽい。

これはネクストキーロックという機構によるもの。ネクストキーロックは「SELECT FOR UPDATE したときには存在しないが、ロック対象の行に該当するであろうキー行に対しても自動的にロックをかける」ので、結果、一意に特定できる条件でないもので SELECT FOR UPDATE してしまった場合はすべての行がロック対象になるみたい…

なのでこれって下手したらテーブルロックと同等？　なのかニャー

Permalink | コメント(0) | トラックバック(0)