By: Sergey Gigoyan | 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](/tipimages2/8059_sql-server-xact-state-vs-trancount-1.png)
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](/tipimages2/8059_sql-server-xact-state-vs-trancount-2.png)
Therefore, we will have selected the values of XACT_STATE() and @@TRANCOUNT only before the error:
![Values before the error](/tipimages2/8059_sql-server-xact-state-vs-trancount-3.png)
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](/tipimages2/8059_sql-server-xact-state-vs-trancount-4.png)
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](/tipimages2/8059_sql-server-xact-state-vs-trancount-5.png)
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](/tipimages2/8059_sql-server-xact-state-vs-trancount-6.png)
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](/tipimages2/8059_sql-server-xact-state-vs-trancount-7.png)
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:
- @@TRANCOUNT (Transact-SQL) - SQL Server | Microsoft Learn
- XACT_STATE (Transact-SQL) - SQL Server | Microsoft Learn
- Transactions (Transact-SQL) - SQL Server | Microsoft Learn
About the author
![MSSQLTips author Sergey Gigoyan](/images/Sergey-Gigoyan-2.jpg)
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