The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Posted on : 12-01-2012 | By : Devi Prasad | In : SQL Errors, SQLServerPedia Syndication

Share:

1


While using transactions in SQL Server sometimes you get the following errors:

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Cause For this error:
There is no transaction in progress but the COMMIT/ROLLBACK TRANSACTION request is issued.

Replication of the scenario:

BEGIN TRAN
COMMIT TRAN
COMMIT TRAN

In the above query the first COMMIT TRAN statement has commited the transaction started by BEGIN TRAN, When the second COMMIT TRAN statement is executed the error The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION occurs.

Same is the case for ROLLBACK TRAN in the below query.

BEGIN TRAN
ROLLBACK TRAN
ROLLBACK TRAN 

Fix For this error:

As a best practice always check for open transactions before issuing COMMIT TRAN or ROLLBACK TRAN statement.
@@TRANCOUNT can be used to find the number of open transactions.

IF(@@TRANCOUNT>0)
COMMIT TRAN
IF(@@TRANCOUNT>0)
ROLLBACK TRAN

This way the commit/rollback tansaction error can be avoided.

Other Such Scenarios:

BEGIN TRAN
COMMIT  
COMMIT  
BEGIN TRAN
COMMIT WORK
COMMIT WORK
BEGIN TRAN
ROLLBACK WORK
ROLLBACK WORK
(Visited 303 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Comments (1)

thanks…

Write a comment



Tags: , , , , , , , , , , , , ,