@@Trancount and XACT_STATE()

Recently, I’ve encountered code that includes both @@Trancount and XACT_STATE().

 

XACT_STATE() is a scalar function that gives the user transaction state of a current running request. It indicates whether the request has an active user transaction, and whether the transaction is capable of being committed or not.

XACT_STATE returns the following three values

• 1: 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.

So before commit or rollback always test XACT_STATE for 0, 1, or -1.

• If 1, the transaction is committable.
• If -1, the transaction is uncommittable and should be rolled back.
• If 0, there is no transaction and a commit or rollback operation will generate an error.

 

Below is the difference between them

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.

URL:  http://www.advancesharp.com/blog/1017/sql-transaction-status-and-xact-state