SQL Server, C#: Timeout exception on Transaction Rollback

Question

Hey guys,

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.

Infrequently and without any visible pattern the Rollback() operation on my DbTransaction object throws a 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.

Does anyone have an idea about this problem?

Thanks a lot, Matthias

Answer 1

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.

Answer 2

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.

Answer 3

@Hans: yeah, increasing the ConnectionTimeout did the trick! Woah, this is really odd! Misusing the connection timeout for doing the complete rollback is strange.

Thanks @all for your instant help!