SQL ServerでXACT_ABORTとRAISERRORの動作を理解する

2024-04-06

SQL ServerでRAISERROR後に実行が継続される理由

RAISERROR は、SQL Server でエラーメッセージを表示するために使用されるステートメントです。通常、RAISERROR の後、トランザクションはロールバックされ、実行は停止されます。しかし、XACT_ABORT オプションがオンの場合、RAISERROR の後も実行が継続されることがあります。

原因

XACT_ABORT オプションは、エラーが発生したときにトランザクションを自動的にロールバックするかどうかを制御します。このオプションがオンの場合、RAISERROR でエラーレベルが 16 以下の場合、トランザクションはロールバックされます。しかし、エラーレベルが 17 以上の場合は、トランザクションはロールバックされません。

影響

XACT_ABORT オプションがオンの場合、RAISERROR の後も実行が継続されると、予期せぬ結果が発生する可能性があります。例えば、エラーが発生したにもかかわらず、データが変更されたり、コミットされたりすることがあります。

解決策

XACT_ABORT オプションがオンの場合、RAISERROR の後も実行が継続されることを防ぐためには、以下の方法があります。

エラーレベルを 16 以下にする
THROW ステートメントを使用する
TRY...CATCH ブロックを使用する

補足

XACT_ABORT オプションは、デフォルトでオフになっています。
RAISERROR でエラーレベルを指定しない場合は、エラーレベルは 11 になります。
THROW ステートメントは、エラーを発生させるために使用されます。
TRY...CATCH ブロックは、エラーが発生した場合の処理を記述するために使用されます。

SET XACT_ABORT ON

BEGIN TRY

    -- エラーが発生するクエリ

    RAISERROR('エラーが発生しました。', 17, 1)

END TRY

BEGIN CATCH

    -- エラー処理

END CATCH

このコードを実行すると、エラーメッセージが表示されますが、トランザクションはロールバックされません。

以下のコードは、THROW ステートメントを使用してエラーを発生させる例です。

SET XACT_ABORT ON

BEGIN TRY

    -- エラーが発生するクエリ

    THROW 50000, 'エラーが発生しました。', 1;

END TRY

BEGIN CATCH

    -- エラー処理

END CATCH

以下のコードは、TRY...CATCH ブロックを使用してエラー処理を行う例です。

SET XACT_ABORT ON

BEGIN TRY

    -- エラーが発生するクエリ

BEGIN CATCH

    -- エラー処理

    ROLLBACK TRAN

END CATCH

上記のサンプルコードはあくまでも例です。実際のコードは、状況に合わせて変更する必要があります。

RAISERROR の後も実行が継続されることを防ぐその他の方法

RAISERROR のエラーレベルを 16 以下に設定することで、XACT_ABORT オプションがオンであってもトランザクションがロールバックされます。

RAISERROR('エラーが発生しました。', 10, 1)

@@TRANCOUNT 変数は、現在のトランザクションレベルを示します。RAISERROR の前に @@TRANCOUNT の値を保存しておき、RAISERROR の後に @@TRANCOUNT の値が減っている場合は、トランザクションをロールバックすることができます。

DECLARE @trancount INT

SET @trancount = @@TRANCOUNT

RAISERROR('エラーが発生しました。', 17, 1)

IF @@TRANCOUNT < @trancount
BEGIN
    ROLLBACK TRAN
END

GOTO ステートメントを使用して、エラーが発生した場合はエラー処理を行うラベルにジャンプすることができます。

BEGIN TRY

    -- エラーが発生するクエリ

    GOTO ErrorHandler

END TRY

BEGIN CATCH

    -- エラー処理

END CATCH

ErrorHandler:

    -- エラー処理

ユーザー定義エラーメッセージを使用することで、エラーメッセージに詳細情報を追加することができます。

CREATE PROCEDURE dbo.MyError
AS
BEGIN
    RAISERROR('エラーが発生しました。詳細については、エラーログを参照してください。', 17, 1)
END

BEGIN TRY

    EXEC dbo.MyError

END TRY

BEGIN CATCH

    -- エラー処理

END CATCH

トレースフラグを使用して、エラーが発生したときの動作を変更することができます。

DBCC TRACEON(1224)

BEGIN TRY

    -- エラーが発生するクエリ

END TRY

BEGIN CATCH

    -- エラー処理

END CATCH

DBCC TRACEOFF(1224)

リモートプロシージャコール (RPC) を使用する

RPC を使用することで、別のサーバーでエラーが発生した場合でも、現在のトランザクションをロールバックすることができます。

EXEC sp_executesql @remote_server, 'RAISERROR(''エラーが発生しました。'', 17, 1)'

BEGIN TRY

    -- エラーが発生するクエリ

    BEGIN DIALOG @dialog_handle
        FROM SERVICE @service_name
        TO SERVICE @service_name
        ON CONVERSATION @conversation_handle
    END DIALOG @dialog_handle

END TRY

BEGIN CATCH

    -- エラー処理

END CATCH