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

2023-06-01

この記事には、SQL プールでトランザクションを実装し、ソリューションを開発するためのヒントが記載されています。

ウィザードの内容

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

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

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

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

トランザクションサイズ

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

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

下の表では、2 つの想定が行われています。

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

Gen2

DWU	ディストリビューションあたりの上限 (GB)	ディストリビューション数	最大トランザクションサイズ (GB)	ディストリビューションあたりの行数	トランザクションあたりの最大行数
DW100c	1	60	60	4,000,000	240,000,000
DW200c	1.5	60	90	6,000,000	360,000,000
DW300c	2.25	60	135	9,000,000	540,000,000
DW400c	3	60	180	12,000,000	720,000,000
DW500c	3.75	60	225	15,000,000	900,000,000
DW1000c	7.5	60	450	30,000,000	1,800,000,000
DW1500c	11.25	60	675	45,000,000	2,700,000,000
DW2000c	15	60	900	60,000,000	3,600,000,000
DW2500c	18.75	60	1125	75,000,000	4,500,000,000
DW3000c	22.5	60	1,350	90,000,000	5,400,000,000
DW5000c	37.5	60	2,250	150,000,000	9,000,000,000
DW6000c	45	60	2,700	180,000,000	10,800,000,000
DW7500c	56.25	60	3,375	225,000,000	13,500,000,000
DW10000c	75	60	4,500	300,000,000	18,000,000,000
DW15000c	112.5	60	6,750	450,000,000	27,000,000,000
DW30000c	225	60	13,500	900,000,000	54,000,000,000

Gen1

DWU	ディストリビューションあたりの上限 (GB)	ディストリビューション数	最大トランザクションサイズ (GB)	ディストリビューションあたりの行数	トランザクションあたりの最大行数
DW100	1	60	60	4,000,000	240,000,000
DW200	1.5	60	90	6,000,000	360,000,000
DW300	2.25	60	135	9,000,000	540,000,000
DW400	3	60	180	12,000,000	720,000,000
DW500	3.75	60	225	15,000,000	900,000,000
DW600	4.5	60	270	18,000,000	1,080,000,000
DW1000	7.5	60	450	30,000,000	1,800,000,000
DW1200	9	60	540	36,000,000	2,160,000,000
DW1500	11.25	60	675	45,000,000	2,700,000,000
DW2000	15	60	900	60,000,000	3,600,000,000
DW3000	22.5	60	1,350	90,000,000	5,400,000,000
DW6000	45	60	2,700	180,000,000	10,800,000,000

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

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

警告

最大トランザクションサイズは、HASH または ROUND_ROBIN で分散し、データが均等に分散されたテーブルでのみ達成できます。トランザクションによりデータが書き込まれ、分散が傾斜する場合、最大トランザクションサイズに到達する前に上限に到達する可能性があります。

トランザクションの状態

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

Note

失敗したトランザクションを示すために XACT_STATE 関数の -2 を使用するのは、SQL Server とは異なる動作です。 SQL Server では、コミットできないトランザクションを表すために値 -1 を使用します。 SQL Server では、コミット不可としてマークしなくても、トランザクション内で一部のエラーを許容できます。たとえば、SELECT 1/0 はエラーになりますが、トランザクションが強制的にコミット不可状態になることはありません。

また、SQL Server では、コミットできないトランザクションでの読み取りも許可されます。ただし、SQL プールではこれを実行できません。 SQL プールのトランザクションでエラーが発生した場合は、-2 状態が自動的に入力され、ステートメントがロールバックされるま select ステートメントをそれ以上実行できなくなります。

このため、コードを変更する必要がある場合は、アプリケーションコードを調べて、XACT_STATE() を使用しているかどうかを確認することが重要です。

たとえば、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 プールに準拠するために削除する必要があります。

代わりに、コードでクエリラベルを使用して同等の機能を実装します。詳しくは、ラベルに関する記事をご覧ください。

THROW と RAISERROR の使用

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

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

制限事項

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

制限事項は次のとおりです。

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

次のステップ

トランザクションの最適化について詳しくは、トランザクションのベストプラクティスに関する記事をご覧ください。他の SQL プールのベストプラクティスの詳細については、「SQL プールのベストプラクティス」を参照してください。