トランザクション分離レベルの設定 (Transact-SQL)

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric プレビューの SQL データベース

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

Transact-SQL 構文表記規則

構文

Microsoft Fabric プレビューでの SQL Server、Azure SQL Database、および SQL データベースの構文。

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

Azure Synapse Analytics と Parallel Data Warehouse の構文。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

引数

READ UNCOMMITTED

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

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

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

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

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

READ COMMITTED

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

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

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

• READ_COMMITTED_SNAPSHOTが ON に設定されている場合、データベース エンジンは行のバージョン管理を使用して、ステートメントの開始時に存在していたデータのトランザクション整合性スナップショットを各ステートメントに提示します。 ロックは、他のトランザクションによる更新からデータを保護するために使用されません。

  • READ_COMMITTED_SNAPSHOT ON は、Microsoft Fabric プレビューの Azure SQL Database と SQL データベースの既定値です。

スナップショット分離は、FILESTREAM データをサポートします。 スナップショット分離モードでは、トランザクション内のステートメントによって読み取られた FILESTREAM データは、トランザクションの開始時に存在していたデータのトランザクション整合性バージョンです。

READ_COMMITTED_SNAPSHOT データベース オプションがONされている場合は、READCOMMITTEDLOCK テーブル ヒントを使用して、READ COMMITTED分離レベルで実行されているトランザクション内の個々のステートメントの行のバージョン管理ではなく、共有ロックを要求できます。

反復可能な読み取り

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

トランザクションの各ステートメントで読み取ったすべてのデータには共有ロックが設定され、トランザクションが完了するまでその状態が保持されます。 これにより、他のトランザクションが現在のトランザクションによって読み取られた行を変更できなくなります。 他のトランザクションでは、現在のトランザクションで実行されているステートメントの検索条件に一致する行を新しく挿入することができます。 その後、現在のトランザクションがステートメントを再試行すると、新しい行が取得され、結果としてファントム読み取りが行われます。 共有ロックは各ステートメントの最後に解放されるのではなく、トランザクションの最後に保持されるため、コンカレンシーは既定の 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 回目に実行されると、同じ行セットが読み取られます。 範囲ロックはトランザクションが完了するまで保持されます。 これではキー範囲全体がロックされ、トランザクションが完了するまでその状態が保持されるので、これは最も制限の厳しい分離レベルといえます。 このオプションはコンカレンシーが低いため、必要なときにのみ使用してください。 このオプションは、トランザクション内のすべての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を使用して 1 つのセッションの分離レベルの設定を変更しても、そのセッションにバインドされている他のセッションの設定には影響しません。

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

関連コンテンツ

• データベースを変更 (Transact-SQL)
• DBCC USEROPTIONS (Transact-SQL)
• SELECT (Transact-SQL)
• SET ステートメント (Transact-SQL)
• テーブル ヒント (Transact-SQL)