错误 - 与当前连接关联的事务已完成但尚未处理 [英] Error - The transaction associated with the current connection has completed but has not been disposed

问题描述

我在使用 TransactionScope 将多个数据库查询包装到一个事务中时遇到了问题，我使用的是批处理大小为 500 的 SqlBulkCopy.当我将批处理大小增加到 1000 时，出现错误:

I've been having trouble using the TransactionScope to wrap multiple database queries into a transaction, I am using SqlBulkCopy with batchsize 500. When I increased the batch size to 1000 I am getting the error:

与当前连接关联的事务已完成但尚未处置.交易必须在连接可用于执行 SQL 语句.

The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.

这是我使用的代码:

using (var scope = new TransactionScope())
{
    using (var connection = (SqlConnection)customerTable.OpenConnection())
    {
        var table1BulkCopy = new SqlBulkCopy(connection)
        {
            BatchSize = BATCH_SIZE,
            DestinationTableName = TableName1
        };

        table1BulkCopy.WriteToServer(table1DataTable);

        var table2BulkCopy = new SqlBulkCopy(connection)
        {
            BatchSize = BATCH_SIZE,
            DestinationTableName = TableName2
        };

        table2BulkCopy.WriteToServer(table2DataTable);

        var table3BulkCopy = new SqlBulkCopy(connection)
        {
            BatchSize = BATCH_SIZE,
            DestinationTableName = TableName3
        };

        table1BulkCopy.WriteToServer(table3DataTable);

        var table4BulkCopy = new SqlBulkCopy(connection)
        {
            BatchSize = BATCH_SIZE,
            DestinationTableName = TableName4
        };

        table4BulkCopy.WriteToServer(table4DataTable);

        scope.Complete();
    }
}

推荐答案

这可能在事务超时时发生.您可以像这样增加交易的超时时间(使用适合交易预期长度的值).下面的代码是 15 分钟:

This can happen when the transaction times out. You can increase the timeout for your transaction like this (use values appropriate for the expected length of your transaction). The code below is for 15 minutes:

using (TransactionScope scope = 
             new TransactionScope(TransactionScopeOption.Required, 
                                   new System.TimeSpan(0, 15, 0)))
  {
      // working code here
  }

这就是为什么它可能适用于 500 而不适用于 1000.

This is why it could have worked for batchsize 500 and not for 1000.

这篇关于错误 - 与当前连接关联的事务已完成但尚未处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！