先日、アーキテクトの業務支援でトランザクション分離レベルについてメリデメを比較し、最終結論を導き出す機会があったので、内容を整理しておきます。
トランザクション
データベースを操作する複数の処理をひとまとめにしたもの。
複数の処理が存在する場合、処理によるテーブルデータの整合性を担保するための仕組み。
トランザクション分離レベル
データベースシステムにおけるトランザクションの同時実行性とデータの整合性を管理するための設定。トランザクション分離レベルの設定は、複数のトランザクションが同時に実行された場合に、データの読み取りと書き込みの間にどのような制御を行うかを定義する。
複数トランザクションの同時実行における問題点
複数のトランザクションが同時実行された場合、発生する可能性のある問題として、以下のものがあげられます。
1.ダーティーリード
「未コミット読み取り」「非コミット読み取り」とも呼ばれる。
あるトランザクション（A）がデータ更新処理をコミットする前に、他のトランザクション（B）が更新したデータ読み込んでしまうこと。
データを更新するトランザクション（A）がロールバックされた場合、対象データは更新前のデータに戻るため、他のトランザクション（B）が更新したデータを読み出してしまっている場合、不整合が生じる。
2.ファジーリード
「ノンリピータブルリード（反復不能読み取り）」とも呼ばれる。
あるトランザクション（A）内で、複数回同じデータを読み込む処理がある場合、トランザクション実行中に他のトランザクション（B）が対象データを書き換えると、書き換え前後でトランザクション（A）側で読み込まれるデータの内容が変わってしまう。
3.ファントムリード
「幻像読み取り」「仮読み取り」とも呼ばれる。
あるトランザクション（A）内で、複数回同じ条件でレコードを読み込む処理がある場合、トランザクション実行中に他のトランザクション（B）が条件に合致するレコードを挿入したり、削除した場合、トランザクション（B）の処理前後でトランザクション（A）側で取得されるレコード数が異なる。
トランザクション分離レベルの種類
SQLServerでは上記問題点を回避するレベルの種類は、大きく2つに分けられます。
1.ロック形式
トランザクションの要求があるたびに、そのトランザクションが依存する行、各種リソースにロックがかかる。
ロックをかけると、ロックを要求したトランザクションにとって問題になるようなリソースの変更が行われないように、他のトランザクションがブロックされ、各トランザクションは、ロックしたリソースに対する依存関係がなくなったときにロックを解放する。
2.行バージョン形式
変更されたそれぞれの行のバージョンがSQL Server データベース エンジンによって管理される。行のバージョン管理を使用することで、読み取り操作が原因で他のトランザクションがブロックされる可能性が大幅に減少する。
これらを踏まえ、SQLServerのトランザクション分離レベルについて、メリデメを順番に見ていきます。
1.READ UNCOMMITTED
分離レベルの中では、一番制限が緩いレベルとなる。
リソースの排他制御が緩くなり、読み取り待ち時間が少なくなるが、データの整合性は保てなくなる。
メリット：
・読み取り待ち時間が少なくなるので、高パフォーマンスが期待できる
・未コミットの変更を見ることができるため、最新データにアクセスできる
デメリット：
・ダーティーリードが発生する可能性がある。
・ノンリピータブルリードが発生する可能性がある。
・ファントムリードが発生する可能性がある。
2.READ COMMITTED
常にコミットされたデータのみを読み取ることが可能。
現在のトランザクションが変更していない行についてはロックがかからないので、他のトランザクションから変更される可能性がある。
※SQLServer2017のデフォルト設定
メリット：
・ダーティーリードが起こらない。
デメリット：
・ノンリピータブルリードが発生する可能性がある。
・ファントムリードが発生する可能性がある。
3.REPEATABLE READ
他のトランザクションで変更され、未コミットのデータ行を読み取れないようにしつつ、現在のトランザクションが完了するまでは、読み取ったデータはほかのトランザクションに変更されないようにしている。
メリット：
・ダーティーリードが起こらない。
・ノンリピータブルリードが起こらない。
デメリット：
・ファントムリードが発生する可能性がある。
4.SERIALIZABLE
トランザクションを直列化するため、完全に分離された状態を実現する。
メリット：
・データの整合性はもっとも保たれる。
デメリット：
・トランザクションが同時実行されず、順次実行されるため、同時性が低下しパフォーマンスが低下する可能性がある。
5.READ COMMITTED SNAPSHOT
行バージョン管理なのでロックが発生しない。
SQLのステートメント実行開始時時にスナップショットを生成してデータを提供する。
スナップショットからデータを取得してくるので同時実行性を損なわずに一貫性のあるデータを参照できる。
メリット：
・ロックエスカレーションがなくなる。
・高い平行性が実現でき、同時に多くの読み取りトランザクションが実行される場合に効果的。
・読み取りトランザクションと書き込みトランザクションが同時に行われてもデータの整合性が保たれる。
デメリット：
・スナップショットを作成するため、データベースのサイズが増える可能性がある。
・更新トランザクションが頻繁に行われる場合、スナップショットの作成と管理に関するオーバーヘッドが発生する可能性がある。
6.SNAPSHOT
行バージョン管理なのでロックが発生しない。
トランザクションの開始時点でデータのスナップショットを生成する。
スナップショットはほかのトランザクションの変更を反映せず、トランザクション内での一貫性を保つ。
メリット：
・ロックエスカレーションがなくなる
・ほかのトランザクションによる変更の影響を受けず、一貫したデータを読み取ることができる。
デメリット：
・スナップショットを作成するため、データベースのサイズが増える可能性がある。
・更新トランザクションが頻繁に行われる場合、スナップショットの作成と管理に関するオーバーヘッドが発生する可能性がある。
・スナップショットが大きい場合や、長時間にわたって保持される場合、トランザクションの実行速度が低下する可能性がある。
ちなみに、
5.READ COMMITTED SNAPSHOT
6.SNAPSHOT
を選択した場合、ロック方式が変更となるため、アプリケーション側での排他制御を考慮する必要がある。
さいごに
私は業務で要件定義や基本設計からプロジェクトに携わることは多々ありますが、アーキテクトの業務支援を行うことはけっこう稀で、普段やらないことをやったため、なかなか骨が折れました。
普段はアーキテクチャについてはある程度決まった段階で参画し、決まったアーキテクチャに沿って設計、製造を行うことが多いので、アーキテクチャを検討する側でプロジェクトに参画できたことは、とてもいい経験になりました。
また、実際の業務では、トランザクション分離レベル以外にも、データの照合順序、文字列データ型、日付データ型、タイムゾーン、共通カラム、排他制御についても検討し、本当に勉強になりました。またアーキテクト業務の支援機会があれば、ぜひやらせてもらいたいと考えています。
参考URL：https://techracho.bpsinc.jp/kotetsu75/2018_12_14/66410