@@TRANCOUNT (Transact-SQL)

Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

Topic link icon Transact-SQL Syntax Conventions

Syntax

@@TRANCOUNT  

Note

This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.

Note

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Return Types

integer

Remarks

The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.

Examples

A. Showing the effects of the BEGIN and COMMIT statements

The following example shows the effect that nested BEGIN and COMMIT statements have on the @@TRANCOUNT variable.

PRINT @@TRANCOUNT  
--  The BEGIN TRAN statement will increment the  
--  transaction count by 1.  
BEGIN TRAN  
    PRINT @@TRANCOUNT  
    BEGIN TRAN  
        PRINT @@TRANCOUNT  
--  The COMMIT statement will decrement the transaction count by 1.  
    COMMIT  
    PRINT @@TRANCOUNT  
COMMIT  
PRINT @@TRANCOUNT  
--Results  
--0  
--1  
--2  
--1  
--0  

B. Showing the effects of the BEGIN and ROLLBACK statements

The following example shows the effect that nested BEGIN TRAN and ROLLBACK statements have on the @@TRANCOUNT variable.

PRINT @@TRANCOUNT  
--  The BEGIN TRAN statement will increment the  
--  transaction count by 1.  
BEGIN TRAN  
    PRINT @@TRANCOUNT  
    BEGIN TRAN  
        PRINT @@TRANCOUNT  
--  The ROLLBACK statement will clear the @@TRANCOUNT variable  
--  to 0 because all active transactions will be rolled back.  
ROLLBACK  
PRINT @@TRANCOUNT  
--Results  
--0  
--1  
--2  
--0  

See Also

BEGIN TRANSACTION (Transact-SQL)
COMMIT TRANSACTION (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL)
System Functions (Transact-SQL)