MySQLのINSERT/UPDATE時におこる不整合対策

先日、作っているアプリケーションにバグが発生しました。バグの内容は次のようなものでした。

同時に存在してはいけないはずのデータが、DB に存在する
整合性のチェックはアプリケーションレベルで行っている
- 一意制約のような単純なものではないので、アプリケーションレベルで実装
整合性のチェックロジックは正しい

これに対し、バグは次のような状況で発生したと仮説を立てました。

ユーザがレコードを一括登録しようとする
登録ボタンを押したがレスポンスが遅い
- この間、整合性チェックが走っている
ユーザはもう一度登録ボタンを押した
- 2回目の登録の整合性チェックが走り始める
1回目の登録の整合性チェックが完了、INSERTが始まる
2回目の登録の整合性チェックが完了、INSERTが始まる
- 2回目の登録の整合性チェックの間、DBにはまだ1回目の登録によるINSERTが実行されていないので、チェックを通過した
結果、本来同時に存在してはいけないはずのデータがDB に登録されてしまった

この問題に対応するためには、ロック機構が必要になります。
(js でダブルポストできないようにするとかはあるけど、複数人のユーザが同時に登録を走らせる場合もあるかも。よって、今回js 云々の話はナシ)

僕自身、MySQLのロックの理解が浅かったので、少し調べることにしました。今回はその調査内容のまとめになります。
主な内容は、

一意制約の保証
それ以外の制約における整合性の保証
- update の場合
- 楽観的ロックと悲観的ロック
- MyISAM, InnoDBにおける対応方法の違い
- insert の場合
  - トランザクション分離レベルにおける対応方法の違い

です。

問題の定義

今回の問題を考えるにあたって、不整合が発生している例を3つ挙げます。
そして、それぞれの問題対する対応方法を示します。

1. 重複キー問題

学生は同じ部活には所属できないはずなのに、花道君がバスケ部とバスケ部に所属している > <
こういう状態。

student_id	name	student_id	club_id	club_id	name
1	花道	1	1	1	バスケ部
		1	1

2. ロストアップデート問題

よくトランザクションの説明に用いられる銀行取引の例。

エキスパートのためのMySQL「運用+管理」トラブルシューティングガイド(奥野幹也著) p.143 図2-4 より引用

BEGIN;
SELECT b INTO @x
FROM lost_upd
WHERE a = 1;
　　　　　　　　　　　　　　　　　BEGIN;
　　　　　　　　　　　　　　　　　SELECT b INTO @x
　　　　　　　　　　　　　　　　　FROM lost_upd WHERE a = 1;

# 処理に時間がかかる
　　　　　　　　　　　　　　　　　UPDATE lost_upd SET b = @x + 100
　　　　　　　　　　　　　　　　　WHERE a = 1;
　　　　　　　　　　　　　　　　　COMMIT;

UPDATE lost_upd SET b = @x + 10
WHERE a = 1;

COMMIT;

「b = @x + 100」の更新がなかったことになってしまう > <

3. ファントムリード問題

今回僕が遭遇した問題。少し改変した例として、会議室の予約システムを考えます。

1コマ2時間単位で予約可能
開始時間として指定できるのは日時のみ(分以下は指定不能)

BEGIN;
SELECT *
FROM reservations 
WHERE started_at BETWEEN '2011-01-23 13:00' AND '2011-01-23 15:00'
  AND room_id = 1;
　　　　　　　　　　　　　　　　　　　　　BEGIN;
　　　　　　　　　　　　　　　　　　　　　SELECT *
　　　　　　　　　　　　　　　　　　　　　FROM reservations 
　　　　　　　　　　　　　　　　　　　　　WHERE started_at = '2011:01:23 14:00' AND '2011:01:23 16:00'
　　　　　　　　　　　　　　　　　　　　　  AND room_id = 1;
　　　　　　　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　　　　　　　# => Empty set

　　　　　　　　　　　　　　　　　　　　　INSERT INTO reservations
　　　　　　　　　　　　　　　　　　　　　(room_id, started_at)
　　　　　　　　　　　　　　　　　　　　　VALUES (1, '2011:01:23 15:00');

　　　　　　　　　　　　　　　　　　　　　COMMIT;

# 処理に時間がかかる

# => Empty set

INSERT INTO reservations
(room_id, started_at)
VALUES (1, '2011:01:23 14:00');

COMMIT;

15時〜16時間が重複している予約が登録されてしまった > <

では、それぞれの問題への対方法を見ていきます。

1. 重複キー問題への対応

一意制約をつける
これによって重複登録が発生するとエラーする
MySQL レベルで保証してくれるので安心

CREATE UNIQUE INDEX clubs_students_index ON clubs_students (club_id, student_id);

2. ロストアップデート問題への対応

楽観的ロックまたは、悲観的ロックを使うことによって対応する。

楽観的ロックを使う
- Version パターン
- これによって、後発のUPDATEが失敗する
- 失敗した更新が正しく反映されるよう、再度後発のUPDATEを実行する

悲観的ロックを使う
- SELECTの代わりに、SELECT ... LOCK IN SHARE MODEを使う
- これによって

1. 1. 先発のUPDATEが待ち状態になる
  2. 後発のUPDATE時にデッドロックが発生、先発のUPDATE が実行される

- 楽観的ロックの場合と同様、失敗した後発のクエリを再度実行する

楽観的ロックと悲観的ロック

楽観的ロック

明示的なロックをかけない

更新したデータを行に書き戻す前に、その行を読み取った後に他の誰かがその行に変更を加えていないか確認する方法

Version パターンと呼ばれる

INSERTによって発生する不整合を防止することはできない

バージョン番号を表すカラムをテーブルに追加

SELECTした時のバージョン番号を記憶しておく

UPDATE時に、

バージョン番号が2で取得した番号と等しいかチェックする(UPDATE ... WHERE ... AND lock_version = 1;)

バージョン番号をインクリメントする

更新された行数が、

0だったらエラー。行を読んでから更新する前に他の誰かが行を更新したということ

1だったら更新成功

悲観的ロック

明示的にロックをかける

ロックが開放されるまで、ロックを保持していないユーザはデータを操作できない

場合によっては処理が長時間滞ってしまい、アプリケーションのパフォーマンスを著しく低下させる

MySQL では、LOCK TABLES、SELECT ... LOCK IN SHARE MODE, SELECT ... FOR UPDATE 等によって実現

MySQL の悲観的ロック

LOCK TABLES

テーブルロックを取得する

読み取りロック、書き込みロックどちらを取得するか選択可能

SELECT ... LOCK IN SHARE MODE

検索した行に読み取りロックをかける

別トランザクションから、ロックのかかった行を読み取ることができる

別トランザクションから、ロックのかかった行に書き込むことはできない

InnoDBがSELECT時に内部的にかけるインテンションロックとは別物

インテンションロックについては、奥野さんの本のp.255〜が詳しいです

SELECT ... FOR UPDATE

検索した行に書き込みロックをかける

別トランザクションから、ロックのかかった行を読み取ることはできない

別々のSELECT FOR UPDATEが同じ行を検索しようとした場合、後発のSELECTは、先発のトランザクションが完了するまで待ち状態になる

別トランザクションから、ロックのかかった行に書き込むことはできない

注意：MyISAM を使っている場合

MyISAM を使っている場合、悲観的ロックとしてSELECT ... LOCK IN SHARE MODE/FOR UPDATEを使うことはできない
- トランザクション機構がないため
楽観的ロックを使うか、LOCK TABLESでテーブル全体をロックする必要がある

LOCK TABLES accounts READ;

SELECT ...
UPDATE ...

UNLOCK TABLES;

3. ファントムリード問題への対応

悲観的ロックを使う
- SELECTの代わりに、SELECT ... LOCK IN SHARE MODEを使う
- これによって、

1. 1. 先発のINSERTが待ち状態になる
  2. 後発のINSERT時にデッドロックが発生、先発のINSERTが実行される

- 更新処理よって発生する不整合とは異なり、ファントムリード問題を楽観的ロックで防止することはできない
MyISAM を使っている場合は、LOCK TABLES を使う

注意：InnoDB のトランザクション分離レベルがrepeatable readより低い場合

SELECT ... LOCK IN SHARE MODEによってINSERTがブロックされるのは、ネクストキーロックという仕組みのおかげ
ネクストキーロックとは
- ~~ファントムリードを防止するための機構~~
- ファジーリードとファントムリードを防止するための機構(修正：sh2 さんよりツッコミをいただき修正)
- SELECTで検索したレコードが存在していなくても、そのレコードにロックをかけることができる(ネクストキーロックによるファントム問題の回避)
ネクストキーロックが有効になるのは、トランザクション分離レベルが、repeatable read以上の場合
トランザクション分離レベルがrepeatable readより低い場合、ファントムリード問題に対応するにはLOCK TABLESコマンドによって悲観的ロックをかける必要がある
- InnoDB のrepeatable read でファントムリード問題に対応できるのは、repeatable readはファジーリードだけ防げばよいが、そのための仕組みとしてネクストキーロックを用いているため(追記：sh2 さんよりツッコミをいただき追加)
  - 他のRDBMSを使う場合は分離レベルをserializable にする必要がある
  - ファジーリードについてはDBの「トランザクション分離レベル」が必要な理由　（PostgreSQLで，ファントム・リードを防止すべきサンプル事例） - 主に言語とシステム開発に関してを参照

注意：ネクストキーロックによるデッドロックの弊害(追記：sh2 さんよりツッコミをいただき追加)

このケースをSELECT ... LOCK IN SHARE MODE で対応した場合、13-15時＆16-18時の予約でもデッドロックを起こしてしまう
- ネクストキーロックは、ネクストのキーがないと13-∞時までロックしてしまうため
ネクストキーロックによるロックの挙動はMySQL InnoDBのネクストキーロックおさらい - SH2の日記を参照

まとめ

MySQLのINSERT/UPDATE時におこる不整合対策は、次のようになります。

ストレージエンジン	MyISAM	InnoDB
トランザクション分離レベル	-	repeatable read 未満	repeatable read	serializable
重複キー問題	一意制約	一意制約	一意制約	一意制約
ロストアップデート問題(UPDATE)	楽観的ロック / LOCK TABLES	楽観的ロック / LOCK TABLES	楽観的ロック / SELECT ... LOCK IN SHARE MODE	発生しない
ファントムリード問題(INSERT)	LOCK TABLES	LOCK TABLES	SELECT ... LOCK IN SHARE MODE	発生しない

LukeSilvia’s diary