SqlTransaction已完成，它不再可用，回滚问题(SqlTransaction has completed, it is no longer usable, rollback issue)

我偶尔会遇到这个错误消息：

SqlTransaction已完成; 它不再可用。

我不确定是什么导致了这个错误弹出，我试图设置SQL超时无限。

是否因为我的代码结构？

当小组用户使用时不会发生这种情况，但当庞大的用户群发生时经常发生。

Public Function PostCustomerSet(ByVal ds As CustomerHeaderDetailDataSet, ByVal Table As String, ByRef SessionKeys As String, ByRef errMsg As String, ByVal SubmitType As Integer, ByVal callWrapper As Boolean) As String
    errMsg = Nothing
    Dim newkey As PrimaryKey
    Dim oldkey As PrimaryKey
    Try
        transaction = EnterpriseUtils.StartTransaction(Connection1, m_Dict)
        If ds.CustomerHeader.Rows.Count > 0 Then
            Dim SessionKey As PrimaryKey = PrimaryKey.FromString(SessionKeys)
            Dim CustomerHeaderKey As PrimaryKey = EnterpriseUtils.VerifyDataRowKeys(ds.CustomerHeader.Rows(0), SessionKey, SubmitType)
            Dim OldCustomerHeaderKey As PrimaryKey = New PrimaryKey(CustomerHeaderKey)
            newkey = CustomerHeaderKey
            oldkey = OldCustomerHeaderKey

            If CustomerHeaderKey Is Nothing Then
                Return Nothing
            Else
                If ds.CustomerHeader.Rows.Count > 0 Then 'check if record was not deleted during update
                    CustomerHeaderKey.Update(ds.CustomerHeader(0))
                    If SubmitType = 1 Then
                        EnterpriseUtils.UpdateTemporaryLinks("CustomerNumber", CustomerHeaderKey, OldCustomerHeaderKey, "CustomerDetail", transaction)
                        EnterpriseUtils.UpdateTemporaryLinks("CustomerNumber", CustomerHeaderKey, OldCustomerHeaderKey, "CustomerMultiPayments", transaction)
                    End If


                    If callWrapper AndAlso ds.CustomerHeader(0).CustomerNumber.ToUpper <> "DEFAULT" Then
                        Dim Command As SqlCommand = New SqlCommand("enterprise.Customer_Control", Connection1)
                        Command.CommandType = CommandType.StoredProcedure
                        Command.Transaction = transaction
                        Dim parameter As SqlParameter
                        '@CompanyID
                        parameter = New SqlParameter("@CompanyID", SqlDbType.NVarChar, 36)
                        parameter.Value = ds.CustomerHeader(0).CompanyID
                        Command.Parameters.Add(parameter)
                        'DivisionID
                        parameter = New SqlParameter("@DivisionID", SqlDbType.NVarChar, 36)
                        parameter.Value = ds.CustomerHeader(0).DivisionID
                        Command.Parameters.Add(parameter)
                        '@DepartmentID
                        parameter = New SqlParameter("@DepartmentID", SqlDbType.NVarChar, 36)
                        parameter.Value = ds.CustomerHeader(0).DepartmentID
                        Command.Parameters.Add(parameter)
                        '@DocumentNumber
                        parameter = New SqlParameter("@DocumentNumber", SqlDbType.NVarChar, 36)
                        parameter.Value = ds.CustomerHeader(0).CustomerNumber
                        Command.Parameters.Add(parameter)
                        Command.ExecuteNonQuery()
                    End If
                End If
                transaction.Commit()
                transaction = Nothing
                Return CustomerHeaderKey.ToString()
            End If
        ElseIf ds.CustomerDetail.Count > 0 Then
            CustomerDetail_Adapter.Update(ds.CustomerDetail)
            transaction.Commit()
            transaction = Nothing
        ElseIf ds.CustomerMiscCharges.Count > 0 Then
            CustomerMiscCharges_Adapter.Update(ds.CustomerMiscCharges)
            transaction.Commit()
            transaction = Nothing
        ElseIf ds.CustomerMultiPayments.Count > 0 Then
            CustomerMultiPayment_Adapter.Update(ds.CustomerMultiPayments)
            transaction.Commit()
            transaction = Nothing
        End If
        Return Nothing       
    Catch e As SqlException
        errMsg = "Please contact your system administrator. (code number: " + e.Number + ")" 'Message + ")"
        WebUtils.ProcessError(e, errMsg, EDIErrorType.SendEmailNotification Or EDIErrorType.WriteLog)
        Return Nothing
    Catch e As Exception
        errMsg = e.Message
        WebUtils.ProcessError(e, errMsg, EDIErrorType.SendEmailNotification Or EDIErrorType.WriteLog)
        Return Nothing
    Finally
        If Not transaction Is Nothing Then
            transaction.Rollback()
        End If
        If Connection1.State = ConnectionState.Open Then
            Connection1.Close()
        End If
    End Try
End Function
 
 任何人都可以给我一些提示，我做错了什么？ 
 
 先谢谢了。 
I has been facing this error message once in a while:
 
 
 
The SqlTransaction has completed; it is no longer usable.
 
 
I am not sure what caused this error to pop up, I have tried setting SQL Timeout to infinity. 
 
Is it because of my code structure? 
 
This doesn't happen when small group of user, but happens frequently when huge group of user. 
 
Public Function PostCustomerSet(ByVal ds As CustomerHeaderDetailDataSet, ByVal Table As String, ByRef SessionKeys As String, ByRef errMsg As String, ByVal SubmitType As Integer, ByVal callWrapper As Boolean) As String
    errMsg = Nothing
    Dim newkey As PrimaryKey
    Dim oldkey As PrimaryKey
    Try
        transaction = EnterpriseUtils.StartTransaction(Connection1, m_Dict)
        If ds.CustomerHeader.Rows.Count > 0 Then
            Dim SessionKey As PrimaryKey = PrimaryKey.FromString(SessionKeys)
            Dim CustomerHeaderKey As PrimaryKey = EnterpriseUtils.VerifyDataRowKeys(ds.CustomerHeader.Rows(0), SessionKey, SubmitType)
            Dim OldCustomerHeaderKey As PrimaryKey = New PrimaryKey(CustomerHeaderKey)
            newkey = CustomerHeaderKey
            oldkey = OldCustomerHeaderKey

            If CustomerHeaderKey Is Nothing Then
                Return Nothing
            Else
                If ds.CustomerHeader.Rows.Count > 0 Then 'check if record was not deleted during update
                    CustomerHeaderKey.Update(ds.CustomerHeader(0))
                    If SubmitType = 1 Then
                        EnterpriseUtils.UpdateTemporaryLinks("CustomerNumber", CustomerHeaderKey, OldCustomerHeaderKey, "CustomerDetail", transaction)
                        EnterpriseUtils.UpdateTemporaryLinks("CustomerNumber", CustomerHeaderKey, OldCustomerHeaderKey, "CustomerMultiPayments", transaction)
                    End If


                    If callWrapper AndAlso ds.CustomerHeader(0).CustomerNumber.ToUpper <> "DEFAULT" Then
                        Dim Command As SqlCommand = New SqlCommand("enterprise.Customer_Control", Connection1)
                        Command.CommandType = CommandType.StoredProcedure
                        Command.Transaction = transaction
                        Dim parameter As SqlParameter
                        '@CompanyID
                        parameter = New SqlParameter("@CompanyID", SqlDbType.NVarChar, 36)
                        parameter.Value = ds.CustomerHeader(0).CompanyID
                        Command.Parameters.Add(parameter)
                        'DivisionID
                        parameter = New SqlParameter("@DivisionID", SqlDbType.NVarChar, 36)
                        parameter.Value = ds.CustomerHeader(0).DivisionID
                        Command.Parameters.Add(parameter)
                        '@DepartmentID
                        parameter = New SqlParameter("@DepartmentID", SqlDbType.NVarChar, 36)
                        parameter.Value = ds.CustomerHeader(0).DepartmentID
                        Command.Parameters.Add(parameter)
                        '@DocumentNumber
                        parameter = New SqlParameter("@DocumentNumber", SqlDbType.NVarChar, 36)
                        parameter.Value = ds.CustomerHeader(0).CustomerNumber
                        Command.Parameters.Add(parameter)
                        Command.ExecuteNonQuery()
                    End If
                End If
                transaction.Commit()
                transaction = Nothing
                Return CustomerHeaderKey.ToString()
            End If
        ElseIf ds.CustomerDetail.Count > 0 Then
            CustomerDetail_Adapter.Update(ds.CustomerDetail)
            transaction.Commit()
            transaction = Nothing
        ElseIf ds.CustomerMiscCharges.Count > 0 Then
            CustomerMiscCharges_Adapter.Update(ds.CustomerMiscCharges)
            transaction.Commit()
            transaction = Nothing
        ElseIf ds.CustomerMultiPayments.Count > 0 Then
            CustomerMultiPayment_Adapter.Update(ds.CustomerMultiPayments)
            transaction.Commit()
            transaction = Nothing
        End If
        Return Nothing       
    Catch e As SqlException
        errMsg = "Please contact your system administrator. (code number: " + e.Number + ")" 'Message + ")"
        WebUtils.ProcessError(e, errMsg, EDIErrorType.SendEmailNotification Or EDIErrorType.WriteLog)
        Return Nothing
    Catch e As Exception
        errMsg = e.Message
        WebUtils.ProcessError(e, errMsg, EDIErrorType.SendEmailNotification Or EDIErrorType.WriteLog)
        Return Nothing
    Finally
        If Not transaction Is Nothing Then
            transaction.Rollback()
        End If
        If Connection1.State = ConnectionState.Open Then
            Connection1.Close()
        End If
    End Try
End Function
 
Can anyone give me some hints what I did wrong?
 
Thanks in advanced.
                
最满意答案
                
                    
                        
 你正在设置  transaction = Nothing之后transaction = Nothing 
 
transaction.Commit()
 
 这是造成这个问题的原因，你需要把Finally Method设置为Nothing。 
 
Public Function PostCustomerSet(ByVal ds As CustomerHeaderDetailDataSet, ByVal Table As String, ByRef SessionKeys As String, ByRef errMsg As String, ByVal SubmitType As Integer, ByVal callWrapper As Boolean) As String
errMsg = Nothing
Dim newkey As PrimaryKey
Dim oldkey As PrimaryKey
Try
    transaction = EnterpriseUtils.StartTransaction(Connection1, m_Dict)
    If ds.CustomerHeader.Rows.Count > 0 Then
        Dim SessionKey As PrimaryKey = PrimaryKey.FromString(SessionKeys)
        Dim CustomerHeaderKey As PrimaryKey = EnterpriseUtils.VerifyDataRowKeys(ds.CustomerHeader.Rows(0), SessionKey, SubmitType)
        Dim OldCustomerHeaderKey As PrimaryKey = New PrimaryKey(CustomerHeaderKey)
        newkey = CustomerHeaderKey
        oldkey = OldCustomerHeaderKey

        If CustomerHeaderKey Is Nothing Then
            Return Nothing
        Else
            If ds.CustomerHeader.Rows.Count > 0 Then 'check if record was not deleted during update
                CustomerHeaderKey.Update(ds.CustomerHeader(0))
                If SubmitType = 1 Then
                    EnterpriseUtils.UpdateTemporaryLinks("CustomerNumber", CustomerHeaderKey, OldCustomerHeaderKey, "CustomerDetail", transaction)
                    EnterpriseUtils.UpdateTemporaryLinks("CustomerNumber", CustomerHeaderKey, OldCustomerHeaderKey, "CustomerMultiPayments", transaction)
                End If


                If callWrapper AndAlso ds.CustomerHeader(0).CustomerNumber.ToUpper <> "DEFAULT" Then
                    Dim Command As SqlCommand = New SqlCommand("enterprise.Customer_Control", Connection1)
                    Command.CommandType = CommandType.StoredProcedure
                    Command.Transaction = transaction
                    Dim parameter As SqlParameter
                    '@CompanyID
                    parameter = New SqlParameter("@CompanyID", SqlDbType.NVarChar, 36)
                    parameter.Value = ds.CustomerHeader(0).CompanyID
                    Command.Parameters.Add(parameter)
                    'DivisionID
                    parameter = New SqlParameter("@DivisionID", SqlDbType.NVarChar, 36)
                    parameter.Value = ds.CustomerHeader(0).DivisionID
                    Command.Parameters.Add(parameter)
                    '@DepartmentID
                    parameter = New SqlParameter("@DepartmentID", SqlDbType.NVarChar, 36)
                    parameter.Value = ds.CustomerHeader(0).DepartmentID
                    Command.Parameters.Add(parameter)
                    '@DocumentNumber
                    parameter = New SqlParameter("@DocumentNumber", SqlDbType.NVarChar, 36)
                    parameter.Value = ds.CustomerHeader(0).CustomerNumber
                    Command.Parameters.Add(parameter)
                    Command.ExecuteNonQuery()
                End If
            End If
            transaction.Commit()
            transaction = Nothing
            Return CustomerHeaderKey.ToString()
        End If
    ElseIf ds.CustomerDetail.Count > 0 Then
        CustomerDetail_Adapter.Update(ds.CustomerDetail)
        transaction.Commit()

    ElseIf ds.CustomerMiscCharges.Count > 0 Then
        CustomerMiscCharges_Adapter.Update(ds.CustomerMiscCharges)
        transaction.Commit()

    ElseIf ds.CustomerMultiPayments.Count > 0 Then
        CustomerMultiPayment_Adapter.Update(ds.CustomerMultiPayments)
        transaction.Commit()

    End If
    Return Nothing       
Catch e As SqlException
    errMsg = "Please contact your system administrator. (code number: " + e.Number + ")" 'Message + ")"
    WebUtils.ProcessError(e, errMsg, EDIErrorType.SendEmailNotification Or EDIErrorType.WriteLog)
    Return Nothing
Catch e As Exception
    errMsg = e.Message
    WebUtils.ProcessError(e, errMsg, EDIErrorType.SendEmailNotification Or EDIErrorType.WriteLog)
    Return Nothing
Finally
    If Not transaction Is Nothing Then
        transaction.Rollback()
    End If
    If Connection1.State = ConnectionState.Open Then
        Connection1.Close()
    End If
End Try
transaction = Nothing
 
 结束功能 
You are setting transaction = Nothing after 
 
transaction.Commit()
 
Which is causing this issue, you need to set this as nothing after Finally Method.
 
Public Function PostCustomerSet(ByVal ds As CustomerHeaderDetailDataSet, ByVal Table As String, ByRef SessionKeys As String, ByRef errMsg As String, ByVal SubmitType As Integer, ByVal callWrapper As Boolean) As String
errMsg = Nothing
Dim newkey As PrimaryKey
Dim oldkey As PrimaryKey
Try
    transaction = EnterpriseUtils.StartTransaction(Connection1, m_Dict)
    If ds.CustomerHeader.Rows.Count > 0 Then
        Dim SessionKey As PrimaryKey = PrimaryKey.FromString(SessionKeys)
        Dim CustomerHeaderKey As PrimaryKey = EnterpriseUtils.VerifyDataRowKeys(ds.CustomerHeader.Rows(0), SessionKey, SubmitType)
        Dim OldCustomerHeaderKey As PrimaryKey = New PrimaryKey(CustomerHeaderKey)
        newkey = CustomerHeaderKey
        oldkey = OldCustomerHeaderKey

        If CustomerHeaderKey Is Nothing Then
            Return Nothing
        Else
            If ds.CustomerHeader.Rows.Count > 0 Then 'check if record was not deleted during update
                CustomerHeaderKey.Update(ds.CustomerHeader(0))
                If SubmitType = 1 Then
                    EnterpriseUtils.UpdateTemporaryLinks("CustomerNumber", CustomerHeaderKey, OldCustomerHeaderKey, "CustomerDetail", transaction)
                    EnterpriseUtils.UpdateTemporaryLinks("CustomerNumber", CustomerHeaderKey, OldCustomerHeaderKey, "CustomerMultiPayments", transaction)
                End If


                If callWrapper AndAlso ds.CustomerHeader(0).CustomerNumber.ToUpper <> "DEFAULT" Then
                    Dim Command As SqlCommand = New SqlCommand("enterprise.Customer_Control", Connection1)
                    Command.CommandType = CommandType.StoredProcedure
                    Command.Transaction = transaction
                    Dim parameter As SqlParameter
                    '@CompanyID
                    parameter = New SqlParameter("@CompanyID", SqlDbType.NVarChar, 36)
                    parameter.Value = ds.CustomerHeader(0).CompanyID
                    Command.Parameters.Add(parameter)
                    'DivisionID
                    parameter = New SqlParameter("@DivisionID", SqlDbType.NVarChar, 36)
                    parameter.Value = ds.CustomerHeader(0).DivisionID
                    Command.Parameters.Add(parameter)
                    '@DepartmentID
                    parameter = New SqlParameter("@DepartmentID", SqlDbType.NVarChar, 36)
                    parameter.Value = ds.CustomerHeader(0).DepartmentID
                    Command.Parameters.Add(parameter)
                    '@DocumentNumber
                    parameter = New SqlParameter("@DocumentNumber", SqlDbType.NVarChar, 36)
                    parameter.Value = ds.CustomerHeader(0).CustomerNumber
                    Command.Parameters.Add(parameter)
                    Command.ExecuteNonQuery()
                End If
            End If
            transaction.Commit()
            transaction = Nothing
            Return CustomerHeaderKey.ToString()
        End If
    ElseIf ds.CustomerDetail.Count > 0 Then
        CustomerDetail_Adapter.Update(ds.CustomerDetail)
        transaction.Commit()

    ElseIf ds.CustomerMiscCharges.Count > 0 Then
        CustomerMiscCharges_Adapter.Update(ds.CustomerMiscCharges)
        transaction.Commit()

    ElseIf ds.CustomerMultiPayments.Count > 0 Then
        CustomerMultiPayment_Adapter.Update(ds.CustomerMultiPayments)
        transaction.Commit()

    End If
    Return Nothing       
Catch e As SqlException
    errMsg = "Please contact your system administrator. (code number: " + e.Number + ")" 'Message + ")"
    WebUtils.ProcessError(e, errMsg, EDIErrorType.SendEmailNotification Or EDIErrorType.WriteLog)
    Return Nothing
Catch e As Exception
    errMsg = e.Message
    WebUtils.ProcessError(e, errMsg, EDIErrorType.SendEmailNotification Or EDIErrorType.WriteLog)
    Return Nothing
Finally
    If Not transaction Is Nothing Then
        transaction.Rollback()
    End If
    If Connection1.State = ConnectionState.Open Then
        Connection1.Close()
    End If
End Try
transaction = Nothing
 
End Function