Slow Dance RSSフィード

2011-01-23

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

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

  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_idname student_idclub_id club_idname
1花道 11 1バスケ部
11

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. 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時に、
      4. バージョン番号が2で取得した番号と等しいかチェックする(UPDATE ... WHERE ... AND lock_version = 1;)
      5. バージョン番号をインクリメントする
      6. 更新された行数が、
        • 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. 先発のINSERTが待ち状態になる
      2. 後発のINSERT時にデッドロックが発生、先発のINSERTが実行される
    • 更新処理よって発生する不整合とは異なり、ファントムリード問題を楽観的ロックで防止することはできない
  • MyISAM を使っている場合は、LOCK TABLES を使う

注意:InnoDB のトランザクション分離レベルがrepeatable readより低い場合
  • SELECT ... LOCK IN SHARE MODEによってINSERTがブロックされるのは、ネクストキーロックという仕組みのおかげ
  • ネクストキーロックとは
    • ファントムリードを防止するための機構
    • ファジーリードとファントムリードを防止するための機構(修正:sh2 さんよりツッコミをいただき修正)
    • SELECTで検索したレコードが存在していなくても、そのレコードにロックをかけることができる(ネクストキーロックによるファントム問題の回避)
  • ネクストキーロックが有効になるのは、トランザクション分離レベルが、repeatable read以上の場合
  • トランザクション分離レベルがrepeatable readより低い場合、ファントムリード問題に対応するにはLOCK TABLESコマンドによって悲観的ロックをかける必要がある
注意:ネクストキーロックによるデッドロックの弊害(追記:sh2 さんよりツッコミをいただき追加)
  • このケースをSELECT ... LOCK IN SHARE MODE で対応した場合、13-15時&16-18時の予約でもデッドロックを起こしてしまう
    • ネクストキーロックは、ネクストのキーがないと13-∞時までロックしてしまうため
  • ネクストキーロックによるロックの挙動はMySQL InnoDBのネクストキーロック おさらい - SH2の日記を参照

まとめ

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

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

参考
  1. エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド
  2. RailsによるアジャイルWebアプリケーション開発 第2版

sh2sh2 2011/01/24 02:04 ・ネクストキーロックはファジーリードとファントムリードを防ぐ仕組み
・REPEATABLE READはファジーリードだけ防げばよいが、そのための仕組みとして
 ネクストキーロックを用いているため、ついでにファントムリードも防いでしまう
と解釈してます。
他のRDBMSと互換性をとるなら、お作法はSERIALIZABLEかなと思います。

あと3番をLOCK TABLES以外で対策した版は、13-15時&16-18時の予約でも
デッドロックを起こしてしまうと思います。
ネクストキーロックは、ネクストのキーがないと13-∞時まで
ロックしてしまうという。これといって回避策はないのですが…。

LukeSilviaLukeSilvia 2011/01/26 15:19 id:sh2 さん

コメントありがとうございます!
ツッコミいただいた内容を反映させていただきました!

デッドロックの件は未知でした。sh2 さんがネクストキーロックの挙動について調査されていた記事、奥野さんの本を読んで勉強させていただきました!

本件社内Blog に書こうか迷ったのですが公開してよかったです。とても勉強になりました!

miyamiya 2011/12/31 14:10 ・検索した行に読み取りロックをかける
・別トランザクションから、ロックのかかった行を読み取ることができる

この2行は何を意味するんでしょうか。リードロックをかけたのに読める?

LukeSilviaLukeSilvia 2011/12/31 18:54 > miyaさん

コメントありがとうございます!

読み取りロックは、ロックをかけた範囲に対する書き込みを禁止しますが、読み込みを禁止しません。
読み取り操作に対してデータの整合性を保証する仕組みだからです(同じデータを複数のトランザクションから読み込んでもデータ自体は変わらない)。

miyamiya 2012/01/01 00:01 早速のレスありがとうございます。"LOCK IN SHARE MODE" (共有ロック)をかけると更新トランザクションが終わるまで更新できないが、参照はできる事はわかっているのですが、一般的に日本語の問題としてそれを"読み取りロック"と言うのか疑問に思いコメントさせていただきました。

"read lock" という単語から、読み込みを止める → 読み込み禁止 と連想してしまうのですが、業界では、書き込みのみのロックを "リードロック" と呼ぶのでしょうか?

LukeSilviaLukeSilvia 2012/01/02 19:29 > miyaさん
このブロクを書く際に、日本語でどう表現しようか迷ったのですが、その時は、以下のMySQLのマニュアル内の表現を参照したと思います。
http://dev.mysql.com/doc/refman/4.1/ja/internal-locking.html

最近のマニュアルを見ると、s lock, x lockの表現になっているので、日本語でも、共有/排他の方がよいのかもしれません。
http://dev.mysql.com/doc/refman/5.1-olh/ja/innodb-lock-modes.html

通りすがり通りすがり 2012/01/25 14:03 言葉についてです。
変なことを言っていたらすみません。

2. ロストアップデート問題への対応
→「悲観的ロックを使う」
→「2.後発のUPDATE時にデッドロックが発生、先発のUPDATE が実行される」

についてですが、この時発生するのは"デッドロック"ではなく"ロック待ち"ではないでしょうか?

"デッドロック"は、2つ(複数)のトランザクションでお互いがお互いをロック待ちしてしまい
立ち行きいかなくなってしまった状態の事を言うと思います。

"ロック待ち"は普段は普通に発生する正常な現象ですが、
"デッドロック"は基本的には発生してはいけない異常な現象と認識してます。
(タイムアウト設定や製品側のデッドロック検知機能等で復活するとは思いますが)

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/LukeSilvia/20110123/p1