The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

Question

Here's a strange problem I'm running into on a production server. It has happened twice in the last two weeks, and this is a server that gets a lot of traffic.

We have some code in a Web Service that executes a BEGIN TRAN, then runs a few SQL queries (two inserts followed by an update). Then at the end executes a COMMIT. Twice now we have gotten the message in the logs:

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Between the first two inserts and the update, we call another web service, so there could be a slight delay between the first two inserts and last update before the COMMIT is called. Could this be causing our problem? We're running this on IIS 7 and Server 2008 R2 (all updated applied).

Originally we though it could be the app pools getting recycled, but changed that to recycle in the middle of the night. Now I'm not sure what would be causing SQL server to forget the call to BEGIN TRAN.

This web service does get called quite a bit. Has anyone seen something like this before? I'm at a total loss at the moment...

Any help or suggestion appreciated greatly!

Alexina

and there is also a bug related to this - support.microsoft.com/kb/810100/en-us – Tapping

Answer 1

It looks like your transaction failed, got rolled back and there is nothing to commit

example of such a thing

CREATE TABLE BlaTest(id INT PRIMARY KEY NOT NULL)
GO

Now run this

BEGIN TRAN

INSERT BlaTest VALUES('a')
GO

COMMIT TRAN

Here is the error

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'a' to data type int.
Msg 3902, Level 16, State 1, Line 2
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

This will run without a problem

BEGIN TRAN

INSERT BlaTest VALUES(5)
GO

COMMIT TRAN

A good article on transactions is Error Handling in SQL 2005 and Later by Erland Sommarskog

Dewan

That doesn't make any sense to me. If the transaction failed, wouldn't the error message be different? – Alexina

+1 - Easy fix for this, @sophtware, is to add IF @@TRANCOUNT > 0 COMMIT TRAN. This checks for open transactions. You can do the same thing before a ROLLBACK. – Acton

Now it makes perfect sense! Thanks! I'll check this out. Maybe I should look at the logs for more information about the transaction? – Alexina

Answer 2

My issue was I needed a BEGIN and END around my BEGIN TRAN and COMMIT TRAN.

BEGIN
     BEGIN TRAN

     INSERT BlaTest VALUES(5)
     GO

     COMMIT TRAN
END

Partee

Answer 3

BEGIN TRANS

at the top will help

Falgoust

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Fulfill

Answer 4

I had this problem as well.

Turned out, in my case, the problem was two commits.

The first one worked as intended. The second produced this message. Deleting the second commit solved the problem.

Goethe

Answer 5

I had the same issue. This is what I did to solve it.

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

After I Checked the SQL Query and Add a BEGIN TRAN it will executed successfully. Here My sample code. It will work:

ALTER procedure [dbo].[DeactivateUser]
    @UserId bigint,
    @LoginEmail Nvarchar(100),
    @merchantId int
    as
    Begin
      Begin tran

        update Users set 
        LoginEmail='inactive'+CONVERT(VARCHAR(11), getdate(), 106)+'-'+@LoginEmail,
        IsActive=0 
        where LoginEmail=@LoginEmail and MerchantID=@merchantId                     
        if(@@ERROR=0)
          begin
            commit Tran
            select 0
          end
        else
          begin
            rollback Tran
            select -1
          end


    end

Environment

its not a example it happened for me .so i shared the answers any how will look at this.thank you – Environment