Advertisements

Archive

Posts Tagged ‘Nested Transactions’

SQL Myth | Nested Transactions in SQL Server and hidden secrets

May 26, 2015 Leave a comment

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:

Nested Trsansaction

 
–> 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%')

Nested Trsansaction fb_dblog 01

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%')

Nested Trsansaction fb_dblog 02

– 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%')

Nested Trsansaction fb_dblog 03a
– 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%')

Nested Trsansaction fb_dblog 03b
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.


Advertisements

SQL error – Msg 6401, Level 16, State 1 – Cannot roll back Transaction. No transaction or savepoint of that name was found

May 25, 2015 1 comment

Yesterday in a SQL forum I saw a question regarding this error, and the person who had asked this question was finding it difficult to fix it.

He had a scenario where the Outer Transaction has an Inner Transaction, as shown below. And he was trying to ROLLBACK the inner Transaction and was getting an error for this similar SQL batch that I’ve tried to simulated here:

–> I’ve created a sample table let’s executed the whole below:

USE [tempdb]
GO

CREATE TABLE dbo.TranTest (ID INT)
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

	-- Inner Transaction
	BEGIN TRANSACTION innerTran

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

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

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

SELECT * FROM dbo.TranTest -- 2
GO

– You can see the count of rows inserted in the comments with every SELECT statement.
– But the ROLLBACK TRANSACTION innerTran statement at Line 20 fails with following error:
Msg 6401, Level 16, State 1, Line 20
Cannot roll back innerTran1. No transaction or savepoint of that name was found.

– And thus the last SELECT statement still gives Transaction count = 2, as this Transaction is not Rollbacked and neither committed.

–> NOTE: With the ROLLBACK TRANSACTION statement as per MS BoL you can only specify the name of the outermost BEGIN TRANSACTION statement. So, how can we make this code fail-proof?
 

There are 2 ways to solve this:
1. Just use ROLLBACK TRANSACTION without the name of any transaction.
2. Use SAVE TRANSACTION instead of BEGIN TRANSACTION for inner Transactions.
 

Option #1: Using just ROLLBACK TRANSACTION statement:

-- 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

	-- Inner Transaction
	BEGIN TRANSACTION innerTran

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

	ROLLBACK TRANSACTION -- here, removed inner Transaction name
	SELECT @@TRANCOUNT AS 'innerTran Rollback', count(*) from dbo.TranTest -- 0, 0

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

SELECT * FROM dbo.TranTest -- 0
GO

– This ROLLBACK statement rollbacks the whole Transaction resulting no rows in dbo.TransTest table.
– But the COMMIT TRANSACTION outerTran statement at Line 20 fails with following error:
Msg 3902, Level 16, State 1, Line 24
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

 

Option #2: Using SAVE TRANSACTION instead of BEGIN TRANSACTION

-- 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

	-- Inner Transaction
	SAVE TRANSACTION innerTran -- here, created a SavePoint

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

	ROLLBACK TRANSACTION innerTran -- Rolling back a SavePoint
	SELECT @@TRANCOUNT AS 'innerTran Rollback', count(*) from dbo.TranTest -- 1, 1

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

SELECT * FROM dbo.TranTest -- 1
GO

As you can see that we have converted the Inner Transaction to a Save Point, as as per the error we’ve taken care of this with option #2.

In this case a SavePoint was created and you can Rollback a SavePoint inside the outer Transaction. Thus the outer transaction was finally committed with table having value “1” and inner SavePoint was Rollbacked with row having value “2”.

COMMIT vs ROLLBACK