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)


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.


Comments (5)

  1. Dileep says:

    Can you please explain it more.

    Thanks in Advance,


  2. JensS says:

    Hi Dileep,

    what information do you need in addition ? I simply means that if a transaction exists, this one will be used, thus not increasing the @@Trancount. On the other side, while doing a DML statement and NOT setting an explicit transaction, @@TRancount is increased as well during the execution of the statement,


  3. Dileep says:

    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

  4. Andy says:

    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?