データベース エンジンにおける分離レベル
各トランザクションでは、別のトランザクションによって行われたリソースまたはデータの変更から特定のトランザクションを分離する際の程度を定義する分離レベルを指定します。分離レベルは、ダーティ リードやファントム読み取りなど、同時実行の副作用が許可されるのかという観点で定義されます。
トランザクション分離レベルでは次のことを制御しています。
- データの読み取り時にロックを獲得するかどうか、要求されるロックの種類。
- 読み取りロックの保持期間。
- 別のトランザクションによって変更された行を参照している読み取り操作で、次のことを行うかどうか。
- その行に対する排他ロックが解放されるまでブロックする。
- ステートメントまたはトランザクションの開始時に存在していた行の、コミット済みのバージョンを取得する。
- コミットされていないデータ変更を読み取る。
トランザクション分離レベルを選択しても、データ変更を保護するために獲得したロックは影響を受けません。トランザクションでは、設定されたトランザクション分離レベルに関係なく、常に、そのトランザクションで変更するデータについて排他ロックを獲得し、トランザクションが完了するまでそのロックを保持します。トランザクション分離レベルでは主に、読み取り操作に対して、他のトランザクションによって行われる変更の影響からの保護レベルを定義します。
分離レベルが低いほど多くのユーザーが同時にデータにアクセスできるようになりますが、ユーザーに影響が及ぶ可能性がある同時実行の副作用 (ダーティ リードや更新データの喪失) の種類が多くなります。反対に、分離レベルが高いほど、ユーザーに影響が及ぶ可能性がある同時実行の副作用の種類は減りますが、必要なシステム リソースが増加し、あるトランザクションによって別のトランザクションがブロックされる状況も多くなります。適切な分離レベルの選択は、アプリケーションのデータ整合性の要件と各分離レベルのオーバーヘッドとのバランスによって決まります。最も高い分離レベルの SERIALIZABLE は、トランザクションで読み取り操作が繰り返し実行されるたびに、そのトランザクションで完全に同じデータが取得されることを保証します。このことの実現には、マルチユーザー システムにおいて他のユーザーが影響を受ける可能性が高いロック レベルが適用されています。最も低い分離レベルは READ UNCOMMITTED ですが、このレベルでは、他のトランザクションによって変更され、まだコミットされていないデータを取得する場合があります。READ UNCOMMIITTED レベルでは同時実行のすべての副作用が発生する可能性がありますが、このレベルでは読み取りロックやバージョン管理が行われないのでオーバーヘッドが最小限に抑えられます。
データベース エンジンの分離レベル
SQL-99 標準では、次に示す分離レベルが定義されています。それらのすべてが Microsoft SQL Server データベース エンジンでサポートされます。
- READ UNCOMMITTED (物理的に破損したデータを読み取らないことのみが保証されるようにトランザクションを分離する、最も低い分離レベル)
- READ COMMITTED (データベース エンジンの既定レベル)
- REPEATABLE READ
- SERIALIZABLE (各トランザクションが完全に分離される最も高い分離レベル)
SQL Server 2005 では、行のバージョン管理を使用する 2 つのトランザクション分離レベルがサポートされます。1 つは、READ COMMITTED 分離の新しい実装であり、1 つは新しいトランザクション分離レベルである "スナップショット" です。
- READ_COMMITTED_SNAPSHOT データベース オプションが ON に設定されている場合、READ COMMITTED 分離では、行のバージョン管理を使用して読み取りの一貫性をステートメント レベルで維持します。読み取り操作にはテーブル レベルの SCH-S ロックだけが必要であり、ページ ロックや行ロックは不要です。READ_COMMITTED_SNAPSHOT データベース オプションが OFF に設定されている場合 (既定の設定です)、READ COMMITTED 分離の動作は以前のバージョンの SQL Server と同じになります。どちらの実装も READ COMMITTED 分離の ANSI 定義に準拠しています。
- スナップショット分離レベルでは、行のバージョン管理を使用して読み取りの一貫性をトランザクション レベルで維持します。読み取り操作では、ページ ロックも行ロックも獲得しません。テーブル レベルの SCH-S ロックだけを獲得します。別のトランザクションによって変更された行を読み取るときは、トランザクションの開始時に存在していた行のバージョンを取得します。スナップショット分離は、ALLOW_SNAPSHOT_ISOLATION データベース オプションを ON に設定すると有効になります。既定では、ユーザー データベースのこのオプションは OFF に設定されています。
次の表に、各分離レベルで許容されている同時実行の副作用を示します。
| 分離レベル | ダーティ リード | 反復不可能読み取り | ファントム |
|---|---|---|---|
READ UNCOMMITTED |
可 |
可 |
可 |
READ COMMITTED |
不可 |
可 |
可 |
REPEATABLE READ |
不可 |
不可 |
可 |
スナップショット |
不可 |
不可 |
不可 |
SERIALIZABLE |
不可 |
不可 |
不可 |
各トランザクション分離レベルによって制御される特定のロックまたは行のバージョン管理の種類の詳細については、「SET TRANSACTION ISOLATION LEVEL (Transact-SQL)」を参照してください。