The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

Old forum URL: forums.lhotka.net/forums/t/6565.aspx


kenb posted on Thursday, March 05, 2009

I'm hoping someone can help me troubleshoot this.  I am occasionally getting this exception thrown:

Type : System.Data.SqlClient.SqlException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

The code which causes this exception to be thrown is:

    Protected Overrides Sub DataPortal_Insert()

        Using transaction As New TransactionScope
            Dim db As Database = DatabaseFactory.CreateDatabase()
            Dim sql As String
            sql = "excluded for brevity" & _
                ";select @@identity from Employees"
            Using cmd As DbCommand = db.GetSqlStringCommand(sql)
                AddInsertUpdateParameters(db, cmd)
                mId = db.ExecuteScalar(cmd)
            End Using
            mTestResults.Update(Me)
            transaction.Complete() ' <-- EXCEPTION IS THROWN HERE
        End Using

    End Sub

The call to mTestResults.Update(Me) does not initiate another transaction.

This is an asp.net application. The customer, who isn't very tech savy, seems to think this has something to do with different users simultaneously executing this DataPortal_Insert code.

I am at a loss as to how this exception could ever be thrown. My google searches haven't produced a fix/understanding of the problem. Can anyone help?

EDIT: Should I be passing a parameter to the TransactionScope constructor since I'm operating in an asp.net environment?

EDIT #2: I am including the code in mTestResults.Update(Me). As you can see there is no secondary transaction being explicitly created.

    Friend Sub Insert(ByVal parent As Employee)

        Dim db As Database = DatabaseFactory.CreateDatabase()
        Dim sql As String
        sql = "excluded for brevity" & _
            ";select @@identity from TestResults"
        Using cmd As DbCommand = db.GetSqlStringCommand(sql)
            AddInsertUpdateParameters(db, cmd, parent)
            mId = db.ExecuteScalar(cmd)
        End Using
        MarkOld()

    End Sub

tlong replied on Thursday, March 05, 2009

Is mTestResults a child?

We solved a problem like this with a child, solved it by using the ConnectionManager so the child runs on the same connection as the parent.

We're running 3.5.1 with a web app.

Antonio Sandoval replied on Thursday, March 05, 2009

I'm not sure about how works nested transactions (http://msdn.microsoft.com/en-us/library/ms172152.aspx) and if your are trying that, but CSLA begins and commits the transaction when you specify TransactionScope, and is not necessary that you create your own TransactionScope, that is one of their goals. Take a look at the source code of CSLA TransactionalDataPortal.cs.

JoeFallon1 replied on Friday, March 06, 2009

Why aren't you passing the tr to mTestResults.Update? That is the standard behavior if you are not using ConnectionManager.

You might want to post the code inside mTestResults.Update too. Maybe you really are initiating another tr but do not realize it.

Joe

 

kenb replied on Friday, March 06, 2009

Joe --

I've edited my original post to include the mTestResult.Update code.

When you say that the standard behavior is to pass around the instance of the transaction do you mean TransactionScope or only DbTransaction instances? I was under the impression that TransactionScope instances don't need to be passed around. Perhaps I misunderstood.

I also believe that the Data Access Application Block which I'm using has the same functionality as ConnectionManager.

We now have support for System.Transactions and TransactionScope. The DAAB looks to see if a transaction is happening and caches open connections for re-use with other commands so a Distributed Transaction won't occur if using the same connection. Hence the following is now possible in Enterprise Library 3.0 without causing a Distributed Transaction:
using (TransactionScope scope = new TransactionScope(...))
{
    database.ExecuteNonQuery(...);
    database.ExecuteNonQuery(...);

    // ....

    scope.Complete();
}
source

I appreciate all your help.

-- Ken

JoeFallon1 replied on Monday, March 09, 2009

If you read the new book on Page 495 it shows the simplest possible example:

And the key line is:

tr.Commit

You wrote tr.Complete.

Also, for child object the code is:

FieldManager.UpdateChildren(Me)

You are calling update directly on the child which is OK as long as you pass the tr into the child like the old style code.

I also do not believe that the DAAB is doing what Rocky is doing. And you should not rely on it that way (since it does not work in your code anyway.)

Joe

 

kenb replied on Monday, March 09, 2009

Thanks again for your input Joe.  I changed from using TransactionScope instances (which btw uses .Complete() and not .Commit()) to using instances of DbTransaction.

I'll see if this change fixes the problem.

I appreciate your help.

Copyright (c) Marimer LLC