SQL SERVER – Knowing Nested Transactions Behavior with SQL Server

SQL Server is an interesting database system that baffles me every single day that I get to learn something new. This blog has been my way to share these learnings. Every time I write something new, my blog readers have been kind enough in pushing me with interesting comments that I get to learn a lot more. I am a firm believer that learnings get enhanced every time share something.

This blog is an extension of what I wrote a couple of weeks back SQL SERVER – Error – Msg 6401, Level 16: Cannot roll back Transaction. One of my blog readers asked me, doesn’t SQL Server create or start a new transaction whenever we do these nested transactions? This was a valid question and understandable. I was thinking of a way to show the concept that SQL Server will not create any new transaction even when it is a nested format. Finally, I came up with this script to show the working.

We will create the script in the following sequence:

• Create our database and a dummy table
• Create a transaction and enter some value into the table
• Create a nested transaction and enter some value into the table
• Look at the Transaction Log and Transaction ID to see if they are any different
• Create a ROLLBACK operation and show what is entered in TLogs

-- Create our database for testing
USE MASTER;
GO
CREATE DATABASE TransactionsDemo;
GO
USE TransactionsDemo;
GO
-- Create a table for testing
CREATE TABLE tbl_SQLAuth (Col1 INT);
GO
SET NOCOUNT ON;
GO
ALTER DATABASE [TransactionsDemo] SET RECOVERY SIMPLE WITH NO_WAIT
GO
CHECKPOINT

Let us create the data into our table with transactions.

-- Create an explicit transaction and insert single row
BEGIN TRAN OuterTransaction;
GO
INSERT INTO tbl_SQLAuth VALUES (1);
GO
-- Create an explicit so called nested transaction and insert single row
BEGIN TRAN InnerTransaction;
GO
INSERT INTO tbl_SQLAuth VALUES (2);
GO


At this point, if you check the transaction log records – it shows an interesting note. This can be queried using the undocumented command fn_dblog().

-- Look at the TLogs for entries now.
SELECT Operation, [Transaction ID], Description FROM fn_dblog(NULL, NULL)


If you check the Transaction ID column, you can see the values for both the records point to the same transaction ID. So there is no difference when it comes to creating nested transactions.

To extend our understanding, let us try to rollback the transaction. This will rollback both the rows with a compensating record in the TLog. So let us see what happens.

-- Rollback the OuterTransaction
ROLLBACK TRAN OuterTransaction;
GO


Let us look at the TLog entries now.

As you can see both the rows have been rolled back using a COMPENSATION record for each of the rows that were inserted in the Transaction log. The final ABORT_XACT confirms the completion of our Transaction.

Do let me know if you have ever learnt the transaction context ever like this? It is sometimes fun when someone pushes us to learn the basics the hard way. Hope you like this blog and learnt something new today. Let us learn together.

Reference: Pinal Dave (https://blog.sqlauthority.com)