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?

Did you got this exception one time or does it happen each time you try? — Alexis Dufrenoy
There must be other queries running at the same time, that's why it deadlocks. Two or more queries want access to the same data. We need to see all the queries that are running, not just the one that becomes the deadlock victim. — Colin Mackay
You can't get a deadlock from a query like that. It requires a read lock on 1 row in 1 table. Even if another process had that lock, it would have waited happily until the request timed out. To get a deadlock, typically Process1 has lock A and is waiting for lock B; while Process2 has lock B and is waiting for lock A. There must be some other queries involved. Run the Sql Server Profiler (msdn.microsoft.com/en-us/library/ms187929.aspx) and monitor what queries are executing on your database. — sheikhjabootie
More Profiler and deadlock analyzing links: (1) support.microsoft.com/default.aspx?scid=kb;en-us;832524 (2) msdn.microsoft.com/en-us/library/ms188246.aspx (3) msdn.microsoft.com/en-us/library/ms190465.aspx — Marek Grzenkowicz
Jeff Atwood had the same exception a while back: Coding Horror: Deadlocked!. His article might help :-) — Christophe Keller

Jesse C. Slicer Jesse C. Slicer · Accepted Answer · 2010-11-30T14:36:21

Not that this is going to help the deadlock issue, but you should be disposing your other IDisposable objects much like you're disposing your SqlConnection as such:

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

You might be able to avoid the lock with a locking hint in your query thusly:

Select * from MyTable with (nolock) Where ID='1'

I want to be clear though, you're allowing for reads of uncommitted data with this solution. It's a risk in a transactional system. Read this answer. Hope this helps.

SqlException: Deadlock

3 Answers