Does SQL Server Truly Supports Nested Transaction ??

Explicit transactions can be nested in Sql server. But the question is does Sql server truly support nested transaction?? The answer is No. I know you will not believe me and the percentage of not believing will be very high if you are a sql server developer. Am I right :) Ok don't worry as when I stared learning SQL server I was also assuming the things as you are doing. But later on when I started reading books I got clear myself on this. Today morning while reviewing the code of a store procedure i found the use nested transactions which was little bit confusing. So i though to write something on this.

Transact-SQL allows you to nest transaction operations by issuing nested BEGIN TRAN commands. By using @@TRANCOUNT we can see the level of nesting transactions. If

@@TRANCOUNT returns 0 that means level of nesting—0 (no nested transaction), 1 indicates nesting one level deep, and so on.

A COMMIT issued against any transaction except the outermost one doesn't commit any changes to disk—it merely decrements the @@TRANCOUNT automatic variable.

A ROLLBACK, on the other hand, works regardless of the level at which it is issued but rolls back all transactions, regardless of the nesting level. 

There's a very good reason for it. If a nested COMMIT actually wrote changes permanently to disk, an outer ROLLBACK wouldn't be able to reverse those changes since they would already be recorded permanently. Likewise, if ROLLBACK didn't reverse all changes at all levels, calling it from within stored procedures and triggers would be vastly more complicated since the caller would have to check return values and the transaction nesting level when the routine returned in order to determine whether it needed to roll back pending transactions.

Let create an environment and then we will verify the same in sql server.

USE MASTER

GO

CREATE DATABASE NESTTRAN

GO

ALTER DATABASE NESTTRAN SET RECOVERY SIMPLE 

--Put Database in simple recovery mode

GO

USE NESTTRAN

GO

CREATE TABLE PRODUCT(ID INT IDENTITY PRIMARY KEY,NAME CHAR(23));

GO

CHECKPOINT --flush the changes to Disk

GO

Let see the result of transaction log using fn_dblog().

Execute: Select * from fn_dblog(null,null)

From screen shot it is clear that there are Only two records.

Now we will insert few records in explicit transaction mode and then we will examine transaction log using the function fn_dblog().

BEGIN TRAN OUTERTRAN

GO

INSERT INTO PRODUCT VALUES('P1')

GO 5

Now run the Query SELECT [current LSN],operation,[Transaction ID], [Transaction Name] FROM FN_DBLOG(NULL,NULL). You will get the output as below:

Notice that the transaction name is outertran as we have given name to our outer most transaction. Now run an inner transaction as below and then we will see the log changes:

BEGIN TRAN INNERTRAN

GO

INSERT INTO PRODUCT VALUES('P2')

GO 5

Now execute SELECT [current LSN],operation,[Transaction ID], [Transaction Name] FROM FN_DBLOG(NULL,NULL)  to see the changes in log file.

From the above screen shot it is clear that no new transaction Id or Transaction name is generated. It means sql is putting all the things within a single transaction. No nested stuffs.

Now lets commit the inner transaction and then we will see the change in log file. Before that just check the output of @@Trancount.

SELECT @@TRANCOUNT

GO

It will return 2. But internally there is only one transaction. Now commit inner tran as below and check the value of @@Trancount. :

COMMIT TRAN INNERTRAN

Go

SELECT @@TRANCOUNT

GO

This time It will return 1.

 Although it reduces the value of @@trancount but committing inner tran does not make any change in log file. We can verify the same from here:

SELECT [current LSN],operation,[Transaction ID], [Transaction Name] FROM FN_DBLOG(NULL,NULL)

No new change in log file. Means committing inner tran gives you the message of successfully committed tran but it does not actually commits the transaction. 

Now  if you are rolling back the OuterTran only, it will rollback entire transaction i.e. OuterTran as well as InnerTran. SO, If your inner tran was actually committed by "commit tran InnerTran" then how it got rollbacked??? It happened because it was not actually committed.

You can verify the same by executing:

Rollback Tran OUTERTRAN

GO

It will rollback all 10 rows. You can verify the same by selecting the records from table or from log change using fn_dblog().

Now lets commit the the outer tran and then we will see the changes in log file as below:

COMMIT TRAN OUTERTRAN

Go

Now execute:  

SELECT [current LSN],operation,[Transaction ID], [Transaction Name] FROM FN_DBLOG(NULL,NULL)

A new row is inserted against the command commit transaction outertran. It means the transaction with id 0000:000002ac is committed. 

So, I think its makes clear that there is such nested transaction exist in sql server.

Issue a checkpoint to flush all changes to disk. Let see what will happen in case of rollack of a nested transaction.

BEGIN TRAN OUTERTRAN

GO

INSERT INTO PRODUCT VALUES('P1')

GO 5

BEGIN TRAN INNERTRAN

GO

INSERT INTO PRODUCT VALUES('P2')

GO 5

Now try to rollback InnerTran as below:

Rollback TRAN INNERTRAN

Go

Ooopss!!! We got error “Cannot roll back INNERTRAN. No transaction or savepoint of that name was found.”

Because there is no nested transaction created in database. Now lets try to rollback the outertran:

Rollback TRAN OUTERTRAN

Go

All the rows from both transaction OUTERTRAN as well as INNERTRAN has been rollbacked.

I hope it helps you to understand that SQL server does not support nested transaction. 

Your Comments/Suggestions are welcome!!

References: The Guru's Guide to Transact-SQL: Ken Henderson, Paul Randal Blog