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!

Answer 1

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.