Zenn
👌

SQLの[SELECT .. FOR UPDATE]文で、ダーティリードとデッドロックを防ぐ #RDBMS

2024/10/14に公開
SQL
RDB
RDBMS
ロック
tech

概要

RDBにおいて、テーブルから特定の行Aを読み取り、その値を基に同じ行Aを更新することを考える。
この時、一般的には以下のような動きになる。

  1. 読み取り処理
    2. SQL: SELECT文を発行して、特定の行を読み取る。
    3. ロック: 共有ロックが掛けられる。
  2. 更新処理
    5. SQL: UPDATE文を発行して、特定の行を更新する。
    6. ロック: 専有ロックが掛けられる。

※正確にはRDBMSの実装やトランザクション分離レベルに依る

ただ、読み取りの後に更新することが最初からわかっているなら、読み取り処理のときに専有ロックを掛けてもよい気がする。

その時に使うのが、FOR UPDATE句である。
SELECT .. FOR UPDATEのように用いることで、専有ロックを明示的に取得することができる。

本記事では、トランザクションの同時実行により発生するノンリピータブルリードとデッドロックの例を用いて、FOR UPDATE句の動作を考察する。

目的

何も考えずに読み取り処理、更新処理を順に行うとデッドロックが発生することがある。

実際、別の記事で考えた例では、共有ロックと専有ロックを段階に取得することでデッドロックが発生した。
できればデッドロックは発生してほしくないので、FOR UPDATE句を使用して防ぐことができないかを考察する。

※最後にも書いたが、FOR UPDATEでノンリピータブルリードやデッドロックを全て防げるわけではないので注意(今回の例は、たまたまFOR UPDATEが有効であっただけである)

別の記事で考えた例について

以下の記事に記載した例のことです。
※「(2)REPEATABLE READと2相ロック」節で、ノンリピータブルリードを防ぐためにトランザクション分離レベルをREPEATABLE READにしたところデッドロックが発生することになった。
https://zenn.dev/neko_student/articles/66cc065cf9f848

興味のある方はこちらの記事も参照ください。

対象読者

共有ロックと専有ロック・2相ロック等の基本的な知識は前提とします。
分かりやすい記事がたくさんあると思います。

FOR UPDATE句を考察する

まず初めに、ノンリピータブルリード・デッドロックが発生する例を見る。
※先ほど紹介したRDBにおける、共有ロック/占有ロック・2相ロック・トランザクション分離レベルの関係をシーケンスで説明すると同じ例です。

その後に、FOR UPDATEを使うことで、どのように改善ができるかを考察する。

ノンリピータブルリードが発生する例

トランザクション分離レベルはREAD COMMITEDとする。つまり、ロックは以下のように取得する：

  • 共有ロックをかける（読み取りが終わったら、すぐ解放してよい）
  • 2相ロックに従って、専有ロックをかける。

以下のトランザクションではノンリピータブルリードが発生してしまう。

  • Aさんの口座残高の初期値が500円
  • Aさんは300円を引き出したい
  • クレジットカード会社は300円を引き落としたい

no-repeatable-read

デッドロックが発生する例

ノンリピータブルリードを防ぐために、トランザクション分離レベルをREPEATABLE READにしてみる。つまり、ロックは以下のように取得する：

  • 2相ロックに従って、共有ロックと専有ロックをかける。

この時、同じ例を考えるとデッドロックが発生してしまう。

  • Aさんの口座残高の初期値が500円
  • Aさんは300円を引き出したい
  • クレジットカード会社は300円を引き落としたい

no-repeatable-read-lock

お互いが、お互いの共有ロック待ち状態になってしまった。

FOR UPDATE句を使う

2つの例で発生したノンリピータブルリードとデッドロックをどうすれば防げるかを考察する。

結論、読み取りの後に更新することがわかっているので、最初から専有ロックを掛けてしまえばよい。

同じ例において、読み取り処理時に専有ロックを取得する(FOR UPDATE指定する)ことを考える。

  • Aさんの口座残高の初期値が500円
  • Aさんは300円を引き出したい
  • クレジットカード会社は300円を引き落としたい

no-repeatable-read-for-update

クレジットカードの支払いができませんでしたが、整合性は取れました。

補足

今回の(FOR UPDATEを使った)例では、専有ロックしか掛けていない。
なので、このシーケンスにおいては、トランザクション分離レベルはREAD COMMITEDとREPEATABLE READのどちらでも同じ動作になる。

まとめ

今回の例では、FOR UPDATE句を使うことでノンリピータブルリードやデッドロックが防ぐことができた。

注意

専有ロックは強めのロックなので多用はできない。例えば、以下の点には十分に考慮したうえで使用する必要がある。

  • パフォーマンス：強めのロックなので他のトランザクションへの影響がある
  • デッドロック：FOR UPDATE≠デッドロックを防ぐ
  • ロック範囲：表ロックになる場合、多くのトランザクションへ影響がある
  • 他の手法で解決できないか：トランザクション分離レベル、楽観的ロック、MVCCなどなど

当然、FOR UPDATEでなんでも解決できるわけではないので注意。
※今回は都合のいい例を用いています。

参考文献

GitHubで編集を提案

Discussion