Detecting a transaction v7.0
SQL Server provides a global variable called @@TRANCOUNT. This variable is
helpful in determining what's happening within a current session.
Since @@TRANCOUNT is a session-specific global variable, all users modifying
data on the server have their own @@TRANCOUNT variable. These do not affect each
other when applying transactions to the database.
Wrapping your T-SQL statements in a BEGIN TRAN COMMIT/ROLLBACK TRAN is good
programming practice. However, each issuance of one of these commands affects
the @@TRANCOUNT variable.
Each time a BEGIN TRAN is issued, the @@TRANCOUNT variable will increment by
one. Issuing a COMMIT will decrement the @@TRANCOUNT variable by one.
When a ROLLBACK is issued, the @@TRANCOUNT variable will set itself back to
zero. However, when a ROLLBACK to a SAVEPOINT is issued (i.e., ROLLBACK
<savepoint_name>) the @@TRANCOUNT variable is unaffected.
You can quickly see how deep you are within your T-SQL statements by checking
the @@TRANCOUNT variable. Be sure to check your @@TRANCOUNT variable at regular
intervals, as this is an indicator that you may be holding resources/locks open
on your data.
Home Previous Next