SQL Server の ISOLATION LEVEL（トランザクション分離レベル）とは？

ここでは、SQL Server の ISOLATION LEVEL（トランザクション分離レベル）についてご説明します。

目次
  1. SQL Server の ISOLATION LEVEL とは？
    1. ISOLATION LEVEL - READ UNCOMMITTED とは？
    2. ISOLATION LEVEL - READ COMMITTED とは？
    3. ISOLATION LEVEL - REPEATABLE READ とは？
    4. ISOLATION LEVEL - SNAPSHOT とは？
    5. ISOLATION LEVEL - SERIALIZABLE とは？
  2. ISOLATION LEVEL の設定の方法

SQL Server の ISOLATION LEVEL とは？

SQL Server の ISOLATION LEVEL は、複数のトランザクションが同時に実行された時に、データを他のトランザクションからどの程度分離するかを定義したものです。

ISOLATION LEVEL が高くなればなるほど、データはロックされてデータの整合性などは保たれますが、その間他のトランザクションは処理を待たなければいけなくなるので、並行性が低下します。

反対に ISOLATION LEVEL を低くすると、同時にできる処理は増えますが、まだコミットされていないデータを他のトランザクションが読むなどして、データの整合性などに問題が出てくる場合があります。

ですので状況に応じて、適切な ISOLATION LEVEL を使い分ける必要があります。


SQL Server の ISOLATION LEVEL は次の 5 つがありますので順番にご紹介します。

SNAPSHOT 以外は、ANSI/ISO SQL 標準で定められている分離レベルで、上から下へ分離レベルが高くなっていきます。

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SNAPSHOT
  • SERIALIZABLE

ISOLATION LEVEL - READ UNCOMMITTED とは？

READ UNCOMMITTED は、他のトランザクションが変更してまだコミットしていないデータを読む (Dirty Read) ことができる ISOLATION LEVEL です。

5 つの中で、一番低い ISOLATION LEVEL です。

変更中のデータを取得しても問題がない時には、READ UNCOMMITTED を指定すると、他のトランザクションが終わるのを待たずにデータが取得できます。

SELECT 文のテーブルに NOLOCK のテーブルヒントをつけても READ UNCOMMITTED と同等の動きになります。


ISOLATION LEVEL - READ COMMITTED とは？

READ COMMITTED は、他のトランザクションが変更してまだコミットしていないデータは読めない ISOLATION LEVEL です。

ですので Dirty Read 問題は防げますが、他のトランザクションがデータを変更することができるため、トランザクション内で同じデータを繰り返し読み込むときに、値が変わってしまっている可能性があります。

この、READ COMMITTED のトランザクション分離レベルが、SQL Server のデフォルトです。


ISOLATION LEVEL - REPEATABLE READ とは？

REPEATABLE READ は、他のトランザクションが変更してまだコミットしていないデータを読むことができないようにし、かつ shared lock で他のトランザクションが現在のトランザクションが読んだデータを変更できないようにする ISOLATION LEVEL です。

READ COMMITTED と違って、このトランザクション内では同じデータは何度読み込んでも同じ値になります。

ただ、他のトランザクションが、このトランザクション内で取得したデータの検索条件に合致するようなデータを追加することができます。

ですので、同じトランザクション内で同じクエリーでデータを複数回取得した際に、同じデータが得られない場合があります。


ISOLATION LEVEL - SNAPSHOT とは？

SNAPSHOT は、SQL Server 特有のトランザクション分離レベルで、トランザクションが開始した時にコミット済みのデータのスナップショットをトランザクション内で使用する ISOLATION LEVEL です。

トランザクションが開始した後に、他のトランザクションが変更したデータは、このトランザクションには影響を与えません。

SNAPSHOT のトランザクション分離レベルを使うには、ALLOW_SNAPSHOT_ISOLATION のデータベースオプションを ON にする必要があります。

データがロックされないので並行性は高まりますが、その分リソースを使います。


ISOLATION LEVEL - SERIALIZABLE とは？

SERIALIZABLE は、一番高い ISOLATION LEVEL で、他のトランザクションはこのトランザクションが読んだデータを変更したり、このトランザクションが取得した検索条件に合致するようなデータを追加したりすることができません。

他のトランザクションが変更してコミットされていないようなデータを読むこともできません。

他のトランザクションの影響を受けずに、安全にデータを処理することができますが、その分並行性は低くなります。

同じテーブル、カラム、インデックスなどを使った SERIALIZABLE 分離レベルのトランザクションが複数同時に実行されていると、処理の順番によってはデッドロックも起こりやすくなります。


ISOLATION LEVEL の設定の方法

SQL Server で ISOLATION LEVEL を設定するには SET TRANSACTION ISOLATION LEVEL を使います。

SET TRANSACTION ISOLATION LEVEL トランザクション分離レベル;

設定できる [トランザクション分離レベル] は次のうちのどれかです。

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SNAPSHOT
  • SERIALIZABLE

例えば、SQL Server Profile で SQL Server Management Studio からの Audit Login のトレースの TextData を見てみると、TRANSACTION ISOLATION LEVEL を READ COMMITTED に設定していることがわかります。

-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

SQL Server の ISOLATION LEVEL（トランザクション分離レベル）1


ちなみに SSMS から実行するクエリのデフォルトの分離レベルを変更したい時は [ツール] > [オプション] メニューを開きます。

SQL Server の ISOLATION LEVEL（トランザクション分離レベル）2


[クエリ実行] > [SQL Server] > [Advanced] の SET TRANSACTION ISOLATION LEVEL で設定できます。

SQL Server の ISOLATION LEVEL（トランザクション分離レベル）3

変更した設定は、新しいコネクションから有効になります。


以上、SQL Server の ISOLATION LEVEL（トランザクション分離レベル）についてご説明しました。

© 2010-2024 SQL Server 入門