A coworker was having trouble with a stored proc. There were several IF statements where one path created another nested level transaction while the other did not. Trying to get the stored proc to COMMIT only certain transactions by name was sometimes generating an error that there was no corresponding BEGIN TRANSACTION. What we learned was the following.
Whenever you execute a COMMIT TRANSACTION statement, any transaction name after the statement is ignored. The only thing a COMMIT TRANSACTION statement does is reduce the @@trancount variable by 1. If this makes @@trancount = 0, then all database modifications are committed.
For example, say you have the following code (from SQL Server Books Online):
CREATE TABLE TestTran (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
BEGIN TRANSACTION OuterTran -- @@TRANCOUNT set to 1.
GO
INSERT INTO TestTran VALUES (1, 'aaa')
GO
BEGIN TRANSACTION Inner1 -- @@TRANCOUNT set to 2.
GO
INSERT INTO TestTran VALUES (2, 'bbb')
GO
BEGIN TRANSACTION Inner2 -- @@TRANCOUNT set to 3.
GO
INSERT INTO TestTran VALUES (3, 'ccc')
GO
COMMIT TRANSACTION Inner2 -- Decrements @@TRANCOUNT to 2.
-- Nothing committed.
-- ROLLBACK TRANSACTION Inner1
GO
COMMIT TRANSACTION Inner1 -- Decrements @@TRANCOUNT to 1.
-- Nothing committed.
GO
COMMIT TRANSACTION OuterTran -- Decrements @@TRANCOUNT to 0.
-- Commits outer transaction OuterTran.
GO
DROP TABLE TestTran
Just copy and paste this code into Query Analyzer. Add Select statements wherever you would like to see the state of the TestTran table and whether the statements have been committed or not.
The only transaction name that SQL Server cares about is OuterTran. It’s fine to label Inner1 and Inner2 transactions for other developers, but SQL Server does not use them. Also, the COMMIT TRANSACTION statement does not use the transaction name. Only a ROLLBACK uses the transaction name, and only the outermost transaction name. For example, trying to do ROLLBACK TRANSACTION Inner1 where it is commented out in the code snippet above would not work.
UPDATE: From BOL, thanks to Frans for pointing this out to me. It makes intuitive sense, and is the confirmed behavior.
Committing inner transactions is ignored by Microsoft® SQL Server™. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.
Each call to COMMIT TRANSACTION or COMMIT WORK applies to the last executed BEGIN TRANSACTION. If the BEGIN TRANSACTION statements are nested, then a COMMIT statement applies only to the last nested transaction, which is the innermost transaction. Even if a COMMIT TRANSACTION transaction_name statement within a nested transaction refers to the transaction name of the outer transaction, the commit applies only to the innermost transaction.
It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all the nested transactions, including the outermost transaction.
