In SQL Server you can’t nest transactions. The transactional syntax allows for you to create more than one transaction, and it appears that more than one transaction is running. In fact, the transaction count returned by the system property @@TRANCOUNT will increase with each transaction. However, the first transaction completion command closes all open transactions. Here is an example.
SELECT @@TRANCOUNT as TransactionCount
BEGIN TRAN
SELECT @@TRANCOUNT as TransactionCount
BEGIN TRAN
SELECT @@TRANCOUNT as TransactionCount
ROLLBACK
SELECT @@TRANCOUNT as TransactionCount
Running this query you get the following results
TransactionCount = 0
TransactionCount = 1
TransactionCount = 2
TransactionCount = 0
The transaction count starts at 0 for the first select statement. After the first BEGIN TRAN the count increments. After the second BEGIN TRAN the count increments again to 2. However, with only a single ROLLBACK command, all of the transactions are rolled back, and the transaction is reset to 0.
If you need to nest transactions, with the intention to create segregated units of work within a transaction, you do have the option to use a Save Point. This is supported in many of the different SQL Engines. A Save Point looks much like a transaction. However, you can nest a Save Point inside of a transaction. Rolling back the commands inside a Save Point to the beginning of the Save Point does not roll back the entire transaction. Additional commands must follow, including a commit or rollback for the outer transaction. If you do have a save point, and call rollback without specifying the save point when calling rollback, then everything is rolled back to the outer most transaction.
It is interesting that a save point cannot participate in a distributed transaction, according to MSDN. This is a good article on the integration of Transactions with Save Points. They show a good TSQL example of how a single stored procedure can behave differently if it is the first SQL being executed, in contrast to being called from somewhere else where a transaction has already been started. If called first, it begins a transaction. If called later, it begins a save point. Then, if an error occurs, it knows if it should rollback a complete transaction or to a save point.
I do want to clarify that this discussion is completely based on the managing of transactions within your TSQL code. Predicting the behavior when integrating this with Distributed Transactions is more difficult to describe and understand. From my experience it is easiest to manage your transaction in a single layer, rather than have part of it in your database, and part of it in your client.
Cheers,
Ben