メモリ最適化テーブルでのトランザクション

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance

ここでは、メモリ最適化テーブルおよびネイティブ コンパイル ストアド プロシージャに固有のトランザクションをあらゆる側面から説明します。

SQL Server のトランザクション分離レベルは、ディスク ベースのテーブルとは異なる方法でメモリ最適化テーブルに適用されます。基になるメカニズムも異なっています。 プログラマは、この違いを理解することで、高スループットのシステムを設計しやすくなります。 トランザクションの整合性という目標は、すべての状況で共有されます。

メモリ最適化テーブルでのトランザクションに固有のエラー条件については、「 競合の検出と再試行ロジック」のセクションに移動してください。

詳細については、「 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)」を参照してください。

ペシミスティックとオプティミスティック

トランザクションの整合性へのアプローチがオプティミスティックかペシミスティックかによって、機能は異なってきます。 メモリ最適化テーブルでは、オプティミスティック アプローチが使用されます。

• ペシミスティック アプローチでは、潜在的な競合を、それが発生する前にロックを使ってブロックします。 ステートメントが実行されるときにロックが取得され、トランザクションがコミットされるとロックが解放されます。

• オプティミスティック アプローチでは、競合がその発生時に検出され、コミット時に検証チェックが実行されます。

  • メモリ最適化テーブルに対しては、エラー 1205 (デッドロック) は発生しません。

オプティミスティック アプローチはオーバーヘッドが少なく、通常はより効率的です。これは 1 つには、トランザクションの競合がほとんどのアプリケーションであまり発生しないためです。 ペシミスティック アプローチとオプティミスティック アプローチの機能上の主な違いは、競合が発生した場合に、ペシミスティック アプローチでは待機するのに対し、オプティミスティック アプローチでは、トランザクションの 1 つが失敗し、クライアントがそのトランザクションを再試行する必要があるという点です。 機能の違いは、REPEATABLE READ 分離レベルが有効だとさらに大きくなり、SERIALIZABLE レベルで最大になります。

トランザクション開始モード

SQL Server のトランザクション開始モードは次のとおりです。

• オートコミット - シンプルなクエリまたは DML ステートメントが開始されると、トランザクションが暗黙的に開かれ、ステートメントが終了すると、トランザクションが暗黙的にコミットされます。 既定値は自動コミットです。

  • オートコミット モードでは、通常、FROM 句で、メモリ最適化テーブルのトランザクション分離レベルに関するテーブル ヒントをコーディングする必要はありません。

• Explicit - Transact-SQL には、BEGIN TRANSACTION コードと共に最終的な COMMIT TRANSACTION コードが含まれています。 2 つ以上のステートメントを、同じトランザクションに含めることができます。

  • Explicite モードでは、データベース オプションの MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT を使用するか、FROM 句でメモリ最適化テーブルのトランザクション分離レベルに関するテーブル ヒントをコーディングする必要があります。

• Implicit - SET IMPLICIT_TRANSACTION ON が有効の場合。 おそらく、IMPLICIT_BEGIN_TRANSACTION という名前の方が適切です。このオプションでは、0 = @@trancount の場合に、各 UPDATE ステートメントの前で、明示的な BEGIN TRANSACTION と同じことを暗黙的に実行するからです。 したがって、明示的な COMMIT TRANSACTION を最終的に発行するかどうかは、T-SQL コード次第です。

• ATOMIC ブロック - ATOMIC ブロックのすべてのステートメントは、常に 1 つのトランザクションとして実行されます。 ATOMIC ブロックのアクションは成功した場合に全体がコミットされるか、エラー発生時にすべてがロールバックされるかのどちらかです。 それぞれのネイティブ コンパイル ストアド プロシージャには ATOMIC ブロックが必要です。

Explicit モードのコード例

解釈された Transact-SQL スクリプトでは次を使用します。

• 明示的なトランザクション。
• dbo.Order_mo という名前のメモリ最適化テーブル。
• READ COMMITTED トランザクション分離レベルのコンテキスト。

したがって、メモリ最適化テーブルにテーブル ヒントが必要です。 ヒントは SNAPSHOT またはさらに高い分離レベルを対象にしている必要があります。 コード例の場合、ヒントは WITH (SNAPSHOT) です。 このヒントが削除されている場合、スクリプトでは、41368 エラー (自動再試行が不適切) が発生します。

エラー 41368

READ COMMITTED 分離レベルを使用してメモリ最適化テーブルにアクセスする機能は、オートコミット トランザクションでのみサポートされます。 明示的なトランザクションおよび暗黙的なトランザクションではサポートされていません。 WITH (SNAPSHOT) などのテーブル ヒントを使用して、メモリ最適化テーブルでサポートされる分離レベルを指定します。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
GO  

BEGIN TRANSACTION;  -- Explicit transaction.  

-- Order_mo  is a memory-optimized table.  
SELECT * FROM  
           dbo.Order_mo  as o  WITH (SNAPSHOT)  -- Table hint.  
      JOIN dbo.Customer  as c  on c.CustomerId = o.CustomerId;  
COMMIT TRANSACTION;

データベース オプション MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT を使用することで、WITH (SNAPSHOT) ヒントの必要はなくなります。 このオプションを ONに設定すると、低い分離レベルでのメモリ最適化テーブルへのアクセスは、自動的に SNAPSHOT 分離に昇格されます。

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

行のバージョン管理

メモリ最適化テーブルでは高度な行バージョン管理システムが使用され、最も厳密な分離レベル SERIALIZABLE でも、効率的なオプティミスティック アプローチが実現します。 詳細については、「 メモリ最適化テーブルの概要」を参照してください。

READ_COMMITTED_SNAPSHOT または SNAPSHOT 分離レベルが有効な場合は、行バージョン管理システムがディスク ベースのテーブルに間接的に含まれます。 このシステムは tempdb に基づいています。一方、メモリ最適化データ構造には、最大限の効率を得るために、行バージョン管理が組み込まれています。

分離レベル

次の表は、トランザクション分離のレベルを、分離が少ないものから順に示しています。 発生する可能性のある競合、およびこれらの競合を処理するための再試行ロジックの詳細については、「 競合の検出と再試行ロジック」を参照してください。

トランザクション フェーズと有効期間

メモリ最適化テーブルが関係している場合、トランザクションの有効期間は次の図に示すようなフェーズを辿ります。

各フェーズの説明は次のとおりです。

通常の処理: フェーズ 1 (1/3)

• このフェーズは、すべてのクエリとクエリ内の DML ステートメントの実行で構成されます。
• このフェーズでは、トランザクションの論理的開始時点のメモリ最適化テーブルのバージョンをステートメントが調べます。

検証: フェーズ 2 (2/3)

• 終了時刻を割り当て、それによってトランザクションに論理的完了とマークすると、検証フェーズが始まります。 この完了により、このトランザクションと依存関係があるその他のトランザクションに、トランザクションのすべての変更内容が表示されます。 従属トランザクションは、このトランザクションが正常にコミットされるまではコミットできません。 さらに、このような依存関係を保持するトランザクションは、結果セットをクライアントに返すことはできません。データベースに正常にコミットされたデータのみ、クライアントが参照するようにするためです。
• このフェーズは、repeatable read と serializable の検証で構成されます。 repeatable read の検証では、トランザクションによって読み取られた行のいずれかが以降更新されたかどうかがチェックされます。 serializable の検証では、このトランザクションによってスキャンされたいずれかのデータ範囲に行が挿入されたかどうかがチェックされます。 スナップショット分離の使用時は、一意キー制約と外部キー制約の整合性を検証するために、「分離レベルと競合」の表に従って、repeatable read と serializable の両方の検証が行われることがあります。

コミット処理: フェーズ 3 (3/3)

• コミット フェーズでは、持続性のあるテーブルへの変更がログに書き込まれ、そのログはディスクに書き込まれます。 その後、制御がクライアントに返されます。
• コミット処理が完了すると、すべての従属トランザクションにコミット可能であることが通知されます。

いつものように、トランザクションの作業単位は、データ ニーズに合わせてできるだけシンプルに、かつ小さくまとめてください。

競合の検出と再試行ロジック

トランザクションが失敗してロールバックが行われる原因となるトランザクション関連のエラー条件には、2 種類あります。 ほとんどの場合、このようなエラーが発生したら、デッドロックが発生したときと同様に、トランザクションを再試行する必要があります。

• 同時実行トランザクション間の競合。 これらは更新の競合と検証のエラーであり、トランザクション分離レベル違反や制約違反が原因として考えられます。
• 依存関係のエラー。 これらは、依存するトランザクションのコミットが失敗したこと、または依存関係の数が大きくなりすぎたことに起因します。

メモリ最適化テーブルにアクセスするトランザクションの失敗の原因となるエラー条件を以下に示します。

再試行ロジック

上記の条件のいずれかが原因でトランザクションが失敗した場合は、そのトランザクションを再試行する必要があります。

再試行ロジックは、クライアント側またはサーバー側に実装できます。 一般的には、クライアント側に再試行ロジックを実装することをお勧めします。この方がより効率的であり、トランザクションが返した結果セットをエラーが発生する前に処理できるからです。

再試行 T-SQL コードの例

T-SQL を使用したサーバー側の再試行ロジックは、結果セットをクライアントに返さないトランザクションでのみ使用してください。 そうしないと、再試行によってクライアントに返されると想定された以上の結果セットが返される場合があります。

次の解釈された T-SQL スクリプトは、メモリ最適化テーブルに関するトランザクション競合に関連付けられたエラーの再試行ロジックを示しています。

-- Retry logic, in Transact-SQL.
DROP PROCEDURE If Exists usp_update_salesorder_dates;
GO

CREATE PROCEDURE usp_update_salesorder_dates
AS
BEGIN
    DECLARE @retry INT = 10;

    WHILE (@retry > 0)
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;

            UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)
                set OrderDate = GetUtcDate()
                where CustomerId = 42;

            UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)
                set OrderDate = GetUtcDate()
                where CustomerId = 43;

            COMMIT TRANSACTION;

            SET @retry = 0;  -- //Stops the loop.
        END TRY

        BEGIN CATCH
            SET @retry -= 1;

            IF (@retry > 0 AND
                ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41823, 41840, 41839, 1205)
                )
            BEGIN
                IF XACT_STATE() = -1
                    ROLLBACK TRANSACTION;

                WAITFOR DELAY '00:00:00.001';
            END
            ELSE
            BEGIN
                PRINT 'Suffered an error for which Retry is inappropriate.';
                THROW;
            END
        END CATCH

    END -- //While loop
END;
GO

--  EXECUTE usp_update_salesorder_dates;

複数コンテナーにまたがるトランザクション

次の場合、トランザクションは複数コンテナーにまたがるトランザクションと呼ばれます。

• 解釈された Transact-SQL のメモリ最適化テーブルにアクセスする。
• トランザクションが既に開いているときに、ネイティブ プロシージャを実行する (XACT_STATE() = 1)。

「複数コンテナーにまたがる」という用語は、2 つのトランザクション管理コンテナー (1 つはディスク ベースのテーブル用、もう 1 つはメモリ最適化テーブル用) にまたがってトランザクションが実行されることに由来します。

複数コンテナーにまたがる単一のトランザクション内で、ディスク ベースのテーブルへのアクセスとメモリ最適化テーブルへのアクセスに、それぞれ異なる分離レベルを使用できます。 この違いは、WITH (SERIALIZABLE) のような明示的なテーブル ヒントか、データベース オプション MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT によって表されます。このデータベース オプションを使用すると、TRANSACTION ISOLATION LEVEL が READ COMMITTED または READ UNCOMMITTED として構成されていれば、メモリ最適化テーブルの分離レベルが暗黙的にスナップショットに昇格されます。

Transact-SQL コードの例を次に示します。

• ディスク ベースのテーブル Table_D1 は、READ COMMITTED 分離レベルを使用してアクセスされます。
• メモリ最適化テーブル Table_MO7 は、SERIALIZABLE 分離レベルを使用してアクセスされます。 Table_MO6 には特定の分離レベルが関連付けられていません。挿入は常に一貫しており、基本的に serializable 分離で実行されるからです。

-- Different isolation levels for
-- disk-based tables versus memory-optimized tables,
-- within one explicit transaction.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
go

BEGIN TRANSACTION;

    -- Table_D1 is a traditional disk-based table, accessed using READ COMMITTED isolation.

    SELECT * FROM Table_D1;


    -- Table_MO6 and Table_MO7 are memory-optimized tables.
    -- Table_MO7 is accessed using SERIALIZABLE isolation,
    --   while Table_MO6 does not have a specific isolation level.

    INSERT Table_MO6
        SELECT * FROM Table_MO7 WITH (SERIALIZABLE);

COMMIT TRANSACTION;
go

制限事項

• 複数データベースにまたがるトランザクションは、メモリ最適化テーブルではサポートされません。 トランザクションがメモリ最適化テーブルにアクセスする場合、そのトランザクションは、他のデータベースにはアクセスできません。ただし次を除きます。

  • tempdb データベース。
  • マスター データベースからの読み取り専用。

• 分散トランザクションはサポートされていません。BEGIN DISTRIBUTED TRANSACTION が使用されると、トランザクションはメモリ最適化テーブルにアクセスできません。

ネイティブ コンパイル ストアド プロシージャ

• ネイティブ プロシージャでは、次のように、ATOMIC ブロックが、ブロック全体に対してトランザクション分離レベルを宣言する必要があります。

  • ... BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, ...) ...

• ネイティブ プロシージャの本文では、明示的なトランザクション制御ステートメントは許可されません。 BEGIN TRANSACTION、ROLLBACK TRANSACTION など、どれも許可されていません。

• ATOMIC ブロックに関するトランザクション制御の詳細については、「ATOMIC ブロック」を参照してください

その他のトランザクション リンク

• [SET IMPLICIT_TRANSACTIONS]

• sp_getapplock (Transact-SQL)

• データベース エンジンでの行のバージョン管理に基づく分離レベル

• トランザクションの持続性の制御