LoginSignup
11
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

ZOZOAdvent Calendar 2024

Day 2
@ititoin株式会社ZOZO

SQL Serverのトランザクション分離レベルについて改めて整理する

Last updated at Posted at 2024-12-02

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

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

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

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

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

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のバージョン
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

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

READ COMMITTED (READ_COMMITTED_SNAPSHOT=OFF)

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

READ COMMITTED (READ_COMMITTED_SNAPSHOT=ON)

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

REPEATABLE READ

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

SERIALIZABLE

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

SNAPSHOT

  • 拡張イベントを確認した結果
    • image.png
    • ObjectID: 203147769 (_SampleTable)に対するSch-Sロックが一時的に取得されてすぐに解放されている
  • sys.dm_tran_locksを確認した結果
    • image.png
    • 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が発生したらリトライするなど、プログラム側で制御する必要があります。

11
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
11
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?