transaction rollback error (This SqlTransaction has completed; it is no longer usable)

Question

I am trying to test the first transaction and if it is a SUCCESS, then i try the second one, if the second is also a SUCCESS then i commit both, if the second is a fail, i need to roll back the first.

So I have the following function which returns a dictionary that can be one of the states:

0 (meaning transaction failed but managed to rollback) -> sql transaction

1 (meaning transaction was successful) -> sql transaction

-1 (meaning transaction failed and didnt manage to rollback) -> sql transaction

Public Function execDBTrans_valMod(transactionName As String, ByVal emailSubject As String, ByVal emailBody As String, ByVal queryString As String, Optional ByVal connectionString As String = "SQLConnectionHere") As Dictionary(Of Integer, SqlTransaction)
        Dim trans_dict As New Dictionary(Of Integer, SqlTransaction)

        Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings(connectionString).ToString) 'LATEST EXEC
            connection.Open()

            Dim command As New SqlCommand(queryString, connection)
            Dim transaction As SqlTransaction = connection.BeginTransaction(transactionName)
            command.Transaction = transaction

            Try
                command.CommandTimeout = 3600 'Used for large updates
                command.ExecuteNonQuery()
                'transaction.Commit() DO NOT COMMIT so that we can commit only after we verify that pbSite can be inserted

                trans_dict.Add(1, transaction)
                Return trans_dict
            Catch dbTrans_ex As Exception
                Try
                    transaction.Rollback()

                    trans_dict.Add(0, transaction)
                    Return trans_dict
                Catch dbTrans2_ex As Exception

                    trans_dict.Add(-1, transaction)
                    Return trans_dict
                    ' This catch block will handle any errors that may have occurred on the server that would cause the rollback to fail, such as a closed connection.
                End Try
            End Try

        End Using
    End Function

Then i have the following code in a function:

Dim transPB_dict = execDBTrans_valMod("transPB", failSubject, failBody, buildInsert)
If transPB_dict.ContainsKey(1) Then 'SUCCESS

    Dim transPBsite_dict = execDBTrans_valMod("transPBsite", failSubject, failBody, buildInsert_PBsite)

    If transPBsite_dict.ContainsKey(1) Then
        transPB_dict.Item(1).Commit()
        transPBsite_dict.Item(1).Commit()
        Return True
    Else 'Failed to create the tables for this user so rollback all the tables that were created in the previous transaction (all the old tables in panelbase)
        transPB_dict.Item(1).Rollback() 'THIS THROWS THE ERROR This SqlTransaction has completed; it is no longer usable

        Return False
    End If

Else
        Return False
End if

I have not commited the first transaction yet, why is it saying that the sqlTransaction has completed when i try to roll back...?

Thank you for any help!

score 1 · Answer 1 · answered Sep 23 '14 at 14:23

Because your connection is opened and closed in execDBTrans_valMod.
When the connection is closed, the transaction associated with the connection is ended.

If you need to rollback the first command when the second fails, you just need only one transaction.
The connecion and the transaction should be managed by the caller of the command executors.

You need something like:

Public Sub execDBTrans_valMod(ByVal transaction As SqlTransaction, ByVal emailSubject As String, ByVal emailBody As String, ByVal queryString As String)
    Using command As New SqlCommand(queryString, transaction.Connection, transaction)
        Try
            command.CommandTimeout = 3600 'Used for large updates
            command.ExecuteNonQuery()
        Catch ex As Exception
            ' Do something with emailSubject and emailBody??
            ' Then rethrow ex to be caught by the caller.
            Throw
        End Try
    End Using
End Sub

And the caller:

Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("SQLConnectionHere").ToString)
    connection.Open()
    Using transaction As SqlTransaction = connection.BeginTransaction()
        Try
            execDBTrans_valMod(transaction, failSubject, failBody, buildInsert)
            execDBTrans_valMod(transaction, failSubject, failBody, buildInsert_PBsite)
            transaction.Commit()
            Return True
        Catch ex As Exception
            Try
                transaction.Rollback()
            Catch exRollback As Exception
                ' Do logging or something when rollback failed.
            End Try
            Return False
        End Try
    End Using
End Using

score 0 · Accepted Answer · answered Sep 23 '14 at 14:17

When you exit the USING block

Using connection As New SqlConnection...
End Using

Your connection is closed and disposed and any pending transactions against that connect are committed. You cannot have the same transaction against multiple connections.

Only create and open your connection once. Pass this connection (and transaction) to your method that is going to process your SQL. Then when it is all done, you can commit your transaction and close/dispose your connection.

Dealing with a single connection across multiple SQL commands will increase the speed of your program because there is overhead involved with each connection that can slow you down.

Ah yes of course that makes sense thank you! I have passed the connection back out and closed it manually instead of using the "Using" statement. — Rezzy, Sep 23 '14 at 15:45

transaction rollback error (This SqlTransaction has completed; it is no longer usable)

2 Answers2