Azure Synapse Analytics の専用 SQL プールでトランザクションを使用する

ソリューションの開発に向けて、Azure Synapse Analytics の専用 SQL プールでトランザクションを実装するためのヒントを紹介します。

ウィザードの内容

予想される通り、専用 SQL プールは、データ ウェアハウスのワークロードの一部としてトランザクションをサポートします。 ただし、専用 SQL プールのパフォーマンスを大規模に維持できるように、SQL Server と比べ、一部の機能は制限されています。 この記事では、相違点について説明し、その他の制限事項を示します。

トランザクション分離レベル

専用 SQL プールは、ACID トランザクションを実装します。 トランザクションサポートの分離レベルは、既定では READ UNCOMMITTED になります。 これは READ COMMITTED SNAPSHOT ISOLATION に変更できます。それには、マスター データベースに接続する際にユーザー データベースの READ_COMMITTED_SNAPSHOT データベース オプションをオンにします。

有効になると、このデータベース内のすべてのトランザクションが READ COMMITTED SNAPSHOT ISOLATION の下で実行され、セッション レベルで READ UNCOMMITTED を設定しても受け入れられません。 詳細については、「ALTER DATABASE の SET オプション (Transact-SQL)」を確認してください。

トランザクション サイズ

1 回のデータ変更トランザクションには規模の面で制限があります。 この制限は、ディストリビューションごとに適用されます。 そのため、割り当ての合計を算出するには、制限とディストリビューション数を掛ける必要があります。

トランザクションの最大行数の近似値を求めるには、ディストリビューション上限を各列の合計サイズで割ります。 列の長さが異なる場合、最大サイズではなく列の平均長を利用することを検討してください。

下の表では、次のような想定が行われています。

• データは均等に分散されました
• 行の平均長は 250 バイトです

Gen2

Gen1

トランザクション サイズ制限は、トランザクションあたりまたは操作あたりで適用されます。 すべての同時実行トランザクションにまたいで適用されることはありません。 そのため、このデータ量をログに書き込むことが各トランザクションに許可されます。

ログに書き込まれるデータ量を最適化および最小化する方法については、トランザクションのベスト プラクティスに関する記事を参照してください。

トランザクションの状態

専用 SQL プールでは、XACT_STATE() 関数を使い、値 -2 を使用して、失敗したトランザクションを報告します。 この値は、トランザクションが失敗し、ロールバックのためにのみマークされていることを意味します。

たとえば、SQL Server では、次のようなトランザクションを目にすることがあります。

SET NOCOUNT ON;
DECLARE @xact_state smallint = 0;

BEGIN TRAN
    BEGIN TRY
        DECLARE @i INT;
        SET     @i = CONVERT(INT,'ABC');
    END TRY
    BEGIN CATCH
        SET @xact_state = XACT_STATE();

        SELECT  ERROR_NUMBER()    AS ErrNumber
        ,       ERROR_SEVERITY()  AS ErrSeverity
        ,       ERROR_STATE()     AS ErrState
        ,       ERROR_PROCEDURE() AS ErrProcedure
        ,       ERROR_MESSAGE()   AS ErrMessage
        ;

        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN;
            PRINT 'ROLLBACK';
        END

    END CATCH;

IF @@TRANCOUNT >0
BEGIN
    PRINT 'COMMIT';
    COMMIT TRAN;
END

SELECT @xact_state AS TransactionState;

上記のコードでは、次のようなエラー メッセージが発生します。

メッセージ 111233、レベル 16、状態 1、ライン 1 111233; 111233; 現在のトランザクションは中止され、保留中の変更はすべてロールバックされています。 原因:rollback-only 状態のトランザクションが、DDL、DML、または SELECT ステートメントの前に明示的にロールバックされませんでした。

ERROR_* 関数の出力は得られません。

専用 SQL プールでは、コードを少し変更する必要があります。

SET NOCOUNT ON;
DECLARE @xact_state smallint = 0;

BEGIN TRAN
    BEGIN TRY
        DECLARE @i INT;
        SET     @i = CONVERT(INT,'ABC');
    END TRY
    BEGIN CATCH
        SET @xact_state = XACT_STATE();

        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN;
            PRINT 'ROLLBACK';
        END

        SELECT  ERROR_NUMBER()    AS ErrNumber
        ,       ERROR_SEVERITY()  AS ErrSeverity
        ,       ERROR_STATE()     AS ErrState
        ,       ERROR_PROCEDURE() AS ErrProcedure
        ,       ERROR_MESSAGE()   AS ErrMessage
        ;
    END CATCH;

IF @@TRANCOUNT >0
BEGIN
    PRINT 'COMMIT';
    COMMIT TRAN;
END

SELECT @xact_state AS TransactionState;

想定される動作が見られるようになりました。 トランザクションのエラーが管理され、ERROR_* 関数が予想通りの値を示します。

変更点をまとめると、CATCH ブロック内でエラー情報の読み取り前にトランザクションの ROLLBACK が発生するようになりました。

Error_Line() 関数

専用 SQL プールが、ERROR_LINE() 関数を実装またはサポートしていないことにも注意してください。 この関数がコードに含まれている場合は、専用 SQL プールに準拠するために、これを削除する必要があります。 代わりに、コードでクエリ ラベルを使用して同等の機能を実装します。 詳しくは、LABEL に関する記事を参照してください。

THROW および RAISERROR の使用

THROW は、専用 SQL プールで例外を発生させるための最新の実装ですが、RAISERROR もサポートされています。 ただし、注意が必要な相違点がいくつかあります。

• THROW では、ユーザー定義のエラー メッセージの番号として、100,000～150,000 の範囲の番号を指定することはできません。
• RAISERROR のエラー メッセージは 50,000 に固定されています。
• sys.messages の使用はサポートされていません。

制限事項

専用 SQL プールには、トランザクションに関する他の制限事項がいくつかあります。 制限事項は次のとおりです。

• 分散トランザクションは使用できません。
• 入れ子になったトランザクションは使用できません。
• セーブ ポイントは使用できません。
• トランザクションに名前を付けることはできません。
• トランザクションにマークを付けることはできません。
• ユーザー定義トランザクション内では CREATE TABLE のような DDL はサポートされません。

次のステップ

トランザクションの最適化について詳しくは、トランザクションのベスト プラクティスに関する記事をご覧ください。 専用 SQL プールとサーバーレス SQL プール のベスト プラクティス ガイドも用意されています。