错误-与当前连接关联的事务已完成，但尚未释放

我在使用 TransactionScope将多个数据库查询封装到一个事务中时遇到了麻烦，我正在使用 SqlBulkCopy 和 batchsize 500。当我把批量增加到1000时，我得到了一个错误:

与当前连接关联的事务已完成 交易必须在 连接可用于执行 SQL 语句。

这是我正在使用的代码:

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();
}
}
85825 次浏览
小开

Move scope.Complete(); outside the connection block.

using (var scope = new TransactionScope())
{
using (var connection = (SqlConnection)customerTable.OpenConnection())
{
//
}
scope.Complete();
}
小开

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
}

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

小开

I found that setting the timeout in the TransactionScope didn't work for me. I also needed to add the following config key to the end of the machine.config <configuration> tag to extend past the default max timeout of 10 minutes.

<system.transactions>
<machineSettings maxTimeout="00:30:00" /> <!-- 30 minutes -->
</system.transactions>

Credit: http://thecodesaysitall.blogspot.com.au/2012/04/long-running-systemtransactions.html

小开

The full answer must be more full.

You must specify - where will be max transaction timeout determined - in the .Net code, or in the server config

<sectionGroup name="system.transactions"....
...allowDefinition="MachineOnly"
</sectionGroup>

In this case you can set max timeout in the machine.config

<configuration>
<system.transactions>
<machineSettings maxTimeout="01:00:00" />
</system.transactions>
</configuration>

Or may be you want to override this behaivor in the application. Then in the machine.config you should set atribute the velue:

...allowDefinition="MachineToApplication"

This is a good arcticle: https://blogs.msdn.microsoft.com/ajit/2008/06/18/override-the-system-transactions-default-timeout-of-10-minutes-in-the-code/

小开

Pretty obvious problem with time out, but you don't get effect if you set TransactionOptions.Timeout higher. Even if you set TimeSpan.MaxValue you don't actually get a profit. It doesnt matter that the Timeout property of the TransactionOptions are set to a higher value, TransactionOptions.Timeout can not exceed maxTimeout property. You should set up some changes in machine.config.

Shortly you should find machine.config file %windir%\Microsoft.NET\Framework\yourversion\config\machine.config
And add this in <configuration> tag:

<system.transactions>
<machineSettings maxTimeout="00:30:00"/>
</system.transactions>

Here you can set maxTimeout property to 30 minutes.
See following for more details http://thecodesaysitall.blogspot.com/2012/04/long-running-systemtransactions.html

小开

C# 9 Lang version. TransactionScopeOption.Suppress works like magic for me.

// TransactionScopeOption.Suppress works and fixed my issue
using TransactionScope Scope = new TransactionScope(TransactionScopeOption.Suppress);
try
{
using var CentralConnection = SQLConnection("Other connection string here");
using var LocalConnection = SQLConnection("Other connection string here");


// Central
using var cmd0 = new SqlCommand("OtherSQLCommandTextHere", CentralConnection)
{
CommandTimeout = 0 // I just add zero timeout here
};
cmd0.ExecuteNonQuery();


// Local
using var cmd1 = new SqlCommand("OtherSQLCommandTextHere", LocalConnection)
{
CommandTimeout = 0 // I just add zero timeout here
};
cmd1.ExecuteNonQuery();


// The Complete method commits the transaction. If an exception has been thrown,
// Complete is not called and the transaction is rolled back.
Scope.Complete();
}
catch (Exception ex)
{
Scope.Dispose();
MessageBox.Show(ex.ToString());
}


public SqlConnection SQLConnection(string ISQLConnection)
{
SqlConnection conn = null;
try
{
conn = new SqlConnection(ISQLConnection);
conn.Open();
}
catch
{
// skipping here error message
}
return conn;
}
小开

Apply this :

TransactionScope(TransactionScopeOption.Required, new System.TimeSpan(0, 30, 0));
(0, 30, 0) = (hour,minute,seconds)

If you want to test, apply the Time Span with this (0, 0, 1), try and you will get the error. After that, increase seconds or minute or hour and try. it will work.


提交答案