先日、仕事でXACT_STATEを使用したコードを見かけたので、調べてみました。（この読み方については「SQLServer XACT 読み方」の記事をご覧ください。）
XACT_STATE()は返り値としてそのトランザクションの状態を返し、主に例外処理で使用されます。
| 返り値 | 意味 |
|---|---|
| 1 | トランザクションが開かれている。コミット可能。 |
| 0 | トランザクションが開かれていない。 |
| -1 | トランザクションが開かれているが、コミット不可能。完全ロールバックのみ可能。 |
例外が発生した際のトランザクションの状態としては以下の3つが考えられます。
①トランザクションが開いていない場合
（XACT_STATE() = ‘0’）
→コミットもロールバックもしない。
②「トランザクションが開いている」かつ「コミット可能」
（XACT_STATE() = ‘1’）
→コミットしてそこまでの実行結果を反映させるか、ロールバックしてすべて巻き戻す。
③「トランザクションが開いている」かつ「コミット不可能」
（XACT_STATE() = ‘-1’）
→ロールバックしてすべて巻き戻す。
XACT_STATE()はこの分岐を実現するために使用されます。
SQLserver内部でトランザクションを開いた場合、以下のような例外処理となります。なお、内部でトランザクションを開いている場合は、CATCH内でXACT_STATE() = ‘0’である場合の分岐は不要です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
BEGIN TRY BEGIN TRANSACTION; -- 何らかの処理（ここでエラーが発生するとする。） ・・・ COMMIT TRANSACTION; END TRY BEGIN CATCH -- トランザクションが開いており、コミット可能な場合 -- （エラーが起きてもエラー箇所以外はコミット可能な場合があります。） IF (XACT_STATE()) = '1' BEGIN COMMIT; // またはROLLBACK END -- トランザクションが開いているが、コミット不可能な場合 IF (XACT_STATE()) = '-1' BEGIN -- コミットできないので、トランザクションをロールバックします。 ROLLBACK; END ・・・ログ出力など END CATCH |
次に、ストアドプロシージャを想定します。トランザクション制御を外部（C#プログラムなど）で行っている場合があるので、COMMITやROLLBACKは内部で行いません。以下は、処理結果をステータスコード「成功→2、失敗→XACT_STATE」で返す場合のプログラムです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
BEGIN TRY -- 何らかの処理（ここでエラーが発生するとする。） ・・・ -- 成功した場合、2を返す。 RETURN(2); END TRY BEGIN CATCH ・・・ストアドログ出力 // ↑外部でロールバックする場合、このログ出力もロールバックされてしまいます。 // それを避けるために、ストアドログ出力はSQL-CLRを用いて別コネクションとして行うことがあります。 // XACT_STATEの結果を返します。 RETURN(CONVERT(int, XACT_STATE())); END CATCH |
TRY内の最後では、成功したという結果２を返し、CATCH内の最後では、XACT_STATE()の結果を返します。この処理を呼び出した側のプログラムでは、この結果を使用して、コミットするかロールバックするかなどの処理の分岐を行います。
SET XACT_ABORT ONについて
XACT_STATE()に関連する、XACT_ABORT ONについて、説明します。Microsoft Documentでは、SET XACT_ABORT ONについて、以下のように述べています。
SET XACT_ABORT が ON の場合は、Transact-SQL ステートメントで実行時エラーが発生すると、トランザクション全体が終了し、ロールバックされます。
https://docs.microsoft.com/ja-jp/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-ver15
また、XACT_ABORTがOFFの場合、実行時エラーが発生しても、エラーの原因となったステートメント以外はコミットによってDBに反映できます。しかし、OFFの状態でも重大なエラーが生じた場合は、強制的に完全ロールバックされます。XACT_ABORTがOFFで、XACT_STATE()が-1を返すのはそのような重大なエラーの時のみとなります。これについては、参考文献②に詳しく載っています。
XACT_ABORTがONの場合、参照制約などの実行時エラーが生じるだけでも、完全ロールバックを強制するので、コミット不可になります。以下、Microsoft Documentの引用です。
Because
https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql?view=sql-server-ver15SET XACT_ABORTisON, the constraint violation error causes the transaction to enter an uncommittable state.
訳すと、「XACT_ABORTがONなので、制約違反エラーによってトランザクションはコミット不可状態になります。」です。逆に、XACT_ABORTがOFFであれば、制約違反ごときでは、コミット不可状態にはならないということです。
結論として、以下のことが言えます。
① XACT_ABORTのON・OFFにかかわらず、XACT_STATE()が-1を返す（コミット不可状態になる）ことはある
② XACT_ABORTがONの場合のほうが、XACT_STATE()が-1を返す条件が多い（重大でないエラーでも、コミット不可状態になる）
参考
① XACT_STATE (Transact-SQL) – SQL Server | Microsoft Docs
② Errors Raised with Severity/Level 16 May Cause Transactions into Doomed State