SQL ServerにおけるXACT_ABORTのON/OFFによる挙動差異と注意点

SQL Server

ogp

はじめに

こんにちは。カート決済部の林です。ZOZOTOWN内のカートや決済の機能開発、保守運用を担当しています。

過去に福袋販売イベントの負荷対策の記事を掲載しました。

techblog.zozo.com

上記の記事では、タイムアウトしたプロセスがロックを掴んだままになっていたことが原因で、大量のブロッキングが発生していました。詳細な負荷や対策の内容について知りたい方は、ぜひ上記の記事を読んでみてください。

こちらの原因を解決するために、XACT_ABORTの設定をONにすることが有効であると記載しています。XACT_ABORTはトランザクション内でエラーが発生すると即座にロールバック＋ロックの解放を指示できるオプションです。このオプションをONにすることで、タイムアウトした時点でロックが解放され、ブロッキングが発生しなくなりました。

ただし、設定をONに変えると一部動作が変わり、既存の処理が正常に動かなくなることがあります。弊社でONにした際も一部のストアドプロシージャ（以下ストアド）が正常に動かなくなりました。その時にXACT_ABORTの動作について確認したので、その確認内容について本記事で紹介したいと思います。

XACT_ABORTの動作について知りたい方や、これからONにしようとしている方などの参考になれば幸いです。

目次

XACT_ABORTの概要

XACT_ABORTはSQL Serverのオプションの1つで、デフォルトではOFFになっています。

ON/OFFそれぞれの動作を以下の表に記載します。

設定 エラー時の動作 後続の処理
OFF 発生した処理のみがロールバック 実行される場合がある
ON トランザクション全体が終了しロールバック 実行されない

実際に上記の動作についてテスト用のテーブルを作成し動作確認を行います。

動作確認の準備

実際に動作を確認するために、テスト用のテーブルと初期レコードを準備します。テーブル作成は以下のDDLを実行し、table1とtable2を作成します。このとき、動作確認時にエラーを起こしやすくするために、table1のcol1とtable2のcol1に外部キー制約を設定します。

CREATE TABLE table1
    (col1 INT NOT NULL PRIMARY KEY);
CREATE TABLE table2
    (col1 INT NOT NULL REFERENCES table1(col1));

初期のレコードとして以下のクエリを実行しtable1に4レコードINSERTします。

INSERT INTO table1 VALUES (1);
INSERT INTO table1 VALUES (3);
INSERT INTO table1 VALUES (4);
INSERT INTO table1 VALUES (5);

ここまでで準備完了になります。

トランザクション内での動作

XACT_ABORTがOFFの場合

動作確認のために以下のクエリを実行します。

SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO table2 VALUES (1);
INSERT INTO table2 VALUES (2); -- 外部キー制約によりエラーになる
INSERT INTO table2 VALUES (3);
COMMIT TRANSACTION;
GO

1行目でXACT_ABORTをOFFに設定します。その後トランザクションを開始します。トランザクション内でtable2に3回のINSERTを行います。1回目と3回目のINSERTは正常に完了します。しかし、2回目のINSERTではtable1のcol1に2が入っているレコードが存在しないため、外部キー制約により失敗します。3回のINSERTが終わった後にCOMMIT TRANSACTIONを行います。

では、この時にtable2のレコードはどうなっているでしょうか。以下のクエリで見てみます。

SELECT * FROM table2

クエリの結果は以下になります。

result1

2回目のINSERTでエラーになっているのですが、1,3回目のINSERTは反映されています。つまりは、XACT_ABORTがOFFの場合には一部のクエリがエラーになってもそのまま処理が続けられます。なので、エラーハンドリングを自前で行う必要があります。

例えば今回の場合に、2回目のINSERTが失敗したら全てロールバックするには以下のようにTRY-CATCHで囲います。

SET XACT_ABORT OFF;
GO
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT INTO table2 VALUES (1);
    INSERT INTO table2 VALUES (2); -- 外部キー制約によりエラーになる
    INSERT INTO table2 VALUES (3);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
END CATCH
GO

XACT_ABORTがONの場合

では次にONの場合の動作を見ていきます。OFFの動作確認時のtable2のレコードを以下のクエリで削除します。

DELETE table2

以下のクエリで動作確認をします。

SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO table2 VALUES (1);
INSERT INTO table2 VALUES (2); -- 外部キー制約によりエラーになる
INSERT INTO table2 VALUES (3);
COMMIT TRANSACTION;
GO

クエリの内容としては1行目でXACT_ABORTをONにした以外はOFFの動作確認と同じです。このクエリを実行後に以下のクエリでtable2のレコードを見ます。

SELECT * FROM table2

取得できたレコードの件数は0件になります。つまりは、XACT_ABORTがONの場合にエラーが発生すると、その時点で処理が終わりロールバックされます。なので、ONの場合に自前でロールバックを行う必要はありません。

ストアド内での動作

XACT_ABORTがOFFの場合

XACT_ABORTがOFFで動作するストアドを作成します。

CREATE PROCEDURE TEST_PROC
AS
    SET XACT_ABORT OFF;
    BEGIN TRANSACTION;
    INSERT INTO table2 VALUES (2); -- 外部キー制約によりエラーになる
    IF @@ERROR = 0
    BEGIN
        COMMIT TRANSACTION;
        RETURN 0
    END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION;
        RETURN 1
    END
GO

table2へのINSERTの結果により返り値を変えています。本記事の環境ではtable1のcol1に2が入っているレコードが存在しないため、外部キー制約によるエラーになります。そのため、返り値は1となりロールバックされます。

作成したストアドを以下のクエリで実行します。

DECLARE @Return INT
EXEC @Return = TEST_PROC
SELECT  'Return Value' = @Return
GO

すると以下の結果になります。

result2

XACT_ABORTがONの場合

では次にXACT_ABORTの設定をONに変えます。

ALTER PROCEDURE TEST_PROC
AS
    SET XACT_ABORT ON;
    BEGIN TRANSACTION;
    INSERT INTO table2 VALUES (2); -- 外部キー制約によりエラーになる
    IF @@ERROR = 0
    BEGIN
        COMMIT TRANSACTION;
        RETURN 0
    END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION;
        RETURN 1
    END
GO

変更点は3行目のXACT_ABORTの設定をONに切り替えたところのみです。変更後のストアドを以下のクエリで実行します。

DECLARE @Return INT
EXEC @Return = TEST_PROC
SELECT  'Return Value' = @Return
GO

Return Valueが返ってこなくなります。これはXACT_ABORTの設定により処理が中断されたためです。返り値で1を返せるようにするには、@@ERRORでハンドリングしていたところをTRY-CATCHにする必要があります。TRY-CATCHに修正したストアドが以下になります。

ALTER PROCEDURE TEST_PROC
AS
    SET XACT_ABORT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        INSERT INTO table2 VALUES (2); -- 外部キー制約によりエラーになる
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        RETURN 1
    END CATCH
    COMMIT TRANSACTION;
    RETURN 0
GO

変更後のストアドを以下のクエリで実行します。

DECLARE @Return INT
EXEC @Return = TEST_PROC
SELECT  'Return Value' = @Return
GO

結果は以下のようになり、XACT_ABORTがOFFの時と同じように返り値が1になることが確認できます。

result3

このように、@@ERRORでハンドリングしている処理がある場合には修正が必要になります。

弊社では@@ERRORでハンドリングしているストアドがあり、ONにするタイミングで@@ERRORのハンドリングからTRY-CATCHを使ったハンドリングへ修正しました。

注意点

本記事ではSQL Server Management Studioからクエリを実行してXACT_ABORTの動作を確認しています。そのため、アプリケーションでトランザクションを張った場合などは異なる動作をすることがあります。各自の実行環境で動作確認してから導入してください。

また、外部キー制約時のエラーの動作を中心に記載しました。この動作はエラーの重要度レベルによって変わることがあります。エラーレベルが異なる場合も各自で動作を確認してみてください。エラーレベルに関しては以下を参照してください。

docs.microsoft.com

まとめ

本記事ではXACT_ABORTの動作について紹介しました。紹介した動作について以下の表にまとめます。

設定 エラー時の動作 ロールバック処理の記述 ストアド内でのハンドリング方法
OFF 発生した処理のみがロールバック 全ての処理をロールバックしたい場合は処理を書く必要がある @@ERRORでのエラーハンドリングが可能
ON トランザクション全体が終了しロールバック 自動でロールバックされるので書く必要がない TRY-CATCHでのエラーハンドリングが可能

最後に

カート決済部では負荷軽減の対策から、新機能開発、カート決済リプレイスなどを行っておりタスクが山積みの状態です。このような課題を一緒に進めていただける仲間を募集しています。ご興味のある方は以下のリンクから是非ご応募ください。

hrmos.co

hrmos.co

また、カジュアル面談も随時実施中です。是非ご応募ください。

hrmos.co

カテゴリー