Memory-Optimized テーブルでのトランザクション分離レベルのガイドライン

2017-03-06

多くのシナリオでは、トランザクション分離レベルを指定する必要があります。メモリ最適化テーブルのトランザクション分離は、ディスクベースのテーブルとは異なります。

トランザクション分離レベルを指定するための要件:

TRANSACTION ISOLATION LEVEL は、ネイティブコンパイルストアドプロシージャの内容を構成する ATOMIC ブロックに必要なオプションです。
コンテナー間トランザクションでの分離レベルの使用に制限があるため、解釈された Transact-SQL でのメモリ最適化テーブルの使用には、多くの場合、テーブルへのアクセスに使用される分離レベルを指定するテーブルヒントが伴う必要があります。分離レベルのヒントとコンテナー間トランザクションの詳細については、「トランザクション分離レベル」を参照してください。
目的のトランザクション分離レベルを明示的に宣言する必要があります。ロックヒント (XLOCK など) を使用して、トランザクション内の特定の行またはテーブルを確実に分離することはできません。
データベースにアクセスするアプリケーションでは、トランザクションの運命の競合、検証エラー、コミット依存関係のエラーに起因するエラーに対処するための再試行ロジックを実装する必要があります。コミット依存関係エラーは、読み取り専用トランザクションでも発生する可能性があることに注意してください。
メモリ最適化テーブルでは、実行時間の長いトランザクションを回避する必要があります。このようなトランザクションにより、競合とその後のトランザクションの終了の可能性が高くなります。実行が長時間にわたるトランザクションでは、ガベージコレクションも遅延されます。トランザクションの実行時間が長いほど、OLTP In-Memory が最近削除された行のバージョンを保持し続けるので、新しいトランザクションの検索パフォーマンスが低下する可能性があります。

ディスクベースのテーブルは、通常、トランザクションの分離のためにロックとブロックに依存します。メモリ最適化テーブルは、分離を保証するために、マルチバージョン管理と競合検出に依存します。詳細については、「 Memory-Optimized テーブルのトランザクションの競合検出、検証、コミット依存関係チェック」セクションを参照してください。

ディスクベースのテーブルでは、分離レベル SNAPSHOT とREAD_COMMITTED_SNAPSHOTを使用したマルチバージョン管理が可能です。メモリ最適化テーブルの場合、REPEATABLE READ や SERIALIZABLE など、すべての分離レベルがマルチバージョンベースです。

トランザクションの種類

SQL Server のすべてのクエリは、トランザクションのコンテキストで実行されます。

SQL Server には、次の 3 種類のトランザクションがあります。

トランザクションの自動コミット。アクティブなトランザクションコンテキストがなく、暗黙的なトランザクションがセッションで ON に設定されていない場合、各クエリには独自のトランザクションコンテキストがあります。トランザクションは、ステートメントの実行が開始されたときに開始され、ステートメントの終了時に完了します。
明示的なトランザクション。ユーザーは、明示的な BEGIN TRAN または BEGIN ATOMIC を使用してトランザクションを開始します。トランザクションは、対応する COMMIT および ROLLBACK または END (アトミックブロックの場合) に従って完了します。
暗黙的なトランザクション。オプション IMPLICIT_TRANSACTIONSが ON に設定されている場合、ユーザーがステートメントを実行し、アクティブなトランザクションコンテキストがない場合は常に、トランザクションが暗黙的に開始されます。トランザクションは、明示的な COMMIT と ROLLBACK を使用して完了します。

基本の READ COMMITTED 隔離性

READ COMMITTED は、SQL Server の既定の分離レベルです。

分離レベル READ COMMITTED では、トランザクションに現在のトランザクション以外の変更からのコミットされていないデータが表示されないことが保証されます。つまり、トランザクションは、データベースにコミットされているか、現在のトランザクションによって変更されたデータのみを読み取ります。

メモリ最適化テーブルでサポートされているすべての分離レベルは、読み取りコミット保証を提供します。そのため、トランザクションでより強力な保証が必要ない場合は、メモリ最適化テーブルでサポートされている分離レベルのいずれかを使用できます。 SNAPSHOT は、他の分離レベルと比較して、最も少ないシステムリソースを使用します。

SNAPSHOT 分離レベルによって提供される保証 (メモリ最適化テーブルでサポートされる最下位レベルの分離) には、READ COMMITTED の保証が含まれます。トランザクション内のすべてのステートメントは、同じ一貫性のあるバージョンのデータベースを読み取ります。データベースにコミットされたトランザクションによってすべての行が読み取られるだけでなく、すべての読み取り操作で同じトランザクションセットによって行われた一連の変更も表示されます。

ガイドライン: READ COMMITTED 分離の保証のみが必要な場合は、ネイティブコンパイルストアドプロシージャと共に SNAPSHOT 分離を使用し、解釈された Transact-SQL を介してメモリ最適化テーブルにアクセスします。

自動コミットトランザクションの場合、分離レベル READ COMMITTED は、メモリ最適化テーブルの SNAPSHOT に暗黙的にマップされます。したがって、TRANSACTION ISOLATION LEVEL セッション設定が READ COMMITTED に設定されている場合、メモリ最適化テーブルにアクセスするときに、テーブルヒントを使用して分離レベルを指定する必要はありません。

次の自動コミットトランザクションの例は、アドホックバッチの一部として、メモリ最適化テーブル Customers と通常のテーブル [注文履歴] の間の結合を示しています。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
GO  
SELECT *   
FROM dbo.Customers AS c   
LEFT JOIN dbo.[Order History] AS oh   
    ON c.customer_id = oh.customer_id;

次の明示的または暗黙的なトランザクションの例は、同じ結合を示していますが、今回は明示的なユーザートランザクションです。メモリ最適化されたテーブル [Customers] は、テーブルヒント WITH (SNAPSHOT) で示されているようにスナップショット分離下でアクセスされ、通常のテーブル [Order History] は読み取りコミット済み分離下でアクセスされます。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
GO  
BEGIN TRAN  
SELECT * FROM dbo.Customers c with (SNAPSHOT)   
LEFT JOIN dbo.[Order History] oh   
    ON c.customer_id=oh.customer_id  
...  
COMMIT

運用上の違い

読み取りコミット保証に加えて、ディスクベーステーブルを使用するアプリケーションが依存する可能性がある 2 つの主要な実装の詳細もあります。 READ COMMITTED 分離を使用してアクセスされるディスクベースのテーブルを、SNAPSHOT 分離を使用してアクセスされるメモリ最適化テーブルに変換する場合は、次の点に注意してください。

ディスクベーステーブルの READ COMMITTED 分離レベル (READ_COMMITTED_SNAPSHOTが OFF の場合) の実装では、ロックを使用して、リーダーとライターの間の競合を防ぎます。ライターは、行の更新を開始するとロックを受け取り、トランザクションがコミットされるまでロックを解除しません。読み取り操作はすべてブロックされ、書き込みトランザクションがコミットされるまで待機します。

一部のアプリケーションでは、特にアプリケーション層の 2 つのトランザクション間に同期がある場合、リーダーはライターのコミットを常に待機すると想定される場合があります。

ガイドライン： アプリケーションはブロック動作に依存できません。アプリケーションで同時実行トランザクション間の同期が必要な場合、このようなロジックは、sp_getapplock (Transact-SQL) を使用して、アプリケーション層またはデータベース層に実装できます。
READ COMMITTED 分離を使用するトランザクションでは、各ステートメントにデータベース内の行の最新バージョンが表示されます。したがって、後続のステートメントでは、データベースの状態の変化が表示されます。

新しい行が見つかるまで WHILE ループを使用してテーブルをポーリングすることは、この前提条件を使用するアプリケーションパターンの例です。ループを繰り返すたびに、クエリにはデータベース内の最新の更新が表示されます。

ガイドライン： アプリケーションでメモリ最適化テーブルをポーリングして、テーブルに書き込まれた最新の行を取得する必要がある場合は、ポーリングループをトランザクションの範囲外に移動します。

この前提条件を使用するアプリケーションパターンの例を次に示します。新しい行が見つかるまで WHILE ループを使用してテーブルをポーリングします。各ループイテレーションで、クエリはデータベース内の最新の更新プログラムにアクセスします。

次のスクリプト例では、行が追加されるまでテーブル t1 をポーリングします。その後、テーブルから 1 つの行を削除して、さらに処理します。

ポーリングロジックは、スナップショット分離を使用してテーブル t1 にアクセスするため、トランザクションの範囲外である必要があることに注意してください。トランザクションのスコープ内でポーリングロジックを使用すると、実行時間の長いトランザクションが作成されます。これは不適切な方法です。

-- poll table  
WHILE NOT EXISTS (SELECT 1 FROM dbo.t1)  
BEGIN   
  -- if empty, wait and poll again  
  WAITFOR DELAY '00:00:01'  
END  
  
BEGIN TRANSACTION  
  DECLARE @id int  
  SELECT TOP 1 @id=id FROM dbo.t1 WITH (SNAPSHOT)  
  DELETE FROM dbo.t1 WITH (SNAPSHOT) WHERE id=@id  
  
  -- insert processing based on @id  
COMMIT

ロックテーブルヒント

HOLDLOCK や XLOCK などのロックヒント (テーブルヒント (Transact-SQL)) をディスクベースのテーブルと共に使用すると、SQL Server が指定された分離レベルで必要以上のロックを取得できます。

メモリ最適化テーブルではロックは使用されません。 REPEATABLE READ や SERIALIZABLE などのより高い分離レベルを使用して、必要な保証を宣言できます。

ロックヒントはサポートされていません。代わりに、トランザクション分離レベルを使用して必要な保証を宣言します。 (SQL Server はメモリ最適化テーブルに対してロックを取らないので、NOLOCK がサポートされています。ディスクベースのテーブルとは異なり、NOLOCK はメモリ最適化テーブルの READ UNCOMMITTED 動作を意味しないことに注意してください)。

こちらもご覧ください

Memory-Optimized テーブルのトランザクションについて
 Memory-Optimized テーブルでのトランザクションの再試行ロジックのガイドライン
 トランザクション分離レベル