How to catch SqlException caused by deadlock?

Here is a C# 6 way of detecting deadlocks.

try
{
    //todo: Execute SQL. 
    //IMPORTANT, if you used Connection.BeginTransaction(), this try..catch must surround that code. You must rollback the original transaction, then recreate it and re-run all the code.
}
catch (SqlException ex) when (ex.Number == 1205)
{
    //todo: Retry SQL
}

Make sure this try..catch surrounds your entire transaction. According to @Steven (see his answer for details), when the sql command fails due to the deadlock, it causes the transaction to be rolled back and, if you don't recreate the transaction, your retry will execute outside of the context of the transaction and can result in data inconsistencies.

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.

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.