SQL Server Interview Question #146

What is @@TRANCOUNT?

Transactions, Locking, Blocking & Deadlocks Senior Advanced

Detailed Explanation

@@TRANCOUNT returns the number of BEGIN TRANSACTION statements that have occurred on the current connection minus the number of corresponding COMMIT operations, subject to SQL Server's transaction semantics.

It is useful when writing procedures that may execute inside an existing transaction. A ROLLBACK without a savepoint rolls back the entire transaction and resets @@TRANCOUNT to zero, while a COMMIT of a nested transaction mainly decrements the count until the outermost transaction is committed.

Code Example

BEGIN TRANSACTION;
SELECT @@TRANCOUNT AS TransactionCount;

COMMIT;
SELECT @@TRANCOUNT AS TransactionCount;