@@TRANCOUNT

Question

@@TRANCOUNT

dbgaragedays

Old Hand

Points: 358
More actions
October 1, 2019 at 9:57 pm

#3685150

The question is: What is @@trancount after this SP is executed? I don't understand why the answer is 1.
My understanding is for every BEGIN TRAN, @@TRANCOUNT is incremented by 1 and for every COMMIT, @@TRANCOUNT is decremented by 1, so why is the answer not 0?
I know it has to do with the nesting of transactions, but I just don't get it....
thanks
DBG

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

wellsy SSC Veteran Points: 203 More actions · Answer 1

Hi DBG

I see that you have a begin transaction out of the TRY loop and the last associated COMMIT within the TRY loop. In this scenario you could just have one transaction to cover all script and would ROLLBACK to start if any part of script failed. If you are utilising multiple transactions it also helps to name the TRANSACTION ie. BEGIN TRANSACTION TRAN1 and will help with script clarity.

Perhaps use DBCC OPENTRAN command to help troubleshoot the open transactions.

Also perhaps check that no existing transactions are open before you run your script

Hope that is of some help

Thanks

James

Steve Collins SSCrazy Points: 2152 More actions · Answer 2

I agree with James one transaction would likely be a good idea. To see how the trancount changes with successive trans/commits:

drop proc if exists temp_proc;
go
create proc temp_proc
as
begin
drop table if exists #temp;
create table #temp(colxbit null);

print ('Initial value: '+cast(@@trancount as varchar(9)));

begin transaction
begin try
print ('Tran1: '+cast(@@trancount as varchar(9)));

begin transaction
print ('Tran2: '+cast(@@trancount as varchar(9)));
--insert #temp(colx) select 1/0;
commit transaction
print ('Commit Tran2: '+cast(@@trancount as varchar(9)));

begin transaction
print ('Tran3: '+cast(@@trancount as varchar(9)));
--insert #temp(colx) select 1/0;
commit transaction
print ('Commit Tran3: '+cast(@@trancount as varchar(9)));

commit transaction
print ('Commit Tran1: '+cast(@@trancount as varchar(9)));
end try
begin catch
print ('Oops: '+cast(@@trancount as varchar(9)));
rollback;
end catch
print ('Final value: '+cast(@@trancount as varchar(9)));
end
go

exec temp_proc;

With the 1/0's commented out this returns:

Initial value: 0
Tran1: 1
Tran2: 2
Commit Tran2: 1
Tran3: 2
Commit Tran3: 1
Commit Tran1: 0
Final value: 0

With the 2nd of the 1/0's un-commented this returns:

Initial value: 0
Tran1: 1
Tran2: 2
Commit Tran2: 1
Tran3: 2

(0 rows affected)
Oops: 2
Final value: 0

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

I think what may be more important is to understand when to use transactions and how nested transactions affect the script. Typically you wouldn't put a single command within a transaction this way unless you wanted to verify the results somehow before committing.

Transactions are primarily used when you want to try to make multiple statements somewhat atomic, such that they all should happen or not happen. For example, If you have a command that removes money from account1 and a separate command that adds that money to account2, you wouldn't want either of those statements to succeed unless they BOTH succeed, so you would wrap them together in a transaction.

Here's a good article that discusses "nested transactions" and their behaviors in SQL Server:

https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/

The short answer is to avoid them!

Michael L John One Orange Chip Points: 27054 More actions · Answer 4

Do you realize that there really is no such thing as a nested transaction in SQL Server? The two inner begin/commit transactions will not be committed until the final commit is executed.

So, for example, if the first nested transaction completes successfully, and the second one throws an error, the entire batch will be rolled back.

The article cited above by Paul Randal will help immensely

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/