セージ の メモ書き

メモこそ命の恩人だ

DB - 分離レベル/リード現象

Isolation Level:分離レベル

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

  • 分離レベルは ACID特性 の"Isolation"のレベル。
  • 分離レベルは4段階ある。
    • SELECT の共有ロック方法を指定できる。
    • INSERT/UPDATE/DELETEの占有ロック方法は同じ。
  • "信頼性" と "速度" でトレードオフの関係
    • ロックの範囲大:信頼性Up、速度Down
    • ロックの範囲小:信頼性Down、速度Up
分離レベル ダーティリード ノンリピータブルリード
(ファジーリード)
ファントムリード
READ UNCOMMITTED 発生 発生 発生
READ COMMITTED 発生 発生
REPEATABLE READ 発生
SERIALIZABLE
分離レベル SELECT時の共有ロック ロック期間 ロック粒度
READ UNCOMMITTED ロックなし
READ COMMITTED ロックあり 読込時のみ 行ロック
REPEATABLE READ ロックあり Trの間 行ロック
SERIALIZABLE ロックあり Trの間 表ロック
分離レベル 特徴
READ UNCOMMITTED スループット良好
READ COMMITTED SQL Server の既定値
REPEATABLE READ 複数回、読込しても安心
SERIALIZABLE 直列可能性を保証


READ UNCOMMITTED

ダーティリードの発生理由

TrA が "READ UNCOMMITTED" で処理した場合

Step TrA TrB 内容
1   リソースXをUPDATE TrBによりXは占有ロック状態
2 リソースXをSELECT   共有ロックなしで読込
3   ロールバック TrAはダーティリード状態
  • Step2 の TrA による SELECT時、共有ロックを行わない。
  • 占有ロックされたリソースを読み取りする。
  • 結果、ロールバックされてダーティリード。


READ COMMITTED

ダーティリードの回避理由

TrA が "READ COMMITTED" で処理した場合

Step TrA TrB 内容
1   リソースXをUPDATE TrBによりXは占有ロック状態
2 リソースXをSELECT   共有ロックありで読込 => ロック発生
3   ロールバック TrAはロック解放後、リソースXを読込
  • Step2 の TrA による SELECT時、共有ロックを行う。
  • 占有ロック中のため、ロック待ちとなる。
  • 結果、ダーティリードを回避できる。

ノンリピータブルリードの発生理由

TrA が "READ COMMITTED" で処理した場合

Step TrA TrB 内容
1 リソースXをSELECT    
2   リソースXをUPDATE  
3   コミット  
4 リソースXをSELECT   ノンリピータブルリード状態
  • リソースXは、TrBのトランザクションにおいて、1回目と2回目で結果が異なる。
  • 結果、ノンリピータブルリード発生。
  • システムによっては大問題になることも。。。
  • 例:リソースXが"予約システムの座席情報"の場合、操作開始から予約確定時に指定座席を予約できない。


REPEATABLE READ

ノンリピータブルリードの回避理由

TrA が "REPEATABLE READ" で処理した場合

Step TrA TrB 内容
1 リソースXをSELECT   Tr完了まで共有ロック
2   リソースXをUPDATE リソースXはロック中のため待機
3 リソースXをSELECT   ノンリピータブルリード回避
4 Tr終了    
5   UPDATE実行  
  • TrAの完了まで、リソースXに共有ロックをかけるとこがポイント。
  • 複数回の読込をしても他のTrに変更されない。
  • ただし、ロックの期間が長くなるため、スループットは低下する。


ファントムリードの発生理由

TrA が "REPEATABLE READ" で処理した場合

Step TrA TrB 内容
1 リソースXをSELECT   Tr完了まで共有ロック
2   リソースYをINSERT "リソースX" と "リソースY" は同じキー情報
3 リソースXをSELECT   ファントムリード状態
  • TrA は存在しないリソースまでロックできない。
  • TrB は表に新たなレコードを追加できる。
  • TrA のリソース検索条件でリソースXとYの2つがヒットする可能性あり。
  • これが、ファントムリード。


SERIALIZABLE

ファントムリードの回避理由

TrA が "SERIALIZABLE" で処理した場合

Step TrA TrB 内容
1 リソースXをSELECT   Tr完了まで共有ロック+ロック粒度:表
2   リソースYをINSERT ロック待ち
3 リソースXをSELECT   ファントムリード回避
4 Tr終了    
5   INSERT実行  
  • TrAの完了まで、ロック粒度を表にするとこがポイント。
  • 同じ表に対して、他のTrは一切操作できない。
  • ただし、ロック範囲が広がり、スループットはさらに低下する。


デッドロックの多発

  • 下記の分離レベルで多発する。
    • REPEATABLE READ
    • SERIALIZABLE
  • 実運用では使用されない分離レベルとなる。
  • 実運用では下記の対策をとる。
    • 楽観的制御
    • 更新ロック
  • デッドロックの理由
    • Tr中、共有ロックを保持するため。
    • 同じリソースに対して、複数Trが共有ロックした時点で危うい。
    • そのリソースを占有ロックした時点でデッドロック状態。
Step TrA TrB 内容
1 リソースXをSELECT   リソースXは共有ロック状態
2   リソースXをSELECT リソースXは共有ロック状態
3 リソースXをUPDATE   占有ロックできず、解放待ち
4   リソースXをUPDATE 占有ロックできず、解放待ち
=>デッドロック状態



以上