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
| 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 プールに準拠するために、これを削除する必要があります。 代わりに、コードでクエリ ラベルを使用して同等の機能を実装します。 詳しくは、LABEL に関する記事を参照してください。
THROW および RAISERROR の使用
THROW は、専用 SQL プールで例外を発生させるための最新の実装ですが、RAISERROR もサポートされています。 ただし、注意が必要な相違点がいくつかあります。
- THROW では、ユーザー定義のエラー メッセージの番号として、100,000～150,000 の範囲の番号を指定することはできません。
- RAISERROR のエラー メッセージは 50,000 に固定されています。
- sys.messages の使用はサポートされていません。
制限事項
専用 SQL プールには、トランザクションに関する他の制限事項がいくつかあります。 制限事項は次のとおりです。
- 分散トランザクションは使用できません。
- 入れ子になったトランザクションは使用できません。
- セーブ ポイントは使用できません。
- トランザクションに名前を付けることはできません。
- トランザクションにマークを付けることはできません。
- ユーザー定義トランザクション内では CREATE TABLE のような DDL はサポートされません。
次のステップ
トランザクションの最適化について詳しくは、トランザクションのベスト プラクティスに関する記事をご覧ください。 専用 SQL プールとサーバーレス SQL プール のベスト プラクティス ガイドも用意されています。