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.

 

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

Comments (5)

  1. Dileep says:

    Can you please explain it more.

    Thanks in Advance,

    Dileep

  2. 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,

    /Jens

  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?