XACT_STATE() in SQL Server transaction

XACT_STATE() is a transaction health-check function in SQL Server. Unlike @@TRANCOUNT, which only tells whether a transaction exists, XACT_STATE() tells whether that transaction is still committable. It returns 1 for a valid transaction, 0 when no transaction exists, and -1 when the transaction is doomed or uncommittable. Internally, SQL Server marks a transaction as doomed after certain runtime errors to preserve ACID properties and prevent inconsistent data. Therefore, production-grade error handling always checks XACT_STATE() in the CATCH block before deciding whether to COMMIT or ROLLBACK.`

XACT_STATE return 3 values 1,0 or -1.

Return Value	Transaction State	Description
1	Active & Committable	A transaction exists and can be either COMMITTED or ROLLED BACK.
0	No Active Transaction	No user transaction is currently active.
-1	Active but Uncommittable (Doomed)	A transaction exists but cannot be committed. Only ROLLBACK is allowed.

XACT_STATE() = 1

Ø  Transaction Exists

Ø  Transaction Healthy

Ø  Can Commit

Ø  Can Rollback

XACT_STATE() = 0

Ø  No Active Transaction

XACT_STATE() = -1

Ø  Transaction Exists

Ø  Transaction Failed

Ø  Cannot Commit

Ø  Only Rollback Allowed

Ø  Doomed Transaction

Ø  Uncommittable Transaction

The primary purpose of XACT_STATE() is to determine whether the current transaction can still be committed or must be rolled back after an error occurs.

Let’s see the demo

Creating a table and inserting few records.

CREATE TABLE Employee (     EmployeeID INT PRIMARY KEY,     EmployeeName VARCHAR(50) ); GO INSERT INTO Employee VALUES (1,'Bagesh');

Table created successfully.

XACT_STATE()=0

Currently we don’t have any open transaction. So, the value of XACT_STATE =0.

Now inserting the valid record in the table.

Before inserting the record. Data in tables.

Now inserting the records.

BEGIN TRY     BEGIN TRAN;     INSERT INTO Employee VALUES (2,'Rohan');     INSERT INTO Employee VALUES (3,'Sohan');     SELECT XACT_STATE() AS TransactionState;      if XACT_STATE()=1     COMMIT;     else     ROLLBACK; END TRY BEGIN CATCH     IF @@TRANCOUNT > 0         ROLLBACK;     THROW; END CATCH; select * from Employee

After successfully insert the value of XACT_State is 1. In case of error, it will return -1.

Now I am going to insert few records

BEGIN TRY     BEGIN TRAN;     INSERT INTO Employee VALUES (2,'Rohan');     INSERT INTO Employee VALUES (3,'Sohan');     SELECT XACT_STATE() AS TransactionState;     if XACT_STATE()=1     COMMIT;  END TRY BEGIN CATCH  SELECT         ERROR_MESSAGE() AS ErrorMessage,         @@TRANCOUNT AS TranCount,         XACT_STATE() AS XactState;     IF XACT_STATE() <> 0         ROLLBACK;   END CATCH; select * from Employee

Here we are seeing the value of XACT_STATE =1 after our transaction failed.

A Primary Key violation does not automatically make a transaction uncommittable. With the default SET XACT_ABORT OFF, only the failing statement is rolled back, so XACT_STATE() remains 1. XACT_STATE() becomes -1 only when SQL Server marks the transaction as doomed, commonly under SET XACT_ABORT ON or severe runtime errors.

Now I am setting the XACT_ABORT OFF and see.