Freigeben über

Usage of @@Trancount in DML statements

  • Artikel

 

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

  • 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?