CommandTimeout – How to handle it properly – code example

Properly closing a connection to Sql Server in DotNet is more challenging than one might think. Classic code looks something like this MSDN example:

Or a mixture of “using” and “try-catch” constructs like in this nice example.

But, none of them handles the leaked transactions which stay opened after command timeout occurs. Not many people know that the SqlCommand timeout event immediately interrupts tsql when occurs, and even tsql’s catch block is NOT executed, so rollbacks/commits in tsql are not reached! Therefore, transaction stays opened holding all the locks, and there is no chance for the interrupted tsql to close that transaction. It must be done from the client (dotnet catch block). Have you ever seen how catch block that properly handles the timeout exception looks like? I haven’t. So I decided to write one here that everyone can make use of.

This is written in PowerShell 4.0 ISE, but it uses dotnet SqlClient classes so it is easily convertable to any dotnet language:

Important lines are in the “catch” block. If timeout SqlException is detected (e.Number=-2), additional command is sent to SQL within the same connection. That is very important, because we can only close transaction if we are in the same connection that started that transaction. The command can be:

  • WHILE @@TRANCOUNT>0 COMMIT TRAN; — To commit the transaction. “While” is to work even with nested transactions.
  • IF @@TRANCOUNT>0 ROLLBACK TRAN; — To rollback the transaction. Single rollback exits all levels of nested transactions, no need to loop.

Summary

I wanted to provide you a code that properly handles a command timeout event when it occurs, without leaving opened transactions and locks behind. That is a common scenario that DBA’s usually solved by killing connections, but I think it is much better to solve it by properly handling the event. So violent kills and blockings caused by unclosed transactions will be prevented to occur at the very root. It will make your app more reliable, users happier, DBAs less work, and developers less banging their heads on how to solve that heck of a “leaking transaction” problem that sporadically occurs causing havoc on the database.

Posted in Sql Client Code Tagged with: , ,
2 comments on “CommandTimeout – How to handle it properly – code example
  1. Joel says:

    Thanks for this. We’ve been having trouble that I think may be due to command timeouts and while I read (and tested) that you can use “SET XACT_ABORT ON” in your query to make sure transactions are rolled back and locks released, I’ve been thinking that there must be a way to handle command timeouts from the client, but I couldn’t find any example code.

  2. Vedran Vedran says:

    Update: changed the code to prefer ROLLBACK in timeout handler.

Leave a Reply

Your email address will not be published. Required fields are marked *

*