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.