How to catch SqlException caused by deadlock?

Question

From a .NET 3.5 / C# app, I would like to catch SqlException but only if it is caused by deadlocks on a SQL Server 2008 instance.

Typical error message is Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Yet, it does not seem to be a documented error code for this exception.

Filtering exception against the presence of the deadlock keyword in their message seems a very ugly way to achieve this behavior. Does someone know the right way of doing this?

Answer 1

The Microsft SQL Server-specific error code for a deadlock is 1205 so you'd need to handle the SqlException and check for that. So, e.g. if for all other types of SqlException you want the bubble the exception up:

catch (SqlException ex) {     if (ex.Number == 1205)     {         // Deadlock      }     else         throw; } 

Or, using exception filtering available in C# 6

catch (SqlException ex) when (ex.Number == 1205) {     // Deadlock  } 

A handy thing to do to find the actual SQL error code for a given message, is to look in sys.messages in SQL Server.

e.g.

SELECT * FROM sys.messages WHERE text LIKE '%deadlock%' AND language_id=1033 

An alternative way to handle deadlocks (from SQL Server 2005 and above), is to do it within a stored procedure using the TRY...CATCH support:

BEGIN TRY     -- some sql statements END TRY BEGIN CATCH     IF (ERROR_NUMBER() = 1205)         -- is a deadlock     ELSE         -- is not a deadlock END CATCH 

There's a full example here in MSDN of how to implement deadlock retry logic purely within SQL.

Answer 2

Because I suppose you possibly want to detect deadlocks, to be able to retry the failed operation, I like to warn you for a little gotcha. I hope you’ll excuse me for being a bit off topic here.

A deadlock detected by the database will effectively rollback the transaction in which you were running (if any), while the connection is kept open in .NET. Retrying that operation (in that same connection), means it will be executed in a transactionless context and this could lead to data corruption.

It's important to be aware of this. It’s best to consider the complete connection doomed in case of a failure caused by SQL. Retrying the operation can only be done on the level where the transaction is defined (by recreating that transaction and its connection).

So when you are retrying a failed operation, please make sure you open a completely new connection and start a new transaction.