Usage of @@Trancount in DML statements

  • Article

 

Some time ago I got an interesting questions why the following applies:

 --drop table a
create table a(m varchar(max), i int)

go

declare @cnt int
--Explicit transaction
begin tran
set @cnt=@@trancount
insert into a select 'inside', @@trancount
insert into a select 'inside cnt', @cnt
commit tran

set @cnt=@@trancount

--Implicit transcation
insert into a select 'outside', @@trancount
insert into a select 'outside cnt', @cnt

 select * from a

The output is:

inside 2
inside cnt 1

outside 2
outside cnt 0

So why is that? The reason is that the trancount counts the user transaction as well as the inner transaction. You can also see that if you take a look at the transaction log for that. I a DML statement it will be always at least 2.

 

clip_image002

You see that the inner transaction is reusing the outer transaction in case one exists. So if you really need the information of @@TRANCOUNT, you should think about putting that in a local variable first.

The script of the picture below can be downloaded here.

-Jens