DBのデッドロック問題に遭遇した

仕事でWEBアプリケーションの開発に携わっております。

システム自体は私が携わっていない時代に作られたものなのですが、最近利用者数が増えてきて、DBのデッドロック問題が発生するようになりました。

発生箇所の特定

実際のエラーログを集計したところ、デッドロックが実際に発生するのは、AテーブルとBテーブルの2つに限られることがわかりました。
また、Aテーブルは利用者側、Bテーブルは管理画面側でのみ発生することがわかりました。

発生理由

調査した結果、デッドロックが起きる理由は、利用者側と管理画面側でテーブルの更新順序が異なることにあるようです。

利用者： Bテーブルのとあるレコードを更新 => Aテーブルのとあるレコードを更新
管理画面： Aテーブルのとあるレコードを更新 => Bテーブルのとあるレコードを更新

この更新したい「とあるレコード」が衝突した場合に限り、デッドロックが発生します。

改善方法

更新順序が逆であることによってデッドロックを引き起こすので、テーブルの更新順序を統一しました。

利用者： Aテーブル => Bテーブル (Bテーブル => Aテーブルから変更)
管理画面： Aテーブル => Bテーブル

川の流れを同一にすることによって、逆流によるデッドロックの解消をはかります。

SELECT FOR UPDATEの活用

更新順序がプロジェクト内で統一されていれば理想なのですが、仮にずれていてもデッドロックが発生しないよう、SELECT FOR UPDATEを使います。

利用者
- 利用者をロック (SELECT * FROM 利用者 WHERE id = 利用者ID FOR UPDATE)
- その後、Aテーブル => Bテーブルを更新
管理画面
- 利用者をロック (SELECT * FROM 利用者 WHERE id = 利用者ID FOR UPDATE)
- その後、Aテーブル => Bテーブルを更新

利用者レコードを使ってSELECT FOR UPDATEで排他制御することで、利用者と管理画面で同時実行されてデッドロックが発生する問題を防ぎます。なお、本件で利用者レコードを排他制御に使う理由は、更新対象の「Aテーブル」と「Bテーブル」が利用者IDを指定する関連テーブルだから、という点を付け加えておきます。