RDB：トランザクションの分離レベルとロックの種類（主にSQL Server）

トランザクションの分離性に応じて生じる問題の種類

ANSI SQLで、並列トランザクションで起こりうる、正常ではない現象として挙げられているもの。

• ダーティーリード
  • トランザクションAでコミットされていない更新をトランザクションBで読み取ってしまう

• ファジーリード（ノンリピータブルリード）
  • トランザクションAから更新がコミットされることで、トランザクションBの中で読み取るレコードが変わってしまう

• ファントムリード
  • トランザクションAによってレコードが追加＆コミットされることで、トランザクションBの中での読み取り結果に新たなレコードが出現する
  • トランザクションAによってレコードが削除＆コミットされることで、トランザクションBの中での読み取り結果からレコードが消えてしまう

トランザクションの分離レベル

ANSI/ISO SQL標準による分離レベルの分類
※実現するための実装方法についての言及ではないことに注意

https://ja.wikipedia.org/wiki/トランザクション分離レベル

分離レベルが強い順に

• SERIALIZABLE：並列で実行されるどのトランザクションも、それらを逐次的に実行した場合と同じ結果になる
• REPEATABLE READ：トランザクション内で同じデータを何度読み直しても同じ結果になる
• READ COMMITTED：常にコミット済みのデータのみを読み取る
• READ UNCOMMITTED：コミット前のデータまで読み取る

分離レベルと生じる問題の関係性

○：生じる、×：生じない

※MySQL (InnoDB) では、REPEATABLE READ でもファントムリードが生じないらしい

楽観的ロックと悲観的ロック

排他制御をどのように実現するかという方法論の違い。

• 楽観的ロック
  • 同時更新（競合）の発生が低頻度の場合に向いている
    • あまり競合は起きないだろう、という意味で「楽観的」
  • リソースを更新するタイミングで、他のトランザクションによる更新が生じていないかどうかを確認し、生じている場合はロールバックする
    • 通常はVersion的なカラムを設け、自トランザクションの開始時にとった値と比較する
    • リソースの更新時にVersionをインクリメントする
  • トランザクションの分離レベルは通常 READ COMMITTED で運用する
    • 少なくとも REPEATABLE READ 以上とは両立しない（他のトランザクションによるコミットが読めないため）
  • Rails など、フレームワークレベルで楽観的ロックを実装しているものもある
• 悲観的ロック
  • 同時更新（競合）が高頻度で起き得る場合に向いている
  • トランザクションの開始時に、他のトランザクションから同リソースを読めないようにロックをかけてしまう
    • コミット時にロックを解放
  • 通常は、SELECT文で排他ロックをかける（SELECT ～ FOR UPDATE）ことにより実現する
  • SQL Server では、トランザクション分離レベルを REPEATABLE READ にした場合は、そのトランザクションから読んだ行に対して自動で共有ロックを（トランザクション解放まで）保持するようになる
    • SQL Server の ISOLATION LEVEL（トランザクション分離レベル）とは？
    • つまり、SQL Server では分離レベルを REPEATABLE READ にすることで悲観的ロックを実現できる
      • ※ただし厳密には、共有ロックなので他トランザクションからの読み取りはブロックされない
        • 読み取りも含めてブロックしたい場合はやはりSELECT文で明示的に排他ロックを取得する（ SELECT ～ WITH (XLOCK) ）
        • 共有ロックでも書き込みはブロックできるため、リソースの不整合は発生しないが、同じレコードに対する並列トランザクションでのアクセスによりデッドロックが発生する可能性があることに注意

SQL Server のロックの種類

• 共有ロック（Sロック）
  • 同じリソースに対する他トランザクションからの共有ロックの獲得は許可するが、排他ロック獲得はブロックする
  • デフォルトの分離レベル（READ COMMITTED）では、SELECT文で共有ロックが獲得され、クエリ実行直後に解放される
• 排他ロック（Xロック）
  • 同じリソースに対する共有ロックの獲得も排他ロックの獲得もブロックする
  • INSERT/UPDATE/DELETE文により排他ロックが獲得され、トランザクション完了時に解放される
• その他は下記ページに詳しい
  • SQL Serverのロックについて出来る限り分かりやすく解説