How to handle SQL command timeouts in .NET code
If you're a .NET programmer you might be familiar with using SqlConnection and SqlCommand objects to perform database queries.
They might look something like this:
You might think the liberal use of 'using' statements disposes of all the resources that might still be lingering around after execution of this method completes. You might think that there are no 'leakages' happening here. This might be true in most cases however this blog post is going to pose a counter-example.
Consider the scenario where just before the process releases the "Exclusive (X) lock" it has on the resource it is INSERTing, the command timeout expires.
Now what happens?
The command timeout expiration event causes SQL execution to stop immediately for this process. The COMMIT command that follows implicitly from the INSERT statement above is never executed. A Senior Escalation Engineer at Microsoft, Bob Dorr, has the following to say about command timeouts:
To fix this, we should catch the command timeout exception and explicitly commit or rollback the transaction to close it properly.
"Stale" SQL transactions that are not closed properly can cause poor database performance. They can cause other transactions to block on any locks that they have, thus causing more command timeouts and the problem can snowball.
SET IMPLICIT_TRANSACTIONS - https://msdn.microsoft.com/en-us/library/ms187807.aspx
How it Works: Attention, Attention or Should I say Cancel the Query and Be Sure to Process Your Results - http://blogs.msdn.com/b/psssql/archive/2008/07/23/how-it-works-attention-attention-or-should-i-say-cancel-the-query-and-be-sure-to-process-your-results.aspx
CommandTimeout - How to handle it properly? - http://blog.sqlxdetails.com/commandtimeout-how-to-handle-it-properly/
CommandTimeout - How to handle it properly? - code example - http://blog.sqlxdetails.com/commandtimeout-how-to-handle-it-properly-code-example/
How to Minimize SQL Server Blocking - http://www.sql-server-performance.com/2006/blocking/
They might look something like this:
Consider the scenario where just before the process releases the "Exclusive (X) lock" it has on the resource it is INSERTing, the command timeout expires.
Now what happens?
The command timeout expiration event causes SQL execution to stop immediately for this process. The COMMIT command that follows implicitly from the INSERT statement above is never executed. A Senior Escalation Engineer at Microsoft, Bob Dorr, has the following to say about command timeouts:
At the time of the [command timeout] the transaction is not rolled back unless transaction abort (XACT_ABORT) has been enabled. The client needs to submit a rollback. If the client does not submit the rollback and continues other processing the transaction remains open and the behavior scope of the application becomes undefined.What Bob Dorr is saying is that in the above code, MyInsertMethod1, fails to defensively handle the command timeout exception because it does not end the transaction if such an exception occurs. The transaction can be ended by a COMMIT or ROLLBACK. Furthermore, the side effects of this are not good. The transaction still holds onto any locks that it has, or in other words the transaction is still open and it will "leak".
To fix this, we should catch the command timeout exception and explicitly commit or rollback the transaction to close it properly.
"Stale" SQL transactions that are not closed properly can cause poor database performance. They can cause other transactions to block on any locks that they have, thus causing more command timeouts and the problem can snowball.
References
Locking in the Database Engine - https://msdn.microsoft.com/en-us/library/ms190615(v=sql.100).aspx
Lock Compatibility - https://msdn.microsoft.com/en-us/library/ms186396(v=sql.100).aspx
SET IMPLICIT_TRANSACTIONS - https://msdn.microsoft.com/en-us/library/ms187807.aspx
How it Works: Attention, Attention or Should I say Cancel the Query and Be Sure to Process Your Results - http://blogs.msdn.com/b/psssql/archive/2008/07/23/how-it-works-attention-attention-or-should-i-say-cancel-the-query-and-be-sure-to-process-your-results.aspx
CommandTimeout - How to handle it properly? - http://blog.sqlxdetails.com/commandtimeout-how-to-handle-it-properly/
CommandTimeout - How to handle it properly? - code example - http://blog.sqlxdetails.com/commandtimeout-how-to-handle-it-properly-code-example/
How to Minimize SQL Server Blocking - http://www.sql-server-performance.com/2006/blocking/