SQL Server，C＃：事务回滚的超时异常

I've got a strange problem. I have a .NET program and my process logic needs a long-running transaction (~20min) on a SQL Server 2005 database. That's ok, since nobody accesses the database in parallel. When something goes wrong, the transaction should be rolled back.

我有一个奇怪的问题。我有一个.NET程序，我的进程逻辑需要在SQL Server 2005数据库上运行一个长时间的事务（~20分钟）。没关系，因为没有人并行访问数据库。当出现问题时，应该回滚事务。

Infrequently and without any visible pattern the Rollback() operation on my DbTransaction object throws a SqlException:

不常见且没有任何可见模式的DbTransaction对象上的Rollback（）操作会抛出SqlException：

Message: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

StackTrace:
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
   at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
   at System.Data.SqlClient.TdsParserStateObject.ReadByte()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlInternalTransaction.Rollback()
   at System.Data.SqlClient.SqlTransaction.Rollback()

I don't know if it's really a timeout problem due to the fact, that the code works sometimes and sometimes not. Furthermore the only timeouts I know are ConnectionTimeout and CommandTimeout, but obviously those aren't the problem in this case.

我不知道这是否真的是一个超时问题，因为代码有时会工作，有时候不会。此外，我知道的唯一超时是ConnectionTimeout和CommandTimeout，但显然在这种情况下这些不是问题。

Does anyone have an idea about this problem?

有没有人对这个问题有所了解？

Thanks a lot, Matthias

非常感谢，马蒂亚斯

2 个解决方案

#1

Matt Neerincx of the Sql Server team addressed this in an MSDN forum question. Odd but true, the connect timeout from the connection string is used to set the timeout. Verified by him looking at the source code.

Sql Server团队的Matt Neerincx在一个MSDN论坛问题中解决了这个问题。奇怪但真实，连接字符串的连接超时用于设置超时。他通过查看源代码验证。

#2

Transactions can take a while to roll-back; if that takes too long, sure you'll get a timeout. There doesn't seem to be an obvious way to influence this - you could try managing the transaction via TSQL - then you can (ab)use the CommandTimeout - but it could simply be that it takes a little while if you are making lots of changes inside the transaction; SQL Server assumes that most things will run to completion, so "commit" is virtually free, while "rollback" is more expensive.

交易可能需要一段时间才能回滚;如果这需要太长时间，请确保你会超时。似乎没有一种明显的方法来影响这一点 - 您可以尝试通过TSQL管理事务 - 然后您可以（ab）使用CommandTimeout - 但它可能只是需要一段时间，如果你正在做很多交易内部的变化; SQL Server假设大多数事情都将运行完成，因此“提交”几乎是免费的，而“回滚”则更昂贵。