Most people writing code about databases know that in rare cases, a SQL transaction can become deadlocked and killed by the server, but still be able to be retried successfully. (I will not be going into the theory in this post. More info here.) However, most developers forget to code for this because it is typically a very rare event. (If it happens often, you have other problems.) I've gotten plenty of questions through the years about how to code for this, and I thought I would write a quick post on how to do this in C# for reference.
What you need to do, is wrap all your calls to SQL commands with the following code:
bool victimOfDeadlock;
do
{
try
{
da.Fill(ds); //<--This is any line of code that executes a sql command that might become a victim of a deadlock.
victimOfDeadlock = false;
}
catch (SqlException sex)
{
victimOfDeadlock = sex.Number == 1205;
if (!victimOfDeadlock) throw;
}
} while (victimOfDeadlock);
This can/should be used around any call to your database, but not around commands that call functions that call the DB. In other words, only use it when things like .Fill, .ExecuteQuery, .ExecuteNonQuery, .ExecuteReader are actually called. It is a waste to place it around code that calls code that calls the DB, unless you are calling 3rd party code that you do not control AND know that retrying the statement won't have any unintended consequences. (If that isn't clear, I'm sure I'll be getting e-mails from a few of you. 8o) )
(Side note: Quite obviously, this would be a nightmare if you aren't using best practices and keeping 100% of your actual SQL calls inside a reusable data access layer in our code. Having a reusable DAL is critical to most every client side thing you want to do with SQL, unless you want to quickly descend into nightmare of maintaining the same code snippets repeatedly scattered throughout your entire code base.)
I deliberately kept this post rather brief, and skipped over a lot of theory. If a few people post that they want me to post the theory, I can write up more later.