SQL with Manoj

There is no concept of Nested Transactions in SQL Server, Period.

There can be workarounds but Nested Transactions is not out of the box.

–> In my [previous post] we saw a scenario where you have a Nested Transaction. And we saw issues with Rolling back the inner Transaction and handling them gracefully with the Outer Transactions, which typically looked like this:

 
–> Here we will see how the nested Transactions behave internally by executing these SQL statements in chunks.

1. First of all we will create a sample table and execute the code till BEGIN outerTran section:

USE [tempdb]
GO

CREATE TABLE dbo.TranTest (ID INT)
GO

CHECKPOINT
GO

-- Outer Transaction - BEGIN
SELECT @@TRANCOUNT AS 'outerTran Begin', count(*) from dbo.TranTest -- 0, 0
BEGIN TRANSACTION outerTran

INSERT INTO dbo.TranTest values(1)
SELECT @@TRANCOUNT AS 'outerTran Begin', count(*) from dbo.TranTest -- 1, 1

– This was a simple one, one row got inserted with Transaction Count = 1, but its not committed yet.

– Let’s see the Transaction status by using the undocumented function fn_dblog(), how these are tracked in the DB Engine:

SELECT Operation, [Transaction ID], Description, Context
FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] IN (
	SELECT [Transaction ID] 
	FROM fn_dblog(NULL, NULL) 
	WHERE [Description] like '%Tran%')

As you can see in the output above:
– The Outer BEGIN TRANSACTION statement is logged as LOP_BEGIN_XACT Operation and Description = ‘outerTran;0x01…’ for the outer Transaction.
– and INSERT statement is logged as LOP_INSERT_ROWS operation.
 

2. Now let’s execute the next section with the inner Transaction:

	-- Inner Transaction
	BEGIN TRANSACTION innerTran

	INSERT INTO dbo.TranTest values(2)
	SELECT @@TRANCOUNT AS 'innerTran Begin', count(*) from dbo.TranTest -- 2, 2

	COMMIT TRANSACTION innerTran
	SELECT @@TRANCOUNT AS 'innerTran Rollback', count(*) from dbo.TranTest -- 1, 2

– The first SELECT statement before the COMMIT statement gives count of two for the inserted rows in the table, with Transaction Count = 2.
– And after COMMIT Transaction the second SELECT statement gives Transaction count = 1.

– Let’s execute the same function and see the Transaction status:

SELECT Operation, [Transaction ID], Description, Context
FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] IN (
	SELECT [Transaction ID] 
	FROM fn_dblog(NULL, NULL) 
	WHERE [Description] like '%Tran%')

– Here we don’t see any row for the inner Transaction with LOP_BEGIN_XACT Operation.
– A separate row is logged for the second INSERT statement as LOP_INSERT_ROWS operation with the same Transaction ID = ‘0000:00000992’.
Thus, this inner Transaction points to the Outer Transaction internally.
 

–> Now we will see what happens when we COMMIT or ROLLBACK the outer Transaction:

3.a. So, let’s COMMIT the outer Transaction first:

-- Outer Transaction - COMMIT
COMMIT TRANSACTION outerTran
SELECT @@TRANCOUNT AS 'outerTran Commit', count(*) from dbo.TranTest -- 0, 2
GO

SELECT * FROM dbo.TranTest -- 2
GO

– After the COMMIT statement the second SELECT statement gives Transaction count = 0, and there is no active Transaction left.
– And the final SELECT lists the 2 records inserted in the outer & inner Transactions.

Now again let’s execute the same function and see the Transaction status:

SELECT Operation, [Transaction ID], Description, Context
FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] IN (
	SELECT [Transaction ID] 
	FROM fn_dblog(NULL, NULL) 
	WHERE [Description] like '%Tran%')

– When COMMITTING the Outer Transaction it is logged as LOP_COMMIT_XACT Operation with the same Transaction ID = ‘0000:00000992’.
 

3.b. In case of ROLLBACK lets see what happens: You will need to execute all the SQL statements in Step 1 to 3 again.

-- Outer Transaction - COMMIT
COMMIT TRANSACTION outerTran
SELECT @@TRANCOUNT AS 'outerTran Commit', count(*) from dbo.TranTest -- 0, 2
GO

SELECT * FROM dbo.TranTest -- 2
GO

DROP TABLE dbo.TranTest
GO

Now again let’s execute the same function and see the Transaction status:

SELECT Operation, [Transaction ID], Description, Context
FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] IN (
	SELECT [Transaction ID] 
	FROM fn_dblog(NULL, NULL) 
	WHERE [Description] like '%Tran%')

As the outer Transaction is ROLLEDBACKED the inner Transaction also also gets Rollebacked, and thus you can see:
– two DELETE logged rows for the two INSERTed rows above, with LOP_DELETE_ROWS Operation and Description = COMPENSATION.
– and final ROLLBACK log with LOP_ABORT_XACT Operation with the same Transaction IDs.

 
The above exercise shows us that SQL Server only tracks the outermost Transaction, and do not bother about the inner Transactions.

–> So what’s the Hidden Secret?

1. First one is what we saw above, no Nested Transactions.

2. COMMIT TRANSACTION has an option to apply the Transaction name, but the DB Engine simply ignores it and points to the previous BEGIN TRANSACTION statement. Thus while issuing a COMMIT TRANSACTION referencing the name of an outer transaction when there are outstanding inner transactions it only decrements the @@TRANCOUNT value by 1.

3. ROLLBACK TRANSACTION also have an option to apply the Transaction name, but you can only apply the outermost Transaction name in case of Nested Transactions. While using ROLLBACK in inner Transactions you have to use either just ROLLBACK TRANSACTION or ROLLBACK TRANSACTION SavePoint_name, only if the inner transaction are created with SAVE TRANSACTION option instead of BEGIN TRANSACTION.

4. ROLLBACK TRANSACTION SavePoint_name does not decrement @@TRANCOUNT value.

 
Thus it is advised be careful while using Nested Transactions, ROLLBACKS and SavePoints, or just simply ignore them.