Compare SQL Server XACT_STATE vs @@TRANCOUNT Functions

By:   |   Updated: 2024-08-14   |   Comments   |   Related: > Transactions


Problem

When working with transactions, identifying active transactions often becomes quite important. In SQL Server, there are XACT_STATE and @@TRANCOUNT system functions that allow one to get information about active user transactions. However, the question about the differences between these functions is one of the most common questions among database development. Understanding the details of each function and their differences will help developers make the correct choice between these functions to solve a specific problem related to transactions.

Solution

This article will compare the XACT_STATE and @@TRANCOUNT functions and illustrate their differences with examples. The goal is to reduce the confusion between these two functions, allowing for the correct function to be applied to particular tasks.

XACT_STATE()

XACT_STATE() is a scalar system function showing the user transaction state of the current session.

It returns three possible values:

  • 1 – When the current request has an active, committable user transaction.
  • 0 – When the current request does not have an active user transaction.
  • -1 – When the current request has an active user transaction, but it is classified as an uncommittable transaction due to the error.

@@TRANCOUNT

The @@TRANCOUNT function returns the number of open transactions in the current connection. In other words, each BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. In turn, COMMIT TRANSACTION (WORK) statement decrements it by 1. Although a ROLLBACK statement sets the value of @@TRANCOUNT to 0, rolling back to a savepoint (ROLLBACK TRANSACTION <savepoint_name>) does not change its value.

Comparing XACT_STATE() and @@TRANCOUNT

Before comparing these two system functions with examples, let's first create a test environment. The following code creates a TestDB database and a table in it called TestTable with the primary key and a unique index on the Val column: 

USE master
GO
 
--Creating TestDB database
CREATE DATABASE TestDB
GO
 
--Creating a table in TestDB database
USE TestDB
GO
 
CREATE TABLE TestTable
(
   ID INT PRIMARY KEY,
   Val INT
)
GO
 
--Creating unique index on Val column
CREATE UNIQUE INDEX UIX_TestTable_Val ON TestTable (Val)
GO

In the example below, the values of @@TRANCOUNT and XACT_STATE are illustrated inside the same request:

USE TestDB
GO
 
BEGIN TRY
 
   BEGIN TRANSACTION
 
   --Values before COMMIT
   SELECT XACT_STATE() AS 'XACT_STATE before COMMIT'
   SELECT @@TRANCOUNT AS '@@TRANCOUNT before COMMIT'
 
   INSERT INTO TestTable(ID, Val)
   VALUES(1, 100)
 
   COMMIT
   SELECT 'The transaction is committed' AS 'Transaction Status'
 
   --Values after COMMIT
   SELECT XACT_STATE() AS 'XACT_STATE after COMMIT'
   SELECT @@TRANCOUNT AS '@@TRANCOUNT after COMMIT'
 
END TRY
BEGIN CATCH
   
   --Values before ROLLBACK
   SELECT XACT_STATE() AS 'XACT_STATE before ROLLBACK'
   SELECT @@TRANCOUNT AS '@@TRANCOUNT before ROLLBACK'
 
   IF @@TRANCOUNT > 0
      ROLLBACK
   SELECT 'The transaction is rolled back' AS 'Transaction Status'
 
   --Values after ROLLBACK
   SELECT XACT_STATE() AS 'XACT_STATE after ROLLBACK'
   SELECT @@TRANCOUNT AS '@@TRANCOUNT after ROLLBACK'
 
END CATCH

As we can see, immediately after opening the transaction, both have the same value – 1. This is because the BEGIN TRANSACTION statement increments @@TRANCOUNT by 1, and there is an active user transaction that makes XACT_STATE() return 1. The COMMIT statement decrements @@TRANCOUNT by one, so it becomes 0 again. After that statement, there is no active user transaction in the current connection, so XACT_STATE() also returns 0:

query results

However, the value of @@TRANCOUNT was 1 as there was an open transaction regardless of its committable or uncommittable state. The errors described above were handled by the CATCH block. Nevertheless, there are special errors that cannot be tackled by the CATCH block.

Let's explore such a situation in the next example. In the code below, we are trying to insert the data into the temporary table which does not exist: 

USE TestDB
GO
 
BEGIN TRY
 
   BEGIN TRANSACTION
 
   --Values before COMMIT
   SELECT XACT_STATE() AS 'XACT_STATE before COMMIT'
   SELECT @@TRANCOUNT AS '@@TRANCOUNT before COMMIT'
 
   INSERT INTO #TestTable(ID, Val)
   VALUES(1, 100)
 
   COMMIT
   SELECT 'The transaction is committed' AS 'Transaction Status'
 
   --Values after COMMIT
   SELECT XACT_STATE() AS 'XACT_STATE after COMMIT'
   SELECT @@TRANCOUNT AS '@@TRANCOUNT after COMMIT'
 
END TRY
BEGIN CATCH
   
   --Values before ROLLBACK
   SELECT XACT_STATE() AS 'XACT_STATE before ROLLBACK'
   SELECT @@TRANCOUNT AS '@@TRANCOUNT before ROLLBACK'
 
   IF XACT_STATE()<>0
      ROLLBACK
   SELECT 'The transaction is rolled back' AS 'Transaction Status'
 
   --Values after ROLLBACK
   SELECT XACT_STATE() AS 'XACT_STATE after ROLLBACK'
   SELECT @@TRANCOUNT AS '@@TRANCOUNT after ROLLBACK'
 
END CATCH
 
 
/* 
--Values after error
SELECT XACT_STATE() AS 'XACT_STATE after ERROR'
SELECT @@TRANCOUNT AS '@@TRANCOUNT after ERROR'
 
ROLLBACK
 
--Values after error
SELECT XACT_STATE() AS 'XACT_STATE after ROLLBACK'
SELECT @@TRANCOUNT AS '@@TRANCOUNT after ROLLBACK'
*/

The error will be generated which cannot be caught in the CATCH block:

Error not caught by catch block

Therefore, we will have selected the values of XACT_STATE() and @@TRANCOUNT only before the error:

Values before the error

This means that we have an open transaction in the current session. If we run the code in the commented section in the same query window, we will see that the values of these functions are 1 after the error, and after the ROLLBACK, they became 0:

query results

In contrast, if we use SET XACT_ABORT ON at the beginning of the query, we will avoid having uncommitted transactions even in cases when such errors cannot be handled by the CATCH block. This is because when XACT_STATE is ON, the transaction is rolled back automatically in case of a run-time error:

USE TestDB
GO
 
SET XACT_ABORT ON
 
BEGIN TRY
 
   BEGIN TRANSACTION
 
   --Values before COMMIT
   SELECT XACT_STATE() AS 'XACT_STATE before COMMIT'
   SELECT @@TRANCOUNT AS '@@TRANCOUNT before COMMIT'
 
   INSERT INTO #TestTable(ID, Val)
   VALUES(1, 100)
 
   COMMIT
   SELECT 'The transaction is committed' AS 'Transaction Status'
 
   --Values after COMMIT
   SELECT XACT_STATE() AS 'XACT_STATE after COMMIT'
   SELECT @@TRANCOUNT AS '@@TRANCOUNT after COMMIT'
 
END TRY
BEGIN CATCH
   
   --Values before ROLLBACK
   SELECT XACT_STATE() AS 'XACT_STATE before ROLLBACK'
   SELECT @@TRANCOUNT AS '@@TRANCOUNT before ROLLBACK'
 
   IF XACT_STATE()<>0
      ROLLBACK
   SELECT 'The transaction is rolled back' AS 'Transaction Status'
 
   --Values after ROLLBACK
   SELECT XACT_STATE() AS 'XACT_STATE after ROLLBACK'
   SELECT @@TRANCOUNT AS '@@TRANCOUNT after ROLLBACK'
 
END CATCH
 
/*
--Values after error
SELECT XACT_STATE() AS 'XACT_STATE after ERROR'
SELECT @@TRANCOUNT AS '@@TRANCOUNT after ERROR'
 
ROLLBACK
 
--Values after error
SELECT XACT_STATE() AS 'XACT_STATE after ROLLBACK'
SELECT @@TRANCOUNT AS '@@TRANCOUNT after ROLLBACK'
*/

In the example above, we again received the same error – "Invalid object name ##TestTable." But, if we run the code in the comment, we will see that there is no open transaction:

Error message

Therefore, the ROLLBACK statement was not issued, and the values of XACT_STATE() and @@TRANCOUNT, which are 0, indicate that the transaction has already been rolled back:

Transaction has been rolled back

Now, it's time to investigate the values of XACT_STATE() and @@TRANCOUNT in the case of nested transactions. In the following example, we have a transaction inside another transaction:

USE TestDB
GO
 
BEGIN TRY
 
   BEGIN TRANSACTION
 
   --Values in the outer transaction
   SELECT XACT_STATE() AS 'XACT_STATE in the outer transaction'
   SELECT @@TRANCOUNT AS '@@TRANCOUNT in the outer transaction'
 
   INSERT INTO TestTable(ID, Val)
   VALUES(2, 200)
 
      BEGIN TRANSACTION
            --Values in the inner transaction
            SELECT XACT_STATE() AS 'XACT_STATE in the inner transaction'
            SELECT @@TRANCOUNT AS '@@TRANCOUNT in the inner transaction'
 
            INSERT INTO TestTable(ID, Val)
            VALUES(3, 300)
      COMMIT
      SELECT 'The inner transaction is committed' AS 'Outer Transaction Status'
 
      --Values after COMMITTING the inner transaction
      SELECT XACT_STATE() AS 'XACT_STATE after COMMITTING the inner transaction'
      SELECT @@TRANCOUNT AS '@@TRANCOUNT after COMMITTING the inner transaction'
 
   COMMIT
   SELECT 'The outer transaction is committed' AS 'Outer Transaction Status'
 
   --Values after COMMITTING the outer transaction
   SELECT XACT_STATE() AS 'XACT_STATE after COMMITTING the outer transaction'
   SELECT @@TRANCOUNT AS '@@TRANCOUNT after COMMITTING the outer transaction'
 
END TRY
BEGIN CATCH
   
   --Values before ROLLBACK
   SELECT XACT_STATE() AS 'XACT_STATE before ROLLBACK'
   SELECT @@TRANCOUNT AS '@@TRANCOUNT before ROLLBACK'
 
   IF XACT_STATE()<>0
      ROLLBACK
   SELECT 'The transaction is rolled back' AS 'Transaction Status'
 
   --Values after ROLLBACK
   SELECT XACT_STATE() AS 'XACT_STATE after ROLLBACK'
   SELECT @@TRANCOUNT AS '@@TRANCOUNT after ROLLBACK'
 
END CATCH

After starting the second transaction, the value of @@TRANCOUNT was incremented by 1 and became 2, but the value of XACT_STATE() remained 1:

Nested transactions

Thus, @@TRANCOUNT can be used to detect the nesting level of transactions. XACT_STATE() function, however, in case of having active, committable user transactions, always returns 1 regardless of the nesting level. The ROLLBACK statement sets the values of both @@TRANCOUNT and XACT_STATE() to 0 except rolling back to a savepoint, which affects neither the value of @@TRANCOUNT nor XACT_STATE().

Conclusion

To sum up, XACT_STATE() and @@TRANCOUNT can be used to detect active user transactions. Unlike @@TRANCOUNT, XACT_STATE() cannot be used to detect and count nested transactions. Additionally, while it is possible to detect uncommittable transactions using XACT_STATE(), it is not possible to do the same using @@TRANCOUNT. After the ROLLBACK statement, both values are set to 0 (with the exception of rolling back to a savepoint, which does not change the values of these functions).

Next Steps

For additional information, please follow the links below:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2024-08-14

Comments For This Article

















get free sql tips
agree to terms