SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

SQL Server への接続によって発行される Transact-SQL ステートメントの、ロックと行のバージョン管理に関する動作を制御します。

Transact-SQL 構文表記規則

構文

-- Syntax for SQL Server and Azure SQL Database
  
SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse
  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

引数

READ UNCOMMITTED
他のトランザクションで変更されたが、まだコミットされていない行を、ステートメントで読み取れるように指定します。

READ UNCOMMITTED レベルで実行されるトランザクションでは共有ロックが発行されないため、現在のトランザクションで読み取り中のデータが他のトランザクションで変更されることがあります。 また、READ UNCOMMITTED トランザクションは排他ロックによってブロックされないため、他のトランザクションで変更された後まだコミットされていない行を、現在のトランザクションで読み取ることができます。 このオプションが設定されている場合、ダーティ リードと呼ばれる、コミットされていない変更の読み取りが可能になります。 この場合、トランザクションが終了する前に、データの値が変更されたり、データセットの行が挿入または削除されたりする場合があります。 このオプションは、トランザクション内のすべての SELECT ステートメントで、すべてのテーブルに対して NOLOCK を設定するのと同じ効果があります。 これは分離レベルの中で最も制限の緩やかなオプションです。

SQL Server では、コミットされていないデータ変更のダーティ リードが行われないようトランザクションを保護しながら、ロックの競合を最小限にすることもできます。これには、次のいずれかを使用します。

• READ COMMITTED 分離レベル。READ_COMMITTED_SNAPSHOT データベース オプションを ON に設定します。

• SNAPSHOT 分離レベル。 スナップショット分離の詳細については、「SQL Server でのスナップショット分離」を参照してください。

READ COMMITTED
他のトランザクションで変更されたが、まだコミットされていないデータを、ステートメントで読み取れないように指定します。 これにより、ダーティ リードを防ぐことができます。 現在のトランザクション内にある各ステートメント間では、他のトランザクションによるデータの変更が可能です。この結果、反復不能読み取りやファントム データが発生することがあります。 このオプションは SQL Server の既定値です。

READ COMMITTED の動作は、READ_COMMITTED_SNAPSHOT データベース オプションの設定によって異なります。

• READ_COMMITTED_SNAPSHOT が OFF (SQL Server での既定値) に設定されている場合、データベース エンジンでは共有ロックが使用され、現在のトランザクションでの読み取り操作中に他のトランザクションによって行が変更されるのを防ぐことができます。 また、ステートメントが他のトランザクションで変更された行を読み取ろうとしても、そのトランザクションが完了するまでステートメントはブロックされます。 いつ解放されるかは、共有ロックの種類によって決まります。 行ロックは、次の行が処理される前に解放されます。 ページ ロックは次のページの読み取り時に解放され、テーブル ロックはステートメントの終了時に解放されます。

• READ_COMMITTED_SNAPSHOT が ON (Azure SQL Database での既定値) に設定されている場合、データベース エンジンでは行のバージョン管理が使用され、各ステートメントでは、トランザクション全体で一貫性のあるデータのスナップショットが使用されます。これは、ステートメント開始時点に存在したデータのスナップショットです。 ただし、ロックは、他のトランザクションがデータを更新するのを防ぐために使用されることはありません。

READ_COMMITTED_SNAPSHOT データベース オプションが ON の場合は、READ_COMMITTED 分離レベルで実行されるトランザクションの各ステートメントで行のバージョン管理を使用する代わりに、READCOMMITTEDLOCK テーブル ヒントを使用して共有ロックを要求することもできます。

REPEATABLE READ
他のトランザクションで変更されたがコミットされていないデータを、ステートメントで読み取れないように指定すると共に、現在のトランザクションが完了するまでは現在のトランザクションで読み取ったデータを他のトランザクションが変更できないように指定します。

トランザクションの各ステートメントで読み取ったすべてのデータには共有ロックが設定され、トランザクションが完了するまでその状態が保持されます。 これにより、現在のトランザクションで読み取った行は、他のトランザクションで変更できなくなります。 他のトランザクションでは、現在のトランザクションで実行されているステートメントの検索条件に一致する行を新しく挿入することができます。 その後、現在のトランザクションでステートメントが再試行された場合は新しい行が取得されるので、結果としてファントム読み取りが発生します。 共有ロックはステートメントが完了するたびに解除されるのではなく、トランザクションが完了するまで保持されるため、コンカレンシーは既定の READ COMMITTED 分離レベルよりも低くなります。 このオプションは、必要な場合にのみ使用してください。

SNAPSHOT
トランザクション内の任意のステートメントから読み取られるデータは、トランザクション開始時点に存在していたトランザクション的に一致するバージョンのデータであることを指定します。 データの変更は、トランザクションの開始前にコミットされたものだけが認識されます。 現在のトランザクションの開始後に、他のトランザクションによってデータが変更されても、現在のトランザクションを実行しているステートメントではデータの変更は認識されません。 これでは、トランザクションのステートメントが、コミットされたデータのトランザクションの開始時点でのスナップショットを取得する効果があります。

データベースが復旧中の場合を除いて、SNAPSHOT トランザクションではデータの読み取り時にロックが要求されません。 SNAPSHOT トランザクションでデータが読み取られている間も、他のトランザクションによるデータの書き込みはブロックされません。 トランザクションでデータが書き込まれている間も、SNAPSHOT トランザクションではデータを読み取ることができます。

データベースの復旧がロールバック段階のとき、別のトランザクションによってロールバックされているロックされているデータの読み取りが試行されると、SNAPSHOT トランザクションではロックが要求されます。 そのトランザクションのロールバックが完了するまで SNAPSHOT トランザクションはブロックされ、 許可が与えられるとすぐロックは解除されます。

SNAPSHOT 分離レベルを使用するトランザクションを開始する前には、ALLOW_SNAPSHOT_ISOLATION データベース オプションを ON に設定する必要があります。 SNAPSHOT 分離レベルを使用するトランザクションで複数のデータベースのデータにアクセスする場合は、各データベースで ALLOW_SNAPSHOT_ISOLATION を ON に設定する必要があります。

他の分離レベルで開始されたトランザクションに SNAPSHOT 分離レベルを設定できません。設定すると、トランザクションは中止されます。 トランザクションを SNAPSHOT 分離レベルで開始した場合は、他の分離レベルに変更して、その後 SNAPSHOT に戻すことができます。 トランザクションの開始は、初めてデータにアクセスした時点からになります。

SNAPSHOT 分離レベルで実行されているトランザクションでは、そのトランザクションで行った変更が認識されます。 たとえば、トランザクションでテーブルの UPDATE を実行した後、同じテーブルに対して SELECT ステートメントを実行した場合、結果セットには変更後のデータが含まれます。

SERIALIZABLE
次のことを指定します。

• 他のトランザクションで変更されたが、まだコミットされていないデータは、ステートメントで読み取れない。

• 現在のトランザクションが完了するまで、現在のトランザクションで読み取ったデータは他のトランザクションで変更できない。

• 現在のトランザクションが完了するまで、現在のトランザクションの任意のステートメントで読み取ったキー範囲に該当するキー値の行は、他のトランザクションで新しく挿入できない。

トランザクションで実行される各ステートメントの検索条件に一致するキー値の範囲には、範囲ロックが設定されます。 これにより、現在のトランザクションで実行されるステートメントの処理対象となる行はブロックされ、他のトランザクションによる行の更新や挿入ができなくなります。 つまり、トランザクションのステートメントが 2 度実行された場合は、2 度目も同じ行セットが読み取られます。 範囲ロックはトランザクションが完了するまで保持されます。 これではキー範囲全体がロックされ、トランザクションが完了するまでその状態が保持されるので、これは最も制限の厳しい分離レベルといえます。 このオプションはコンカレンシーが低いため、必要なときにのみ使用してください。 このオプションは、トランザクション内のすべての SELECT ステートメントで、すべてのテーブルに対して HOLDLOCK を設定するのと同じ効果があります。

解説

分離レベル オプションは一度に 1 つだけ設定でき、設定したオプションは明示的に変更されない限り、その接続で継続的に使用されます。 ステートメントの FROM 句内にあるテーブル ヒントで、テーブルに対して別のロック動作やバージョン管理動作が指定されない限り、トランザクションのすべての読み取り操作は、指定した分離レベルのルールに従って実行されます。

トランザクションの分離レベルでは、読み取り操作に対して取得されるロックの種類が定義されます。 READ COMMITTED または REPEATABLE READ 用に取得される共有ロックは通常、行ロックです。ただし、読み取り操作でページまたはテーブルの行が大量に参照される場合は、ページ ロックまたはテーブル ロックに変更される場合があります。 トランザクションで行の読み取り後に変更が行われる場合、そのトランザクションでは排他ロックによって行が保護され、トランザクションが完了するまでその状態が保持されます。 たとえば REPEATABLE READ トランザクションで、行に対する共有ロックが取得され、その行がトランザクションによって変更される場合、共有行ロックは排他行ロックに変わります。

1 つの例外を除き、分離レベルはトランザクションの実行中いつでも変更できます。 どの分離レベルでも、SNAPSHOT 分離レベルに変更した場合は例外が発生します。 このときトランザクションは失敗し、ロールバックされます。 SNAPSHOT 分離で開始したトランザクションを他の任意の分離レベルに変更することはできます。

トランザクションの分離レベルを変更した場合、変更後に読み取られるリソースは、新しいレベルのルールに従って保護されます。 変更前に読み取られたリソースは、引き続き以前のレベルのルールに従って保護されます。 たとえば、トランザクションが READ COMMITTED から SERIALIZABLE に変更された場合、変更後に取得される共有ロックは、トランザクションの終了まで保持されるようになります。

SET TRANSACTION ISOLATION LEVEL をストアド プロシージャまたはトリガーで実行した場合は、オブジェクトから制御が返されると、分離レベルはオブジェクトの呼び出し時に使用されていたレベルに再設定されます。 たとえば、バッチに REPEATABLE READ を設定し、このバッチからストアド プロシージャが呼び出されて分離レベルが SERIALIZABLE に設定された場合、ストアド プロシージャからバッチに制御が返されると、分離レベルの設定は REPEATABLE READ に戻ります。

sp_bindsession を使用して 2 つのセッションをバインドすると、各セッションではそれぞれの分離レベル設定が保持されます。 SET TRANSACTION ISOLATION LEVEL を使用してセッションの分離レベル設定を変更しても、そのセッションにバインドしている他のセッションの設定に影響はありません。

SET TRANSACTION ISOLATION LEVEL は、解析時ではなく実行時に有効になります。

ヒープに対して最適化された一括読み込み操作を行うと、次の分離レベルで実行されるクエリがブロックされます。

• SNAPSHOT

• READ UNCOMMITTED

• READ COMMITTED (行のバージョン管理を使用する場合)

一方、これらの分離レベルでクエリを実行すると、ヒープに対する最適化された一括読み込み操作がブロックされます。 一括読み込み操作の詳細については、「データの一括インポートと一括エクスポート (SQL Server)」を参照してください。

FILESTREAM が有効なデータベースでは、次のトランザクション分離レベルがサポートされています。

例

次の例では、セッションの TRANSACTION ISOLATION LEVEL を設定します。 後続の各 Transact-SQL ステートメントに対して、SQL Server ではトランザクションが完了するまですべての共有ロックが保持されます。

USE AdventureWorks2022;  
GO  
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
GO  
BEGIN TRANSACTION;  
GO  
SELECT *   
    FROM HumanResources.EmployeePayHistory;  
GO  
SELECT *   
    FROM HumanResources.Department;  
GO  
COMMIT TRANSACTION;  
GO  

参照

ALTER DATABASE (Transact-SQL)
DBCC USEROPTIONS (Transact-SQL)
SELECT (Transact-SQL)
SET ステートメント (Transact-SQL)
テーブル ヒント (Transact-SQL)