Usage of @@Trancount in DML statements

 

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.

 

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

Comments

• Anonymous
  January 29, 2013
  good one

• Anonymous
  February 17, 2013
  Can you please explain it more. Thanks in Advance, Dileep

• Anonymous
  February 17, 2013
  The comment has been removed

• Anonymous
  February 18, 2013
  Hi Jeans, I didn't get why @@Trancount is set to 2 in a DML statement and also the difference between explict and implicit transactions in terms of @@Trancount. Thanks for your reply

• Anonymous
  February 11, 2014
  I still don't understand where the outside counts.  I understand 'outside cnt' = 0, but thing 'outside' should = 1.  Where does the 2 come from?