エラー発生時のトランザクションのロールバック - SET XACT_ABORT ON　と TRY...CATCH

エラー発生時のトランザクションのロールバック

BEGIN TRANSACTION と COMMIT TRANSACTION を使って、複数のステートメントをひとつのトランザクションとして実行したいとします。

今回は、そのトランザクションの途中でエラーが発生した時のロールバックのお話です。

今回使うテーブルやデータはこちらのスクリプトで生成できますが、同じ名前の既存のテーブルがあると削除されてしまうのでご注意ください。

SET XACT_ABORT ON と TRANSACTION

次のような Student テーブルと TestResult テーブルがあり、TestResult テーブルの StudentID カラムには Student テーブルの StudentID カラムへの外部キー制約が定義されています。

次のように、3 つの UPDATE ステートメントで TestResult テーブルの　StudentID カラムを更新しますが、2 個目は存在しない StudentID を指定しているのでエラーになります。

UPDATE  TestResult
SET     StudentID = 4
WHERE   TestResultID = 1;

UPDATE  TestResult
SET     StudentID = 50
WHERE   TestResultID = 2;

UPDATE  TestResult
SET     StudentID = 6
WHERE   TestResultID = 3;

3 つのステートメントをひとつのトランザクションとして、エラーが起こった場合には、全部の更新をロールバックさせたいとします。

そんな時は BEGIN TRANSACTION と COMMIT TRANSACTION でひとつのトランザクションとしたいスクリプトをはさみますが、それだけではエラー発生時に全ての処理がロールバックされません。

試しに次のスクリプトを実行して結果をみてみましょう。

BEGIN TRANSACTION;

UPDATE  TestResult
SET     StudentID = 4
WHERE   TestResultID = 1;

UPDATE  TestResult
SET     StudentID = 50
WHERE   TestResultID = 2;

UPDATE  TestResult
SET     StudentID = 6
WHERE   TestResultID = 3;

COMMIT TRANSACTION;

[ 実行結果 ]

2 個目の UPDATE ステートメントでエラーが起こりましたが、1 個目と 3 個目の UPDATE ステートメントは実行され、値が更新されてしまいました。

トランザクションの途中でエラーが発生した時に、トランザクション内の処理を全てロールバックさせるには、BEGIN TRANSACTION の前に SET XACT_ABORT を ON に設定しておきます。

それでは、StudentID の値を元に戻して、今度は SET XACT_ABORT ON を実行してから、同じスクリプトを実行してみます。

SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE  TestResult
SET     StudentID = 4
WHERE   TestResultID = 1;

UPDATE  TestResult
SET     StudentID = 50
WHERE   TestResultID = 2;

UPDATE  TestResult
SET     StudentID = 6
WHERE   TestResultID = 3;

COMMIT TRANSACTION;

[ 実行結果 ]

2 個目の UPDATE ステートメントが起こった時点で処理が終了してロールバックされ、TestResult テーブルの値は更新されませんでしたね。

TRY...CATCH を使ってロールバックする

TRY...CATCH を使っても、エラー発生した際に処理全部をロールバックさせることができます。

エラーを CATCH して何か処理をしたい際に便利です。

TRY...CATCH を使って、ひとつでもエラーが起こった場合に、次のようにトランザクション内の処理をロールバックさせることができます。

BEGIN TRY  

    BEGIN TRANSACTION;

        UPDATE  TestResult
        SET     StudentID = 4
        WHERE   TestResultID = 1;

        UPDATE  TestResult
        SET     StudentID = 50
        WHERE   TestResultID = 2;

        UPDATE  TestResult
        SET     StudentID = 6
        WHERE   TestResultID = 3;

    COMMIT TRANSACTION; 

END TRY  
BEGIN CATCH 

    ROLLBACK TRANSACTION;

END CATCH

[ 実行結果 ]

エラーはキャッチされ、TestResult テーブルの値も更新されていませんね。

私は、スクリプトを見ながら実行するような時は SET XACT_ABORT ON を、そうでない時はログを残したいので TRY...CATCH を使うことが多いです。

エラー発生時のトランザクションのロールバック - SET XACT_ABORT ON と TRY...CATCH