Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Suppose I have an open SQL Server session, and do the following:

begin tran 

insert into People (Id) values (1)

select @@TRANCOUNT -- Prints 1

save transaction tt

begin tran

select @@TRANCOUNT -- Prints 2

insert into People (Id) values (2)

Ok, so now I make a:

rollback tran tt

select @@TRANCOUNT -- Prints 2!

My question is maybe obvious:

Why partial rollback doesn't decrement @@TRANCOUNT increased by the inner transaction?

To commit changes I should do two commit commands. IMHO doesn't seems natural.

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

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
share|improve this answer
    
Nice. I guess around this topics DBA code design start being delicate. I guess it's even more weird if that rollback is done without a name, making a rollback of the whole transaction. That last commit would result in an error (trancount would be 0 at that point). What would be a rule of thumb to handle that? –  Peter May 24 '13 at 1:27
add comment

@@TRANCOUNT reports a count of BEGIN TRANSACTION statements, not active transactions. From a different perspective, it is reporting the depth of a nested transaction.

@@TRANCOUNT (Transact-SQL) Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection. [source]

Misunderstandings of nested transactions and savepoint usage arise because nested transactions are not what we would expect them to be. To all intents and purposes there is no such thing as a nested transaction. The actions of a set of nested transactions are not committed until you issue the outer most COMMIT and a ROLLBACK will undo the actions of all.

Nested transactions exist to support transactions in procedures which could be called from external processes that may have started a parent transaction, from parent procedures that may have done the same, or where no prior transaction exists. They do not provide nested commit and rollback behaviour.

share|improve this answer
4  
+1 for "there is no such thing as a nested transaction." –  Thomas Stringer May 24 '13 at 1:50
    
Good point to remember. In general people think of nested transactions when the real concept in their minds are savepoints. –  Peter May 24 '13 at 2:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.