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

mysql トラブル

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

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


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

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

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

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. 1コマ2時間単位で予約可能
  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. 先発のUPDATEが待ち状態になる
      2. 後発のUPDATE時にデッドロックが発生、先発のUPDATE が実行される
    • 楽観的ロックの場合と同様、失敗した後発のクエリを再度実行する
楽観的ロックと悲観的ロック
  • 楽観的ロック
    • 明示的なロックをかけない
    • 更新したデータを行に書き戻す前に、その行を読み取った後に他の誰かがその行に変更を加えていないか確認する方法
    • Version パターンと呼ばれる
    • INSERTによって発生する不整合を防止することはできない
      1. バージョン番号を表すカラムをテーブルに追加
      2. SELECTした時のバージョン番号を記憶しておく
      3. UPDATE時に、
      • バージョン番号が2で取得した番号と等しいかチェックする(UPDATE ... WHERE ... AND lock_version = 1;)
      • バージョン番号をインクリメントする
      1. 更新された行数が、
        • 0だったらエラー。行を読んでから更新する前に他の誰かが行を更新したということ
        • 1だったら更新成功
  • 悲観的ロック
    • 明示的にロックをかける
    • ロックが開放されるまで、ロックを保持していないユーザはデータを操作できない
    • 場合によっては処理が長時間滞ってしまい、アプリケーションのパフォーマンスを著しく低下させる
    • MySQL では、LOCK TABLES、SELECT ... LOCK IN SHARE MODE, SELECT ... FOR UPDATE 等によって実現
MySQL の悲観的ロック
  • LOCK TABLES
    • テーブルロックを取得する
    • 読み取りロック、書き込みロックどちらを取得するか選択可能
  • SELECT ... LOCK IN SHARE MODE
  • 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. 先発のINSERTが待ち状態になる
      2. 後発のINSERT時にデッドロックが発生、先発のINSERTが実行される
    • 更新処理よって発生する不整合とは異なり、ファントムリード問題を楽観的ロックで防止することはできない
  • MyISAM を使っている場合は、LOCK TABLES を使う
注意：InnoDB のトランザクション分離レベルがrepeatable readより低い場合
注意：ネクストキーロックによるデッドロックの弊害(追記：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 発生しない
参考
  1. エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド
  2. RailsによるアジャイルWebアプリケーション開発 第2版