MySQL デッドロック回避パターン
目的
SELECT実行 ↓ (結果行が無ければ)INSERT (結果行が有れば)UPDATE
上記の様なパターンでクエリを発行しているシステムでDBのエラーが稀に出ていた。
解決過程の備忘録。
前提
MySQL 5.6 INNODB (REPEATABLE READ)
対象処理回数は約5〜10万回/日(秒間レベルで複数アクセスが発生している)
ユニークキーが存在する(=SELECTのWHERE条件)
最初の状態
コード(雰囲気)は以下のような感じ。
START TRANSACTION; SELECT 〜 (ユニークキーで検索) if(結果有る){ UPDATE 〜 } else { INSERT 〜 (SELECTで利用したユニークキーを挿入) } COMMIT;
1日に1,2回Duplicateエラーが発生していた。
対処1
エラーの原因として最初のSELECTが同時に走って後続の処理がユニーク制約に引っかかっている事が予想できた。
以下のように排他ロックを利用し対処した。
START TRANSACTION; SELECT 〜 FOR UPDATE (ユニークキーで検索) if(結果有る){ UPDATE 〜 } else { INSERT 〜 (SELECTで利用したユニークキーを挿入) } COMMIT;
ところが、今度は1日に数回デッドロックエラーが発生する様になった!
対処1の問題点
更に調査を進めると原因が以下である可能性が高い事がわかった。
SELECTが空振りするとギャップロックになる
ギャップロック同士は排他ロックされない
ギャップロックは別のトランザクションのINSERTをブロックする
同時に複数のギャップロックが発生するとお互いをブロックしデッドロック状態となる
対処2
以下の様に再対処を行った。
INSERT INTO 〜(ユニークキーをINSERT) ON DUPLICATE KEY UPDATE 〜(目的のUPDATE処理) *トランザクションは未使用に変更
問題のデッドロックエラーも出力されなくなり解決!
雑感
標準SQLの教科書通りの知識だけでは通用しない事が有る。(=経験大事!)
素直にDBMS依存の機能に頼った方が得策?
DUPLICATE KEY UPDATE動作時にプライマリキーがauto incrementされるので注意が必要。*AUTO_INCREMENT指定有り
SHOW ENGINE INNODB STATUSとか自由に使えない現場はツライ