XACT_STATE (Transact-SQL)

Is a scalar function that reports the user transaction state of a current running request. XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.

The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.

0

There is no active user transaction for the current request.

-1

The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.

When a batch finishes running, the Database Engine will automatically roll back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This message indicates that an uncommittable transaction was detected and rolled back.

Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. XACT_STATE cannot be used to determine whether there are nested transactions.

The following example uses XACT_STATE in the CATCH block of a TRY…CATCH construct to determine whether to commit or roll back a transaction. Because SET XACT_ABORT is ON, the constraint violation error causes the transaction to enter an uncommittable state.