CODEWITHSUNDEEP
sqlsql-servertransactions
Ashis Kumar Panda
Ashis Kumar Panda

Reputation: 1

SQL Server transaction count

SELECT @@TRANCOUNT 
BEGIN TRAN T1    
SELECT @@TRANCOUNT  
    BEGIN TRAN T2               
    SELECT @@TRANCOUNT  
    ROLLBACK TRAN T2        
SELECT @@TRANCOUNT 
ROLLBACK TRAN T1   
SELECT @@TRANCOUNT

I want to discuss on @@trancount for a moment. There are five @@trancount. The first one has value 0 . The 2nd one has value 1. the third one has value 2.before the fourth trancount ,it encounters an error(as there is no save checkpoint for the rollback) so the value 2 will continue. Upto this I am getting the logic . Problem comes at the last @@trancount as it should also encounter an error (according to my half knowledge) as there is no savepoint for the rollback and hence it should retain the value 2. But instead of that it shows the output as 0. Please help me to know why the last trancount is 0 instead of 2.

Upvotes: 0

Views: 3174

Answers (2)

IVNSTN
IVNSTN

Reputation: 9334

You may try SAVE TRAN or rollback will affect entire transaction since there were no savepoints defined.

SELECT @@TRANCOUNT 
BEGIN TRAN t1
SELECT @@TRANCOUNT  
    BEGIN TRAN T2
    SAVE TRAN T2 --<<
    SELECT @@TRANCOUNT  
    ROLLBACK TRAN T2
SELECT @@TRANCOUNT 
ROLLBACK TRAN t1
SELECT @@TRANCOUNT

https://msdn.microsoft.com/en-us/library/ms187967.aspx

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.

Nice article: http://www.codeproject.com/Articles/4451/SQL-Server-Transactions-and-Error-Handling

Figure 1: A COMMIT always balances a BEGIN TRANSACTION by reducing the transaction count by one. enter image description here

Figure 2: A single ROLLBACK always rolls back the entire transaction. enter image description here

Savepoints offer a mechanism to roll back portions of transactions. A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. SQL Server allows you to use savepoints via the SAVE TRAN statement, which doesn't affect the @@TRANCOUNT value. A rollback to a savepoint (not a transaction) doesn't affect the value returned by @@TRANCOUNT, either. However, the rollback must explicitly name the savepoint: using ROLLBACK TRAN without a specific name will always roll back the entire transaction.

Upvotes: 1

Alex
Alex

Reputation: 21766

ROLLBACK TRAN transaction_name must refer to the outermost transaction name, otherwise it will fail. You can fix your script like this

SELECT @@TRANCOUNT 
BEGIN TRAN T1    
SELECT @@TRANCOUNT  
    BEGIN TRAN T2               
    SELECT @@TRANCOUNT  
    ROLLBACK TRAN T1
SELECT @@TRANCOUNT 
IF @@TRANCOUNT>0
   ROLLBACK TRAN T1   
SELECT @@TRANCOUNT

Please note that you can use COMMMIT TRAN T2

Upvotes: 0

Related Questions