データベースにおけるトランザクション分離レベルは重要ですが、それぞれの詳細な挙動をすぐに思い出すのは難しいことがあります。
本記事では実際に動かして挙動を確認しつつ各トランザクション分離レベルの特徴を整理してみました。

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

トランザクション分離レベルは、同時に複数のトランザクションが同じデータにアクセスする際に、そのトランザクションの間でどの程度のデータの整合性を保つかを制御するための機能です。

トランザクション分離レベルの設定方法

トランザクション分離レベルは、以下のコマンドで設定します。

SET TRANSACTION ISOLATION LEVEL <分離レベル>

例えば、READ COMMITTEDに設定する場合は以下のようになります。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

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

トランザクション分離レベルには以下の5つがあります。

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
SNAPSHOT

この中でSNAPSHOT分離レベルを使用するためには、まず対象のデータベースでSNAPSHOT分離のサポートを有効にする必要があります。
これはデータベース設定で行います。

ALTER DATABASE [データベース名] SET ALLOW_SNAPSHOT_ISOLATION ON;

SNAPSHOT分離レベルは、トランザクションが開始された時点のデータのスナップショットを使用して、他のトランザクションによる変更の影響を受けずにデータを読み取ることができます。
これにより、データの一貫性を保ちながら、同時実行性を向上させることができます。

READ_COMMITTED_SNAPSHOT について

データベースオプションのREAD_COMMITTED_SNAPSHOTを有効にすることで、READ COMMITTED分離レベルでもスナップショットを使用するように設定することができます。

ALTER DATABASE [データベース名] SET READ_COMMITTED_SNAPSHOT ON;

これにより、READ COMMITTED分離レベルのトランザクションが、共有ロックを取得せずにデータを読み取ることができます。

トランザクション分離レベルの挙動まとめ

上記5つのトランザクション分離レベルと、READ_COMMITTED_SNAPSHOTのON/OFFの違いでの各挙動をまとめると以下のようになります。

分離レベル	ダーティーリード	ノンリピータブルリード	ファントムリード	SELECT時のロックの種類・ロックの範囲	ロックの保持期間	他トランザクションとの更新競合によるエラーについて	備考
READ UNCOMMITTED	発生する	発生する	発生する	読み取り対象のテーブルに対してのSch-Sのみ	SELECT句の実行中	発生しない
READ COMMITTED (READ_COMMITTED_SNAPSHOT=OFF)	発生しない	発生する	発生する	読み取り対象のテーブルに対してのSch-S + 対象レコードのSロック	SELECT句の実行中	発生しない	SQL Serverのデフォルト
READ COMMITTED (READ_COMMITTED_SNAPSHOT=ON)	発生しない	発生する	発生する	読み取り対象のテーブルに対してのSch-Sのみ	SELECT句の実行中	発生しない	SQL Databaseのデフォルト
REPEATABLE READ	発生しない	発生しない	発生する	読み取り対象のテーブルに対してのSch-S + 対象レコードのSロック	トランザクション終了まで	発生しない
SERIALIZABLE	発生しない	発生しない	発生しない	読み取り対象のテーブルに対してのSch-S + 対象レコードのSロック + 存在しないレコードへのロックや範囲ロック	トランザクション終了まで	発生しない
SNAPSHOT	発生しない	発生しない	発生しない	読み取り対象のテーブルに対してのSch-S のみ	SELECT句の実行中	エラーコード 3960 や 3961のエラーが発生する可能性がある

ダーティリード・ノンリピータブルリード・ファントムリードについて
- ダーティリード・・・あるトランザクションが、まだコミット（確定）されていない他のトランザクションの変更を読み取ることを指す
- ノンリピータブルリード・・・同一トランザクション内で、同じデータを2回以上読み取った際に、そのデータが他のトランザクションによって変更されていることを指す
- ファントムリード・・・あるトランザクションがクエリを実行した結果、他のトランザクションがその間にデータを挿入、削除、変更しており、その結果として異なる行がクエリの結果に現れることを指す
ロックの種類・範囲について
- 状況によってはページロックやテーブルロックになることもある
- 行などの下位リソースにロックをかける際に上位リソースに対するインテント共有ロックも発生しうる
- Sch-S (スキーマ安定性ロック) は Sch-M(スキーマ修正ロック)としか競合しないSQL Serverにおける最小のロックモード
スナップショットを使用している場合、TempDBの負荷増加や、Skipped Ghosted Recordsの増加などがあり得る
- 参考:
  - SQL Serverのスナップショット分離レベル導入によるデータ基盤連携の課題解決
  - SQL Serverにおけるスナップショット分離レベルに関するトラブルシューティング

以降は上記表のそれぞれの挙動を実際に確認した記録となります。

それぞれのトランザクション分離レベルでの挙動の確認

実際にトランザクション分離レベルを変更しながらクエリを実行し、挙動を確認しました。

使用したSQL Serverのバージョン
Microsoft SQL Server 2016 (SP3-GDR) (KB5021129) - 13.0.6430.49 (X64) Jan 22 2023 17:38:22 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

ダーティリード・ノンリピータブルリード・ファントムリードの発生有無確認

READ UNCOMMITTED

セッションA	セッションB	説明
`SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 100が返ってくる		セッションAでのトランザクションの開始及び初期値の確認
	`BEGIN TRAN UPDATE [dbo].[_SampleTable] SET IntValue = 101 WHERE ID = 1`	セッションBのトランザクション内でレコードを更新
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 101が返ってくるダーティリードの発生		コミットされていない結果がセッションAから見えてしまう
	`ROLLBACK`
	`BEGIN TRAN UPDATE [dbo].[_SampleTable] SET IntValue = 102 WHERE ID = 1 COMMIT`	セッションBでレコードの更新をコミット
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 102が返ってくるノンリピータブルリードの発生		セッションAのトランザクション内ではレコードを更新していないのに値が変わってしまっている
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1000` -> 結果は無し		ID1000のレコードが存在しないことの確認
	`BEGIN TRAN INSERT INTO [dbo].[_SampleTable](ID, IntValue)VALUES(1000, 1100) COMMIT`
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 1100が返ってくるファントムリードの発生		別のトランザクションによるレコードの追加が見えてしまっている
	`BEGIN TRAN DELETE FROM [dbo].[_SampleTable] WHERE ID = 1000 COMMIT`	セッションBの後片付け
`ROLLBACK`		セッションAのトランザクション終了

READ COMMITTED (READ_COMMITTED_SNAPSHOT=OFF)

セッションA	セッションB	説明
`SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 100が返ってくる		セッションAでのトランザクションの開始及び初期値の確認
	`BEGIN TRAN UPDATE [dbo].[_SampleTable] SET IntValue = 101 WHERE ID = 1`	セッションBのトランザクション内でレコードを更新
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果はセッションBのトランザクションが閉じるまで返ってこない		セッションBで取得済みの排他ロックとセッションAで取得したい共有ロックが競合するため
セッションB側のROLLBACKと同時に結果100が返ってくる	`ROLLBACK`
	`BEGIN TRAN UPDATE [dbo].[_SampleTable] SET IntValue = 102 WHERE ID = 1 COMMIT`	セッションBでレコードの更新をコミット
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 102が返ってくるノンリピータブルリードの発生		セッションAのトランザクション内ではレコードを更新していないのに値が変わってしまっている
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1000` -> 結果は無し		ID1000のレコードが存在しないことの確認
	`BEGIN TRAN INSERT INTO [dbo].[_SampleTable](ID, IntValue)VALUES(1000, 1100) COMMIT`
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 1100が返ってくるファントムリードの発生		別のトランザクションによるレコードの追加が見えてしまっている
	`BEGIN TRAN DELETE FROM [dbo].[_SampleTable] WHERE ID = 1000 COMMIT`	セッションBの後片付け
`ROLLBACK`		セッションAのトランザクション終了

READ COMMITTED (READ_COMMITTED_SNAPSHOT=ON)

セッションA	セッションB	説明
`SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 100が返ってくる		セッションAでのトランザクションの開始及び初期値の確認
	`BEGIN TRAN UPDATE [dbo].[_SampleTable] SET IntValue = 101 WHERE ID = 1`	セッションBのトランザクション内でレコードを更新
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 100が返ってくる
	`ROLLBACK`
	`BEGIN TRAN UPDATE [dbo].[_SampleTable] SET IntValue = 102 WHERE ID = 1 COMMIT`	セッションBでレコードの更新をコミット
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 102が返ってくるノンリピータブルリードの発生		セッションAのトランザクション内ではレコードを更新していないのに値が変わってしまっている
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1000` -> 結果は無し		ID1000のレコードが存在しないことの確認
	`BEGIN TRAN INSERT INTO [dbo].[_SampleTable](ID, IntValue)VALUES(1000, 1100) COMMIT`
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 1100が返ってくるファントムリードの発生		別のトランザクションによるレコードの追加が見えてしまっている
	`BEGIN TRAN DELETE FROM [dbo].[_SampleTable] WHERE ID = 1000 COMMIT`	セッションBの後片付け
`ROLLBACK`		セッションAのトランザクション終了

REPEATABLE READ

セッションA	セッションB	説明
`SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 100が返ってくる		セッションAでのトランザクションの開始及び初期値の確認
	`BEGIN TRAN UPDATE [dbo].[_SampleTable] SET IntValue = 101 WHERE ID = 1` -> セッションAのトランザクションで共有ロックが取得されたままとなっているため、セッションAのトランザクションが終了するまで更新ができない	他トランザクションで更新できないためダーティリードもノンリピータブルリードも発生しない
	クエリの実行を中止して `ROLLBACK`
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1000` -> 結果は無し		ID1000のレコードが存在しないことの確認
	`BEGIN TRAN INSERT INTO [dbo].[_SampleTable](ID, IntValue)VALUES(1000, 1100) COMMIT`
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 1100が返ってくるファントムリードの発生		別のトランザクションによるレコードの追加が見えてしまっている
`ROLLBACK`	`BEGIN TRAN DELETE FROM [dbo].[_SampleTable] WHERE ID = 1000 COMMIT`	セッションBの後片付け、セッションAのトランザクション終了

SERIALIZABLE

セッションA	セッションB	説明
`SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 100が返ってくる		セッションAでのトランザクションの開始及び初期値の確認
	`BEGIN TRAN UPDATE [dbo].[_SampleTable] SET IntValue = 101 WHERE ID = 1` -> セッションAのトランザクションで共有ロックが取得されたままとなっているため、セッションAのトランザクションが終了するまで更新ができない	他トランザクションで更新できないためダーティリードもノンリピータブルリードも発生しない
	クエリの実行を中止して `ROLLBACK`
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1000` -> 結果は無し		ID1000のレコードが存在しないことの確認
	`BEGIN TRAN INSERT INTO [dbo].[_SampleTable](ID, IntValue)VALUES(1000, 1100)` -> セッションAのトランザクションでID=1000に対する共有ロックが取得されているため、セッションAのトランザクションが終了するまでINSERTができない	他トランザクションでレコード追加できないためファントムリードは発生しない
	クエリの実行を中止して `ROLLBACK`
`ROLLBACK`		セッションAのトランザクション終了

SNAPSHOT

セッションA	セッションB	説明
`SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 100が返ってくる		セッションAでのトランザクションの開始及び初期値の確認
	`BEGIN TRAN UPDATE [dbo].[_SampleTable] SET IntValue = 101 WHERE ID = 1`	セッションBのトランザクション内でレコードを更新
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 100が返ってくる
	`ROLLBACK`	ダーティリードは発生しない
	`BEGIN TRAN UPDATE [dbo].[_SampleTable] SET IntValue = 102 WHERE ID = 1 COMMIT`	セッションBでレコードの更新をコミット
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果 100が返ってくる		ノンリピータブルリードは発生しない
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1000` -> 結果は無し		ID1000のレコードが存在しないことの確認
	`BEGIN TRAN INSERT INTO [dbo].[_SampleTable](ID, IntValue)VALUES(1000, 1100) COMMIT`
`SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1` -> 結果はなし		ファントムリードは発生しない
	`BEGIN TRAN DELETE FROM [dbo].[_SampleTable] WHERE ID = 1000 COMMIT`	セッションBの後片付け
`ROLLBACK`		セッションAのトランザクション終了

SELECTした時のロックの種類・範囲・保持期間の確認

トランザクション分離レベルによって、SELECT時のロックの種類・範囲・保持期間は異なります。
それぞれのトランザクション分離レベルで下記クエリを実行した上で次の情報を確認し、ロック周りの挙動を確認します。

SET TRANSACTION ISOLATION LEVEL [トランザクション分離レベル]
BEGIN TRAN
SELECT IntValue FROM [dbo].[_SampleTable] WHERE ID = 1

拡張イベント
- ロックの取得や解放を記録して確認可能
sys.dm_tran_locks
- 現在アクティブなロック要求を確認可能

READ UNCOMMITTED

拡張イベントを確認した結果
- ObjectID: 203147769 (_SampleTable)に対するSch-Sロックが一時的に取得されてすぐに解放されている
- ObjectID 0 に対してのSch-Sロックも取得されている
  - これはSELECT句終了後も解放されず、トランザクション終了後に解放されている
  - ObjectID 0 はシステムオブジェクトであるため、詳細不明
    - 経験上問題になったことはないので基本的競合すること自体ないor少ないのではないかと思われる
sys.dm_tran_locksを確認した結果
- 上記 ObjectID 0 に対してのSch-Sロックのみ残存

READ COMMITTED (READ_COMMITTED_SNAPSHOT=OFF)

拡張イベントを確認した結果
- KEYに対しての共有ロックが取得され、その後解放されている
sys.dm_tran_locksを確認した結果
- READ UNCOMMITTEDの時と同様の状態

READ COMMITTED (READ_COMMITTED_SNAPSHOT=ON)

拡張イベントを確認した結果
- KEYに対しての共有ロックは発生せず、Sch-Sロックが取得され、解放されている
sys.dm_tran_locksを確認した結果
- READ UNCOMMITTEDの時と同様の状態

REPEATABLE READ

拡張イベントを確認した結果
- KEYに対しての共有ロックが取得され、解放されていない
sys.dm_tran_locksを確認した結果
- KEYの共有ロックが維持されている

SERIALIZABLE

拡張イベントを確認した結果
- KEYに対しての共有ロックが取得され、解放されていない
sys.dm_tran_locksを確認した結果
- KEYの共有ロックが維持されている
- SERIALIZABLE の場合、存在しないレコードに対してSELECTした場合もそのレコードに対する共有ロックが取得される
  - 実際に追加でSELECTした例

SNAPSHOT

拡張イベントを確認した結果
- ObjectID: 203147769 (_SampleTable)に対するSch-Sロックが一時的に取得されてすぐに解放されている
sys.dm_tran_locksを確認した結果
- READ UNCOMMITTEDの時と同様の状態

他のトランザクションとの更新時競合について

次のように他のトランザクションで更新されたレコードを更新するようなケースがあるとします。

セッションA	セッションB	説明
`SET TRANSACTION ISOLATION LEVEL SERIALIABLE BEGIN TRAN`		セッションAでのトランザクションの開始及び初期値の確認
	`BEGIN TRAN UPDATE [dbo].[_SampleTable] SET IntValue = 201 WHERE ID = 2 COMMIT`	セッションBのトランザクション内でレコードを更新
`UPDATE [dbo].[_SampleTable] SET IntValue = 202 WHERE ID = 2`		セッションBで更新されたレコードに対してセッションAで更新しようとする

READ UNCOMMITTED , READ COMMITTED , REPEATABLE READ , SERIALIZABLE の場合は更新が成功しますが、SNAPSHOT の場合は次のようなエラーが生じます。

メッセージ 3960、レベル 16、状態 3、行 31
更新の競合により、スナップショット分離トランザクションが中断しました。スナップショット分離を使用してデータベース 'sample_db' のテーブル 'dbo._SampleTable' に直接または間接的にアクセスし、別のトランザクションによって変更または削除された行を更新、削除、または挿入することはできません。トランザクションを再試行するか、更新/削除ステートメントの分離レベルを変更してください。

したがって、エラーコード3960が発生したらリトライするなど、プログラム側で制御する必要があります。