"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION" - Error encountered in SQL Server
During development of stored procedures, I ran into one issue and this was due to not applying best practice of stored procedures, practice and this issue come mostly when we call stored procedures inside another one and so on, which are used in nested in transactions
So I would like to write the next post for the stored procedures development, as how we should write it, It will be a just a template of best practice. In this post I will explain the details of the issue which I suffered and how can we resolve it. Let's for the demonstration we will create one table and stored procedures.
In first stored we have written a code to raise an error and due to transaction issue while running second stored procedure it raised the error. This is not best practice as we have written only "Begin Transaction". We should specify the name of the stored procedure.
# 1 Solution : Let's specify the name of transactions in both of the stored procedures and altering stored procedures here and execute it again with same.
You can see here the error is not raised which is raided in the first case. Here stored procedure find and commit/rollback correct and their transactions only.
# 2 Solution : Let's look another alternative method which can also help to come out from the issue.
The rewritten stored procedures,
Let me know what you think over here. You also encountered this issue in the past, Please share your experience and alternation solution.
So I would like to write the next post for the stored procedures development, as how we should write it, It will be a just a template of best practice. In this post I will explain the details of the issue which I suffered and how can we resolve it. Let's for the demonstration we will create one table and stored procedures.
-- Creating table which will be used in SPs. CREATE TABLE tbl_Tran ( TranId INT NOT NULL PRIMARY KEY ,TranName VARCHAR(10) ) GO -- Creating first stored procedure. CREATE PROCEDURE Firttranproc AS BEGIN SET NOCOUNT ON BEGIN TRY BEGIN TRANSACTION -- Inserting records in table, -- Due to this insert error will be raised. INSERT INTO tbl_Tran (TranId ,TranName) SELECT 1 ,'Tran-1' UNION ALL SELECT 1 ,'Tran-1' COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION PRINT 'Rollback Tran1' END CATCH END GO -- Creating Second stored procedure CREATE PROCEDURE Secondtranproc AS BEGIN SET NOCOUNT ON BEGIN TRY BEGIN TRANSACTION INSERT INTO tbl_Tran (TranId ,TranName) SELECT 2 ,'Tran-2' -- Calling first created stored procedure here. EXEC Firttranproc COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION PRINT 'Rollback Tran2' END CATCH END GO -- Executing second stored procedure here and see what happen. EXEC Secondtranproc GO
In first stored we have written a code to raise an error and due to transaction issue while running second stored procedure it raised the error. This is not best practice as we have written only "Begin Transaction". We should specify the name of the stored procedure.
# 1 Solution : Let's specify the name of transactions in both of the stored procedures and altering stored procedures here and execute it again with same.
-- Altering first stored procedure here ALTER PROCEDURE Firttranproc AS BEGIN SET NOCOUNT ON -- Here we have specified Tran1 as transaction name BEGIN TRY BEGIN TRANSACTION Tran1 INSERT INTO tbl_Tran (TranId ,TranName) SELECT 1 ,'Tran-1' UNION ALL SELECT 1 ,'Tran-1' COMMIT TRANSACTION Tran1 END TRY BEGIN CATCH PRINT 'Rollback Tran1' ROLLBACK TRANSACTION Tran1 END CATCH END GO -- Altering second stored procedure here ALTER PROCEDURE Secondtranproc AS BEGIN SET NOCOUNT ON -- Here we have specified Tran2 as transaction name BEGIN TRY BEGIN TRANSACTION Tran2 -- Inserting records INSERT INTO tbl_Tran (TranId ,TranName) SELECT 2 ,'Tran-2' -- Calling first stored procedure here EXEC Firttranproc COMMIT TRANSACTION Tran2 END TRY BEGIN CATCH PRINT 'Rollback Tran2' ROLLBACK TRANSACTION Tran2 END CATCH END GO -- Executing second stored procedure which will also call of SP1 EXEC Secondtranproc GO
You can see here the error is not raised which is raided in the first case. Here stored procedure find and commit/rollback correct and their transactions only.
# 2 Solution : Let's look another alternative method which can also help to come out from the issue.
The rewritten stored procedures,
-- Altering first stored procedure here ALTER PROCEDURE Firttranproc AS BEGIN SET NOCOUNT ON -- Here we have specified Tran1 as transaction name BEGIN TRY BEGIN TRANSACTION INSERT INTO tbl_Tran (TranId ,TranName) SELECT 1 ,'Tran-1' UNION ALL SELECT 1 ,'Tran-1' COMMIT TRANSACTION END TRY BEGIN CATCH PRINT 'Rollback Tran1' -- This statement first check open transaction for their session -- If found then will rollback it. IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION END CATCH END GO -- Altering second stored procedure here ALTER PROCEDURE Secondtranproc AS BEGIN SET NOCOUNT ON BEGIN TRY BEGIN TRANSACTION -- Inserting records INSERT INTO tbl_Tran (TranId ,TranName) SELECT 2 ,'Tran-2' -- Calling first stored procedure here EXEC Firttranproc COMMIT TRANSACTION END TRY BEGIN CATCH PRINT 'Rollback Tran2' -- This statement first check open transaction for their session -- If found then will rollback it. IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION END CATCH END GO -- Executing second stored procedure which will also call of SP1 EXEC Secondtranproc GOHere is the same output as second case, it is also not raised the error.
Let me know what you think over here. You also encountered this issue in the past, Please share your experience and alternation solution.
I am sorry Paresh, but both your proposed solutions are not completely correct.
ReplyDeleteIn your solution 1; did you ever wonder why you hit the catch block in the calling proc? I.e., there is still something wrong - starting a tran with a name does not alter anything in principle, you are not allowed to roll back atran down in the calling chain. Named savepoints is different, but you are not using a named savepoint here.
In solution 2, you are doing the same mistake, you are rolling back a tran you haven't started - and you end up in the catch block in the calling proc. The only reason you are not getting an error raised in the calling proc is because you are looking for the @@trancount variable, which by the time you hit the catch-block is 0.
To ge some more info (and a better way of doing this look at my blogpost at: http://bit.ly/owH84t