How can I prevent becoming a deadlock victim?

Question

I got this exception msg:

Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.

The only line of my code that was implicated in the Stack Trace was the last one here:

public static DataTable ExecuteSQLReturnDataTable(string sql, CommandType cmdType, params SqlParameter[] parameters)
{
    using (DataSet ds = new DataSet())
    using (SqlConnection connStr = new SqlConnection(CPSConnStr))
    using (SqlCommand cmd = new SqlCommand(sql, connStr))
    {
        cmd.CommandType = cmdType;
        cmd.CommandTimeout = EXTENDED_TIMEOUT;
        foreach (var item in parameters)
        {
            cmd.Parameters.Add(item);
        }

        try
        {
            cmd.Connection.Open();
            new SqlDataAdapter(cmd).Fill(ds);

This is a general-purpose method that I use for all sorts of queries; I haven't changed it recently, nor have I ever seen this particular exception before.

What can I do to guard against this exception being thrown again?

Answer 1

You can catch the deadlock exception and retry X number of times before giving up.

There's no magic solution to avoid deadlocks. If SQL Server detects a deadlock it's going to pick one of the processes to kill. In some cases you may have had deadlocks where your process was the one that was lucky enough to continue.

You can use SQL Profiler to capture the deadlocks. I had to do this in the past to try and figure out what was actually causing the deadlocks. The less often this happens the harder it is to track down. In our test environment we just created some testing code to hammer the database from a few different machines to try and cause a deadlock.

In our case we made some changes to our indexes and modified database triggers to reduce the deadlocks as best we could. In the end we still had to implement the retries as a "just in case".