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.


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
    rollback tran tt
    select @@TRANCOUNT as TranCount -- Prints 2!
    insert into #TodaysFruit (fruit) values ('Banana')
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

| 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

@@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.

| 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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