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 SQL 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;
}

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 (ERR_NUMBER() = 1205)
        -- is a deadlock
    ELSE
        -- is not a deadlock
END CATCH

There's a full example here in technet 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 of 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. I think 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

Answer 3

I use linq to do the query and updates in the following order.

Sub Commit()
Try


Transaction Scope Serialized
Begin Transaction
Check BusinessRule1
Check BusinesRule2

if BusinessRulesFailed
{ Transaction.Rollback }
else {
Query = db.out
db.SubmitChanges()
Transaction.Commit()
}

Catch DeadLockException
in 5 seconds: call Commit()

End Sub

Is it ok to do this? LINQ might still use the same connection since it's the same data context.