こんにちは。今回は、いまさらながらSQL Serverのトランザクション分離レベルについて、整理しておきたいと思います。いつもさらっと読んでさらっと忘れてしまうので、備忘も兼ねて記事にまとめておこうという狙いです。
Contents
トランザクション分離レベルとは
この概念は、SQL Serverに限らず、データベース共通のものになりますが、以下のQiitaの記事に分かりやすくまとめられています。トランザクションに必要なACID特性のうち、I (Isolation, 分離性)を保証するための概念です。
ANSI/ISO標準で定められた分離レベルは、以下の４種類。いずれもトランザクションの整合性とパフォーマンスのトレードオフが存在します。
| 分離レベル | パフォーマンス | ダーティリード | ノンリピータブルリード | ファントムリード |
|---|---|---|---|---|
| SERIALIZABLE | 低 | 発生しない | 発生しない | 発生しない |
| REPEATABLE READ | 発生しない | 発生しない | 発生する | |
| READ COMMITTED | 発生しない | 発生する | 発生する | |
| READ UNCOMMITTED | 高 | 発生する | 発生する | 発生する |
SQL Serverにおけるトランザクション分離レベル
SQL Serverでは、READ COMMITTEDが規定値となっています。
SQL Server / SQL Database の行のバージョン管理の基本動作 at SE の雑記 (engineer-memo.com)
また、上記の４つの分離レベルに加えて、”SNAPSHOT ISOLATION”（スナップショット分離）というオプションが利用可能。
スナップショット分離は、SQL Server 2005に追加された分離レベルらしい。ある処理があるオブジェクトに対して排他ロックを取得している際、「ロック前のスナップショットを見せる」ことで、排他ロックにブロックされない読み取りを可能にする機能のようです。
この「ロック前のスナップショットを見せる」ことを実現するのが、行のバージョン管理と呼ばれる機能になります。行のバージョン管理では、トランザクション開始時に、その時点でのデータをTemp DBに保持するようになります。行のバージョン管理の動作詳細は以下。
SQL Server でのスナップショット分離 – ADO.NET | Microsoft Docs
Temp DBに書き込むオーバーヘッドがあるため、更新性能にはやや悪い影響を与えうるが、読み込み処理には良い影響を与えるトレードオフがあるとのこと。
なお、スナップショット分離は、以下の二つの方法で利用できます。
- 分離レベルをSNAPSHOT ISOLATIONに変更し、セッション毎に明示的にSNAPSHOT ISOLATIONレベルを指定する
- READ COMMITTED SNAPSHOT ISOLATIONオプションを有効化し、READ COMMITTED（SQL Serverのデフォルト）を使用しているすべてのセッションで規定で行のバージョン管理が行われるようにする
前者は以下コマンドで
ALTER DATABASE [TESTDB] SET ALLOW_SNAPSHOT_ISOLATION ON後者は以下コマンドで有効化できる。
ALTER DATABASE [TESTDB] SET READ_COMMITTED_SNAPSHOT ON後者の場合、トランザクションでスナップショット分離を利用するには、上記コマンドでの有効化に加えて、例えば以下のようにトランザクション開始時に明示的な指定が必要。
SQL Server でのスナップショット分離 – ADO.NET | Microsoft Docs
## ADO.NETの場合
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);まとめると以下のような利用オプションがあることになるか。
| 分離レベル | パフォーマンス | ダーティリード | ノンリピータブルリード | ファントムリード |
|---|---|---|---|---|
| SERIALIZABLE | 低 | 発生しない | 発生しない | 発生しない |
| SNAPSHOT | 発生しない | 発生しない | 発生しない | |
| REPEATABLE READ | 発生しない | 発生しない | 発生する | |
| READ COMMITTED (SNAPSHOT ISOLATION ON) | 発生しない | 発生する | 発生する | |
| READ COMMITTED (SNAPSHOT ISOLATION OFF) | 発生しない | 発生する | 発生する | |
| READ UNCOMMITTED | 高 | 発生する | 発生する | 発生する |
なお、SQL ServerのREAD COMMITTED SNAPSHOT ISOLATIONの動きが、OracleのREAD COMMITTEDと同等の動作になるようですね？
SQL Server で最低限設定が必要な内容 – Qiita
スナップショット分離 vs READ COMMITTEDスナップショット分離？
これは以下の記事が参考になりそう。
SQL Server / SQL Database の行のバージョン管理の基本動作 at SE の雑記 (engineer-memo.com)
SQL Serverのスナップショット分離レベル導入によるデータ基盤連携の課題解決 – ZOZO TECH BLOG
双方の記事で言及されていたのは、READ COMMITTEDスナップショット分離は、既存のトランザクションの動きが自動的に変わってしまうので、場合によっては変更の影響が大きいが、スナップショット分離であれば有効化した上で明示的にスナップショット分離の利用を宣言したトランザクションのみ影響を受けるので、影響を限定できるという点。
以上、簡単ですがSQL Serverにおけるトランザクション分離レベルの概要メモでした。
ご参考になりましたら幸いです。
おしまい
コメントを残す