SQL Server, C#: Timeout exception on Transaction Rollback

Question

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

The server logs doesn't say anything? (MS SQL) Thinking that the rollback can be quite heavy..? — Onkelborg, Oct 20 '10 at 11:28
Do you mean with "sys.xp_readerrorlog"? Doesn't show any error... — Matthias, Oct 20 '10 at 11:36

score 25 · Accepted Answer · answered Oct 20 '10 at 11:56

25

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.

answered Oct 20 '10 at 11:56

Hans Passant

922,412
146
1,693
2,536

3

that is bizarre, it should be using CommandTimeout instead. Now I have to bump up my connection time to handle long rollbacks which inadvertently means if my sql box goes down, the code will sit and wait for longer than the default 30 seconds for a response. Lame. – Chris Smith Dec 04 '13 at 22:48
@ChrisSmith "it should be using CommandTimeout instead" How would you accomplish that, exactly, when a transaction isn't tied to any single command? The only option would be for Microsoft to add a Timeout property to the transaction itself. The solution I use is to simply swallow any SQL timeout exception for the rollback using a separate try/catch block, with an associated comment to explain why. It's not the prettiest solution, but at least you don't have to bump your timeouts and you don't pollute your logs. – Tom Lint Oct 19 '22 at 12:30
@Tom Lint I don’t know Tom, it’s been 9 years. :) Sql Server is a bit of a dinosaur these days. Best to move onto another storage system if you can. – Chris Smith Oct 22 '22 at 05:02

score 3 · Answer 2 · answered Oct 20 '10 at 11:43

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.

SQL Server, C#: Timeout exception on Transaction Rollback

2 Answers2

Linked