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?

Grampus

I (finally) found the documentation for the error code: msdn.microsoft.com/en-us/library/aa337376.aspx. You can also find this through SQL Server itself: select * from master.dbo.sysmessages where error=1205 – Mangonel

Here is the list of ALL Sql Server error codes: learn.microsoft.com/en-us/sql/relational-databases/… – Tremolant

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.

Postgraduate

Note the error codes are vendor specific, so 1205 is a deadlock for SQL Server, but it may be different for Oracle, MySQL, etc. – Goldbrick

Depending on the data layer, the SqlException may be wrapped in another one. So we may need need to catch any exception kind and check them then, if they are not directly a deadlock exception, recursively check their InnerException. – Dulse

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.

Tollhouse

Why do you need a completely new connection? I've posted a question about this answer here. – Biquadratic

Answer 3

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.

Cogitable