SQL Server Interview Question #148

What is XACT_STATE()?

Transactions, Locking, Blocking & Deadlocks Senior Advanced

Detailed Explanation

XACT_STATE reports the state of the current user transaction.

A value of 1 means an active transaction exists and can be committed. A value of -1 means a transaction exists but is uncommittable and can only be fully rolled back. A value of 0 means there is no active user transaction.

XACT_STATE is particularly useful in TRY...CATCH transaction handling.

Code Example

BEGIN TRY
    BEGIN TRANSACTION;

    -- Database work here

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;