Sql-server – Reconciling @@TRANCOUNT

sql server

Running SS2005. I have a script that calls procedureA which in turn calls procedureB.

procedureA has no explicit transactions. procedureB is split up into several small blocks of non-nested BEGIN and COMMIT TRANSACTION.

I have peppered procedureB with PRINT statements to show me the value of @@TRANCOUNT. It's always at 0 or 1.

But when I check master..sysprocesses it shows open_tran at 1 or 2. Where this extra transaction is coming from?

I am posting the 2 procedures below. pr_Get12Relatives has been stripped of most of its content for clarity as it contains 1600 lines! It just repeats the same behavior as it searches for different types of relationships.

procedureA = pr_GetRelatives

procedureB = pr_Get12Relatives

Best Answer

This thread answers my question..

When a DML statement is currently being executed in a session the open_tran and @@trancount is always at least 2.

For example

Connection 1

CREATE TABLE T( X INT PRIMARY KEY)

GO

BEGIN TRAN

INSERT INTO T VALUES(1)

WAITFOR DELAY '00:01'

ROLLBACK

Connection 2

INSERT INTO T VALUES(1) /*Blocks on Connection 1*/

Connection 3

(look at blocked connection 2)

SELECT lastwaittype,
       open_tran
FROM   master..sysprocesses
WHERE  spid = <spid of connection 2>

Results

+----------------------------------+-----------+
|           lastwaittype           | open_tran |
+----------------------------------+-----------+
| LCK_M_X                          |         2 |
+----------------------------------+-----------+

Related Solutions

Sql-server – Reconciling dm_os_windows_info windows_sku values, gathering same info from pre SQL Server 2008 R2 instances

You can obtain most of the info from your screen shot from xp_msver. It goes back as far as SQL 2005. The other information might be in sys.configurations: http://msdn.microsoft.com/en-us/library/ms188345.aspx

Sql-server – Partial rollback doesn’t decrement trancount

This behaviour is described in the Microsoft documentation on ROLLBACK TRANSACTION:

ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.

Because a ROLLBACK TRAN can exist in the middle of some complex logic, parts of your code might not even be sure whether one was executed or not, making it difficult to figure out whether you need to issue a COMMIT.

As a result, it seems logical that we should have to close the transactions ourselves.

The following (bad) example might help illustrate:

set nocount on
begin transaction

create table #TodaysFruit (fruit varchar(10));

select @@TRANCOUNT as TranCount -- Prints 1

insert into #TodaysFruit (fruit) values ('Apple')

save transaction tt
begin transaction

insert into #TodaysFruit (fruit) values ('Orange')

select @@TRANCOUNT as TranCount -- Prints 2

if MONTH(GETDATE())=5 -- Oranges aren't seasonal in May
begin
    rollback tran tt
    select @@TRANCOUNT as TranCount -- Prints 2!
    insert into #TodaysFruit (fruit) values ('Banana')
end
insert into #TodaysFruit (fruit) values ('Mandarin')
commit -- I have no idea whether the above IF code was just executed

select @@TRANCOUNT as TranCount -- Prints 1

select fruit from #TodaysFruit

rollback