SqlException: Deadlock

Question

I have these two exceptions generated when I try to get data from SQL database in C#:

System.Data.SqlClient.SqlException: Transaction (Process ID 97) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.

OR

System.Data.SqlClient.SqlException: Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.

OR

System.Data.SqlClient.SqlException: Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

this is the code:

 using (SqlConnection con = new SqlConnection(datasource))
 {
    SqlCommand cmd = new SqlCommand("Select * from MyTable Where ID='1' ", con);
    cmd.CommandTimeout = 300;
    con.Open();
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    adapter.Fill(ds);
    con.Close();
    return ds.Tables[0];
 }

These happened every time.

Any ideas on how these can be resolved?

Answer 1

There are a couple of things you can do to lessen the number of deadlocks you receive, and some things you can do to completely eliminate them.

First off, launch SQL Server Profiler and tell it to give you a deadlock graph. Running this trace will tell you the other query which is conflicting with yours. Your query is quite simple, though I seriously doubt you have a SELECT * query off a table called MyTable in your system...

Anyway, armed with the deadlock graph and the other query, you should be able to tell what resources are deadlocking. The classic solution is to change the order of both queries such that the resources are accessed in the same order -- this avoids cycles.

Other things you can do:

• Speed up your queries by, among other things, applying the correct indexes to them.
• Enable snapshot isolation on the database and use SET TRANSACTION ISOLATION LEVEL SNAPSHOT in your transactions where appropriate. Also enable read committed with row-versioning. In many cases, this is enough to eliminate most deadlocks completely. Read about transaction isolation levels. Understand what you're doing.