SQL Server TRY...CATCH with XACT_STATE

Question

I have a question regarding the MSDN documentation for TRY CATCH blocks. Check out this article and scroll down to Example C "Using TRY…CATCH with XACT_STATE"

http://msdn.microsoft.com/en-us/library/ms175976.aspx

The example first places a COMMIT TRANSACTION within the Try block, and then places a second one in the Catch block if XACT_STATE()=1.

However I thought a Catch block will only execute in case of an error. So how could both the Catch block execute and XACT_STATE return 1? That seems contradictory.

There is an unanswered comment within the XACT_STATE documentation which asks this same question

http://msdn.microsoft.com/en-us/library/ms189797.aspx

Answer 1

@user1181412 My analysis is as follows: This comment:

-- A FOREIGN KEY constraint exists on this table.

--This statement will generate a constraint violation error

is the answer to your question. What is happening is that when the DELETE statement executes, it generates a constraint violation error and the subsequent COMMIT does not execute. The XACT_STATE of the transaction is now 1 and the CATCH block is executing.

At the top, you have

SET XACT_ABORT ON;

This causes the transaction state to be uncommittable and hence this code block will rollback the transaction:

-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
    PRINT
        N'The transaction is in an uncommittable state.' +
        'Rolling back transaction.'
    ROLLBACK TRANSACTION;
END;

However, if you change to "SET XACT_ABORT OFF;" then the CATCH block would be hit albeit the transaction state will be "committable" as XACT_STATE = 1.

NOTE: Delete would still not be done as the constraint violation is still there, but you would see this printed:

(1 row(s) affected) The transaction is committable.Committing transaction.