Sean Lange (8/9/2016)
No discussion about nested transactions would be complete without a reminder that they are really a myth and do not do what it seems like a real nested transaction should. http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/[/url]
The idea that SQL Server doesn't support nested transactions is the myth. When it does allow them the nest can have only one transaction at each level; only the outermost transaction uses "begin transaction" and "commit transaction" commands (unfortunately SQL Server doesn't enforce this, it allows meaningless begin transaction and commit transaction commands at levels other than the outermost, and this cause pointless confusion); inner transactions use "save transaction" with a mandatory name parameter to begin a transaction, and "rollback transaction <name>" to roll back the nest to the point immediately before the beginning of the named transaction; only the outermost transaction can be committed. To rollback the whole nest the rollback transaction command can either specify the name of the outermost transaction (specified in the begin transaction statement) or not provide a name, it doesn't matter which it uses as both mean the same.
The idea of commiting an inner transaction is just plain bizarre, so perhaps people should apply a little common sense and realise that anything that uses "commit" for inner transactions is not a transaction nest, it's just a counter (and it's this same bizarreness of inner commit that precludes more than one subtransaction at the same inner level as the T-SQL language doesn't express any parellism).
Perhaps things would have looked much cleaner if begin transaction had not been allowed in an in-transaction execution context, commit transaction had not allowed a (meaningless) name parameter, and the command for beginng an inner transaction had been named something like "begin subtransaction" instead of save transaction.
So there are two myths here:
Myth 1: using "begin transaction" inside a transaction creates a nested transaction
Myth 2: SQL Server doesn't support nested transactions
The big problem is that people keep on claiming that Myth 1 being a Myth means that Myth 2 is not a myth, and that's just plain silly.