Nested Transactions in TSQL

Question

Hi my current understanding of nested transactions in TSQL is that if you have multiple transactions (several transactions nested inside of one "outer" transaction), all of the tranasactions must be committed (with the "outer" transaction being the last one) for any changes to the database to be made. If the number of commits are less than the number of open transactions then no changes related to any of the transactions are made. Is this a correct overview of how nested transactions work?

Answer 1

Your description of COMMIT is correct.

Kalen Delaney has an article covering the same type of behavior that you describe.

However, as discussed in Kalen's article, a ROLLBACK within a nested transaction will rollback the entire outer transaction, not just the inner transaction where the rollback occurs.

Note the following results:

BEGIN TRAN
SELECT @@trancount
BEGIN TRAN
SELECT @@trancount
BEGIN TRAN
SELECT @@trancount

ROLLBACK TRAN
SELECT @@trancount

This is described in the MSDN article, Nesting Transactions:

A ROLLBACK WORK or a ROLLBACK TRANSACTION statement that does not have a transaction name rolls back all nested transactions and decrements @@TRANCOUNT to 0. A ROLLBACK TRANSACTION that uses the transaction name of the outermost transaction in a set of nested transactions rolls back all of the nested transactions and decrements @@TRANCOUNT to 0. When you are unsure if you are already in a transaction, SELECT @@TRANCOUNT to determine if it is 1 or more. If @@TRANCOUNT is 0, you are not in a transaction.

Answer 2

In short, your answer is yes. From Nesting Transactions:

Committing inner transactions is ignored by the SQL Server Database Engine. 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.

Regarding ROLLBACks, it is only permitted to ROLLBACK the entire outer transaction.