BEGIN TRANSACTION | SQL with Manoj

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

Categories: SQL Errors Tags: BEGIN TRANSACTION, Msg 3902, Msg 6401, Nested Transactions, ROLLBACK TRANSACTION, SAVE TRANSACTION, SAVEPOINT

SQL Server’s weird behavior in error handling with transactions

December 6, 2011 2 comments

In my previous post I mentioned that SQL statements inside the BEGIN & COMMIT TRANSACTION block do not auto rollback the batch if an error occurs, and SET XACT_ABORT ON is required for auto rollbacking the transaction batch in such cases.

As I mentioned earlier in my previous post that by default the SET XACT_ABORT is OFF. There ar very few cases where the T-SQL statement that raised exception are rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF.

But When SET XACT_ABORT is ON, if a T-SQL statement raises an exception, then the entire transaction is terminated and rolled back.

But there is an exception for some errors, like for error level 16, example: Datatype Conversion error, the transaction batch is automatically rolled back.

–> Let’s check this with an example below:

-- Create a table and insert test some records
CREATE TABLE X (NBR INT PRIMARY KEY)

INSERT INTO X VALUES(1)
INSERT INTO X VALUES(2)

-- Tran t1 : PK violation error
BEGIN TRANSACTION T1
	INSERT INTO X VALUES(3)
	INSERT INTO X VALUES(3) -- Msg 2627, Level 14, State 1, Line 9, Violation of PRIMARY KEY constraint...
	INSERT INTO X VALUES(4)
COMMIT TRANSACTION T1

SELECT * FROM X
-- Results 4 records inserted above, the entire transaction batch was not rolled back, but only the 2nd INSERT statement.

-- To automatically rollback above transaction batch we will set XACT_ABORT to ON:
SET XACT_ABORT ON

BEGIN TRANSACTION T1
	INSERT INTO X VALUES(3)
	INSERT INTO X VALUES(3) -- Msg 2627, Level 14, State 1, Line 9, Violation of PRIMARY KEY constraint...
	INSERT INTO X VALUES(4)
COMMIT TRANSACTION T1

SET XACT_ABORT OFF

SELECT * FROM X
-- No results, Entire transaction batch is rolled back.

--// But for some types of errors setting the XACT_ABORT to ON is not required. Like for Datatype Conversion errors (level = 16). Let's check this:

-- Tran t2: Datatype conversion error
BEGIN TRANSACTION T2
	INSERT INTO X VALUES(5)
	INSERT INTO X VALUES('a') -- Msg 245, Level 16, State 1, Line 19, Conversion failed...
	INSERT INTO X VALUES(6)
COMMIT TRANSACTION T2

SELECT * FROM X
-- No results, Entire transaction batch is rolled back.

-- Final Cleanup
DROP TABLE X

Erland Sommarskog in his tech-site lists and distinguish such error types: http://www.sommarskog.se/error-handling-I.html#statementbatch

I also discussed this topic in MSDN TSQL forum & here is the link.