In what cases a transaction can be committed from inside the CATCH block when XACT_ABORT is set to ON?

It turns out that transaction can not be committed from inside the CATCH block if XACT_ABORT is set to ON.

The example from MSDN is somewhat misleading, because the check implies that XACT_STATE can return 1 in some cases and it may be possible to COMMIT the transaction.

IF (XACT_STATE()) = 1
BEGIN
    PRINT 'The transaction is committable.' + 
          ' Committing transaction.'
    COMMIT TRANSACTION;   
END;

It is not true, XACT_STATE will never return 1 inside CATCH block if XACT_ABORT is set to ON.

It seems that the MSDN sample code was meant to primarily illustrate the use of XACT_STATE() function regardless of the XACT_ABORT setting. The sample code looks generic enough to work with both XACT_ABORT set to ON and OFF. It is just that with XACT_ABORT = ON the check IF (XACT_STATE()) = 1 becomes unnecessary.

There is a very good detailed set of articles about Error and Transaction Handling in SQL Server by Erland Sommarskog. In Part 2 - Classification of Errors he presents a comprehensive table that puts together all classes of errors and how they are handled by SQL Server and how TRY ... CATCH and XACT_ABORT changes the behaviour.

+-----------------------------+---------------------------++------------------------------+
|                             |     Without TRY-CATCH     ||        With TRY-CATCH        |
+-----------------------------+-------+-------+-----+-----++------------------+-----+-----+
|              SET XACT_ABORT |  OFF  |  ON   | OFF | ON  ||    ON or OFF     | OFF | ON  |
+-----------------------------+-------+-------+-----+-----++------------------+-----+-----+
| Class Name                  |    Aborts     |   Rolls   ||    Catchable     |   Dooms   |
|                             |               |   Back    ||                  |transaction|
+-----------------------------+-------+-------+-----+-----++------------------+-----+-----+
| Fatal errors                |  Connection   |    Yes    ||       No         |    n/a    |
| Batch-aborting              |     Batch     |    Yes    ||       Yes        |    Yes    |
| Batch-only aborting         |     Batch     | No  | Yes ||       Yes        | No  | Yes |
| Statement-terminating       | Stmnt | Batch | No  | Yes ||       Yes        | No  | Yes |
| Terminates nothing at all   |    Nothing    |    No     ||       Yes        | No  | Yes |
| Compilation: syntax errors  |  (Statement)  |    No     ||       Yes        | No  | Yes |
| Compilation: binding errors | Scope | Batch | No  | Yes || Outer scope only | No  | Yes |
| Compilation: optimisation   |     Batch     |    Yes    || Outer scope only |    Yes    |
| Attention signal            |     Batch     | No  | Yes ||       No         |    n/a    |
| Informational/warning msgs  |    Nothing    |    No     ||       No         |    n/a    |
| Uncatchable errors          |    Varying    |  Varying  ||       No         |    n/a    |
+-----------------------------+-------+-------+-----+-----++------------------+-----+-----+

The last column in the table answers the question. With TRY-CATCH and with XACT_ABORT ON the transaction is doomed in all possible cases.

One note outside the scope of the question. As Erland says, this consistency is one of the reasons to set XACT_ABORT to ON:

I have already given the recommendation that your stored procedures should include the command SET XACT_ABORT, NOCOUNT ON. If you look at the table above, you see that with XACT_ABORT in effect, there is some higher level of consistency. For instance, the transaction is always doomed. In the following, I will show many examples where I set XACT_ABORT to OFF, so that you can get an understanding of why you should avoid this default setting.


TL;DR / Executive Summary: Regarding this part of the Question:

I don't see in what cases the control can be passed inside CATCH with a transaction that can be committed when XACT_ABORT is set to ON.

I have done quite a bit of testing on this now and I cannot find any cases where XACT_STATE() returns 1 inside of a CATCH block when @@TRANCOUNT > 0 and the session property of XACT_ABORT is ON. And in fact, according to the current MSDN page for SET XACT_ABORT:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

That statement appears to be in agreement with your speculation and my findings.

MSDN article about SET XACT_ABORT has an example when some statements inside a transaction execute successfully and some fail when XACT_ABORT is set to OFF

True, but the statements in that example are not within a TRY block. Those same statements within a TRY block would still prevent execution for any statements after the one that caused the error, but assuming that XACT_ABORT is OFF, when control is passed to the CATCH block the Transaction is still physically valid in that all of the prior changes did happen without error and can be committed, if that is the desire, or they can be rolled-back. On the other hand, if XACT_ABORT is ON then any prior changes are automatically rolled-back, and then you are given the choice to either: a) issue a ROLLBACK which is mostly just an acceptance of the situation since the Transaction was already rolled back minus resetting @@TRANCOUNT to 0, or b) get an error. Not much of a choice, is it?

One possibly important detail to this puzzle that is not apparent in that documentation for SET XACT_ABORT is that this session property, and that example code even, has been around since SQL Server 2000 (the documentation is nearly identical between the versions), predating the TRY...CATCH construct which was introduced in SQL Server 2005. Looking at that documentation again and looking at the example (without the TRY...CATCH), using XACT_ABORT ON causes an immediate roll-back of the Transaction: there is no Transaction state of "uncommittable" (please note that there is no mention at all of an "uncommittable" Transaction state in that SET XACT_ABORT documentation).

I think it is reasonable to conclude that:

  1. the introduction of the TRY...CATCH construct in SQL Server 2005 created the need for a new Transaction state (i.e. "uncommittable") and the XACT_STATE() function to get that information.
  2. checking XACT_STATE() in a CATCH block really only makes sense if both of the following are true:
  3. XACT_ABORT is OFF (else XACT_STATE() should always return -1 and @@TRANCOUNT would be all you need)
  4. You have logic in the CATCH block, or somewhere up the chain if the calls are nested, that makes a change (a COMMIT or even any DML, DDL, etc statement) instead of doing a ROLLBACK. (this is a very atypical use case) ** please see note at the bottom, in the UPDATE 3 section, regarding an unofficial recommendation by Microsoft to always check XACT_STATE() instead of @@TRANCOUNT, and why testing shows that their reasoning does not pan out.
  5. the introduction of the TRY...CATCH construct in SQL Server 2005 has, for the most part, obsoleted the XACT_ABORT ON session property as it provides for a greater degree of control over the Transaction (you at least have the option to COMMIT, provided that XACT_STATE() does not return -1).
    Another way to look at this is, prior to SQL Server 2005, XACT_ABORT ON provided an easy and reliable way to stop processing when an error occurred, as compared to checking @@ERROR after each statement.
  6. The documentation example code for XACT_STATE() is erroneous, or at best misleading, in that it shows checking for XACT_STATE() = 1 when XACT_ABORT is ON.

The long part ;-)

Yes, that example code on MSDN is a bit confusing (see also: @@TRANCOUNT (Rollback) vs. XACT_STATE) ;-). And, I feel it is misleading because it either shows something that makes no sense (for the reason that you are asking about: can you even have a "committable" transaction in the CATCH block when XACT_ABORT is ON), or even if it is possible, it still focuses on a technical possibility that few will ever want or need, and ignores the reason one is more likely to need it.

If there is a severe enough error inside the TRY block, the control will pass into CATCH. So, if I'm inside the CATCH, I know that transaction has had a problem and really the only sensible thing to do in this case is to roll it back, isn't it?

I think it would help if we made sure that we are on the same page regarding what is meant by certain words and concepts:

  • "severe enough error": Just to be clear, TRY...CATCH will trap most errors. The list of what will not be caught is listed on that linked MSDN page, under the "Errors Unaffected by a TRY…CATCH Construct" section.

  • "if I'm inside the CATCH, I know that transaction has had a problem" (emphasis added): If by "transaction" you mean the logical unit of work as determined by you by grouping statements into an explicit transaction, then most likely yes. I think most of us DB folks would tend to agree that rolling-back is "the only sensible thing to do" since we likely have a similar view of how and why we use explicit transactions and conceive of what steps should make up an atomic unit of work.

    But, if you mean the actual units of work that are being grouped into the explicit transaction, then no, you don't know that the transaction itself has had a problem. You only know that a statement executing within the explicitly defined transaction has raised an error. But it might not be a DML or DDL statement. And even if it was a DML statement, the Transaction itself might still be committable.

Given the two points made above, we should probably draw a distinction between transactions that you "can't" commit, and ones that you "don't want" to commit.

When XACT_STATE() returns a 1, that means that the Transaction is "committable", that you have a choice between COMMIT or ROLLBACK. You might not want to commit it, but if for some hard-to-even-come-up-with-an-example-for reason you wanted to, at least you could because some parts of the Transaction did complete successfully.

But when XACT_STATE() returns a -1, then you really need to ROLLBACK because some part of the Transaction went into a bad state. Now, I do agree that if control has been passed to the CATCH block, then it makes sense enough to just check @@TRANCOUNT, because even if you could commit the Transaction, why would you want to?

But if you notice at the top of the example, the setting of XACT_ABORT ON changes things a bit. You can have a regular error, after doing BEGIN TRAN that will pass control to the CATCH block when XACT_ABORT is OFF and XACT_STATE() will return 1. BUT, if XACT_ABORT is ON, then the Transaction is "aborted" (i.e. invalidated) for any 'ol error and then XACT_STATE() will return -1. In this case, it seems useless to check XACT_STATE() within the CATCH block as it always seems to return a -1 when XACT_ABORT is ON.

So then what is XACT_STATE() for? Some clues are:

  • MSDN page for TRY...CATCH, under the "Uncommittable Transactions and XACT_STATE" section, says:

    An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block.

  • The MSDN page for SET XACT_ABORT, under the "Remarks" section, says:

    When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.

    and:

    XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server.

  • The MSDN page for BEGIN TRANSACTION, under the "Remarks" section, says:

    The local transaction started by the BEGIN TRANSACTION statement is escalated to a distributed transaction if the following actions are performed before the statement is committed or rolled back:

    • An INSERT, DELETE, or UPDATE statement that references a remote table on a linked server is executed. The INSERT, UPDATE, or DELETE statement fails if the OLE DB provider used to access the linked server does not support the ITransactionJoin interface.

The most applicable usage seems to be within the context of Linked Server DML statements. And I believe I ran into this myself years ago. I don't remember all of the details, but it had something to do with the remote server not being available, and for some reason, that error did not get caught within the TRY block and never got sent to the CATCH and so it did a COMMIT when it shouldn't have. Of course, that could have been an issue of not having XACT_ABORT set to ON rather than failing to check XACT_STATE(), or possibly both. And I do recall reading something that said if you use Linked Servers and/or Distributed Transactions then you needed to use XACT_ABORT ON and/or XACT_STATE(), but I cannot seem to find that document now. If I do find it, I will update this with the link.

Still, I have tried several things and am unable to find a scenario that has XACT_ABORT ON and passes control to the CATCH block with XACT_STATE() reporting 1.

Try these examples to see the effect of XACT_ABORT on the value of XACT_STATE():

SET XACT_ABORT OFF;

BEGIN TRY
    BEGIN TRAN;

    SELECT 1/0 AS [DivideByZero]; -- error, yo!

    COMMIT TRAN;
END TRY
BEGIN CATCH
    SELECT @@TRANCOUNT AS [@@TRANCOUNT],
            XACT_STATE() AS [XactState],
            ERROR_MESSAGE() AS [ErrorMessage]

    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK;
    END;
END CATCH;

GO ------------------------------------------------

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRAN;

    SELECT 1/0 AS [DivideByZero]; -- error, yo!

    COMMIT TRAN;
END TRY
BEGIN CATCH
    SELECT @@TRANCOUNT AS [@@TRANCOUNT],
            XACT_STATE() AS [XactState],
            ERROR_MESSAGE() AS [ErrorMessage]

    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK;
    END;
END CATCH;

GO ------------------------------------------------

SET XACT_ABORT ON;

BEGIN TRY
    SELECT 1/0 AS [DivideByZero]; -- error, yo!
END TRY
BEGIN CATCH
    SELECT @@TRANCOUNT AS [@@TRANCOUNT],
            XACT_STATE() AS [XactState],
            ERROR_MESSAGE() AS [ErrorMessage]
END CATCH;

UPDATE

While not part of the original Question, based on these comments on this Answer:

I've been reading through Erland's articles on Error and Transaction Handling where he says that XACT_ABORT is OFF by default for legacy reasons and normally we should set it to ON.
...
"... if you follow the recommendation and run with SET XACT_ABORT ON, the transaction will always be doomed."

Prior to using XACT_ABORT ON everywhere, I would question: what exactly is being gained here? I have not found it necessary to do and generally advocate that you should use it only when necessary. Whether or not you want to ROLLBACK can be handle easily enough by using the template shown in @Remus's answer, or the one that I have been using for years that is essentially the same thing but without the Save Point, as shown in this answer (which handles nested calls):

Are we required to handle Transaction in C# Code as well as in stored procedure

UPDATE 2

I did a bit more testing, this time by creating a small .NET Console App, creating a Transaction in the app layer, prior to executing any SqlCommand objects (i.e. via using (SqlTransaction _Tran = _Connection.BeginTransaction()) { ... ), as well as using a batch-aborting error instead of just a statement-aborting error, and found that:

  1. An "uncommitable" Transaction is one that has been, for the most part, rolled back already (the changes have been undone), but @@TRANCOUNT is still > 0.
  2. When you have an "uncommitable" Transaction you cannot issue a COMMIT as that will generate and error saying that the Transaction is "uncommittable". You also cannot ignore it / do nothing as an error will be generated when the batch finishes stating that the batch completed with a lingering, uncommittable transaction and it will be rolled back (so, um, if it will auto-roll-back anyway, why bother throwing the error?). So you must issue an explicit ROLLBACK, maybe not in the immediate CATCH block, but before the batch ends.
  3. In a TRY...CATCH construct, when XACT_ABORT is OFF, errors that would terminate the Transaction automatically had they occurred outside of a TRY block, such as batch-aborting errors, will undo the work but not terminate the Tranasction, leaving it as "uncommitable". Issuing a ROLLBACK is more of a formality needed to close out the Transaction, but the work has already been rolled-back.
  4. When XACT_ABORT is ON, most errors act as batch-aborting, and hence behave as described in the bullet point directly above (#3).
  5. XACT_STATE(), at least in a CATCH block, will show a -1 for batch-aborting errors if there was an active Transaction at the time of the error.
  6. XACT_STATE() sometimes returns 1 even when there is no active Transaction. If @@SPID (among others) is in the SELECT list along with XACT_STATE(), then XACT_STATE() will return 1 when there is no active Transaction. This behavior started in SQL Server 2012, and exists on 2014, but I haven't tested on 2016.

With the above points in mind:

  • Given points #4 and #5, since most (or all?) errors will render a Transaction "uncommitable", it seems entirely pointless to check XACT_STATE() in the CATCH block when XACT_ABORT is ON since the value returned will always be -1.
  • Checking XACT_STATE() in the CATCH block when XACT_ABORT is OFF makes more sense because the return value will at least have some variation since it will return 1 for statement-aborting errors. However, if you code like most of us, then this distinction is meaningless since you will be calling ROLLBACK anyway simply for the fact that an error occurred.
  • If you find a situation that does warrant issuing a COMMIT in the CATCH block, then check the value of XACT_STATE(), and be sure to SET XACT_ABORT OFF;.
  • XACT_ABORT ON seems to offer little to no benefit over the TRY...CATCH construct.
  • I can find no scenario where checking XACT_STATE() provides a meaningful benefit over simply checking @@TRANCOUNT.
  • I can also find no scenario where XACT_STATE() returns 1 in a CATCH block when XACT_ABORT is ON. I think it is a documentation error.
  • Yes, you can roll-back a Transaction that you did not explicitly begin. And in the context of using XACT_ABORT ON, it's a moot point since an error happening in a TRY block will automatically roll-back the changes.
  • The TRY...CATCH construct has the benefit over XACT_ABORT ON in not automatically cancelling the whole Transaction, and hence allowing the Transaction (as long as XACT_STATE() returns 1) to be committed (even if this is an edge-case).

Example of XACT_STATE() returning -1 when XACT_ABORT is OFF:

SET XACT_ABORT OFF;

BEGIN TRY
    BEGIN TRAN;

    SELECT CONVERT(INT, 'g') AS [ConversionError];

    COMMIT TRAN;
END TRY
BEGIN CATCH
    DECLARE @State INT;
    SET @State = XACT_STATE();
    SELECT @@TRANCOUNT AS [@@TRANCOUNT],
            @State AS [XactState],
            ERROR_MESSAGE() AS [ErrorMessage];

    IF (@@TRANCOUNT > 0)
    BEGIN
        SELECT 'Rollin back...' AS [Transaction];
        ROLLBACK;
    END;
END CATCH;

UPDATE 3

Related to item #6 in the UPDATE 2 section (i.e. possible incorrect value returned by XACT_STATE() when there is no active Transaction):

  • The odd / erroneous behavior started in SQL Server 2012 (so far tested against 2012 SP2 and 2014 SP1)

  • In SQL Server versions 2005, 2008, and 2008 R2, XACT_STATE() did not report expected values when used in Triggers or INSERT...EXEC scenarios: xact_state() cannot be used reliably to determine whether a transaction is doomed (archived page). However, in these 3 versions (I only tested on 2008 R2), XACT_STATE() does not incorrectly report 1 when used in a SELECT with @@SPID.

  • There is was a Connect bug filed against the behavior mentioned here but is closed as "By Design": XACT_STATE() can return an incorrect transaction state in SQL 2012 (link no longer valid due to incompetent and/or grossly negligent site migration ). However, the test was done when selecting from a DMV and it was concluded that doing so would naturally have a system generated transaction, at least for some DMVs. It was also stated in the final response by MS that:

    Note that an IF statement, and also a SELECT without FROM, do not start a transaction.
    for example, running SELECT XACT_STATE() if you don't have a previously existing transaction will return 0.

    Those statements are incorrect given the following example:

      SELECT @@TRANCOUNT AS [TRANCOUNT], XACT_STATE() AS [XACT_STATE], @@SPID AS [SPID];
  GO
  DECLARE @SPID INT;
  SET @SPID = @@SPID;
  SELECT @@TRANCOUNT AS [TRANCOUNT], XACT_STATE() AS [XACT_STATE], @SPID AS [SPID];
  GO

  • Hence, I filed a new Feedback bug:
    XACT_STATE() returns 1 when used in SELECT with some system variables but without FROM clause

PLEASE NOTE that in the "XACT_STATE() can return an incorrect transaction state in SQL 2012" Connect item linked directly above, Microsoft (well, a representative of) states:

@@trancount returns the number of BEGIN TRAN statements. It is thus not a reliable indicator of whether there is an active transaction. XACT_STATE() also returns 1 if there is an active autocommit transaction, and is thus a more reliable indicator of whether there is an active transaction.

However, I can find no reason to not trust @@TRANCOUNT. The following test shows that @@TRANCOUNT does indeed return 1 in an auto-commit transaction:

--- begin setup
GO
CREATE PROCEDURE #TransactionInfo AS
SET NOCOUNT ON;
SELECT @@TRANCOUNT AS [TranCount],
       XACT_STATE() AS [XactState];
GO
--- end setup

DECLARE @Test TABLE (TranCount INT, XactState INT);

SELECT * FROM @Test; -- no rows

EXEC #TransactionInfo; -- 0 for both fields

INSERT INTO @Test (TranCount, XactState)
    EXEC #TransactionInfo;

SELECT * FROM @Test; -- 1 row; 1 for both fields

I also tested on a real table with a Trigger and @@TRANCOUNT within the Trigger did accurately report 1 even though no explicit Transaction had been started.


I would approach this differently. XACT_ABORT_ON is a sledge hammer, you can use a more refined approach, see Exception handling and nested transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end
go

This approach will rollback, when possible, only the work performed inside the TRY block, and restore state to the state before entering the TRY block. This way you can do complex processing, like iterating a cursor, w/o loosing all the work in case of an error. The only draw back is that, by using transaction savepoints, you are restricted from using anything that is incompatible with savepoints, like distributed transactions.

Tags:

Sql Server

Sql Server 2008

Recent Posts