使用SqlTransaction批量更新时随机出现提交事务无对应开始事务错误求助

Hey there! Let's dig into that random transaction error you're hitting. The "commit transaction request has no corresponding begin transaction" issue usually pops up when you're reusing a transaction or connection object that's already been rolled back/disposed, or when your error handling isn't properly cleaning up resources between batches.

From your code snippet, it looks like you're declaring the connection and transaction outside the while loop—this is probably the culprit! If one batch fails and you roll back the transaction, that transaction object becomes invalid. When the loop moves to the next batch, you might be trying to use that same dead transaction, leading to the random error.

The Fix: Isolate Each Batch with Fresh Resources #

The solution is to create a new connection and transaction for every batch. That way, each 50-record group is fully isolated, and a failure in one batch doesn't poison the next. Here's how to restructure your code properly with strict resource management:

Dim totalRecords As Integer = lstq.Count ' 总记录数
Dim batchSize As Integer = 50
Dim completed As Integer = 0

While completed < totalRecords
    ' 每批都新建连接，Using语句自动处理资源释放
    Using sqlCon As New SqlClient.SqlConnection("你的数据库连接字符串")
        sqlCon.Open()
        ' 为当前批次创建独立事务
        Using sqlTransaction As SqlClient.SqlTransaction = sqlCon.BeginTransaction()
            Try
                ' 获取当前批次的50条数据
                Dim currentBatch = lstq.Skip(completed).Take(batchSize).ToList()
                
                For Each item In currentBatch
                    ' 执行更新命令，务必关联当前事务
                    Using sqlCmd As New SqlClient.SqlCommand("你的更新SQL语句", sqlCon, sqlTransaction)
                        ' 添加SQL参数（避免注入，提升性能）
                        ' sqlCmd.Parameters.Add("@ParamName", SqlDbType.Type).Value = item.Property
                        sqlCmd.ExecuteNonQuery()
                    End Using
                Next

                ' 批量更新成功，提交事务
                sqlTransaction.Commit()
                completed += currentBatch.Count
                Console.WriteLine($"已成功处理 {completed} 条记录")
            Catch ex As Exception
                ' 出错时回滚当前批次的所有操作
                sqlTransaction.Rollback()
                Console.WriteLine($"批次处理失败（起始位置：{completed}）：{ex.Message}")
                ' 可选：如果需要终止整个批量操作，取消注释下面的Throw
                ' Throw
            End Try
        End Using ' 事务会自动释放，无论成功或失败
    End Using ' 连接会自动关闭并释放资源
End While

Key Things to Remember #

• Use Using Statements: These are non-negotiable—they guarantee that connections and transactions are properly disposed even if an error crashes the batch. No more hanging invalid objects!
• Per-Batch Isolation: Each batch gets its own connection and transaction. If one fails, only those 50 records are rolled back, and the next batch starts with a clean slate.
• Never Reuse Transactions/Connections: Once a transaction is committed or rolled back, it's in an invalid state. Reusing it will lead to weird, random errors like the one you're seeing.
• Associate Commands with Transactions: Always pass the transaction object to your SqlCommand constructor—otherwise, the command won't be part of the transaction scope.

If you still run into the error occasionally, double-check for any code that might be modifying the transaction/connection outside the Try-Catch block, or ensure your connection string isn't causing unexpected connection drops.

内容的提问来源于stack exchange，提问作者user1498942