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?

Maffick

Did you got this exception one time or does it happen each time you try? – Tammietammuz

These hapend each time when I try. – Maffick

My query: Select * From MyTable Where PRODUCT_ID = '1' – Maffick

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. – Hom

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. – Genocide

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 – Monoicous

Jeff Atwood had the same exception a while back: Coding Horror: Deadlocked!. His article might help :-) – Bible

Answer 1

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.

Armandinaarmando

Answer 2

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.

Heptane

Answer 3

Basically, the SQL server concurrency model makes it so you can never avoid this exception (eg. completely unrelated transaction might block eachother if they happen to lock the same index page or something). The best you can do is keep your transactions short to reduce the likelyhood, and if you get the exception, do what it says and retry the transaction.

Fa

Your're confusing the concepts of blocking and deadlocking. It's not true at all that this exception is unavoidable. – Heptane

No, I'm not. If you use the default locking (sometimes rowlock, sometimes pagelock), it is easy to see that two unrelated transactions might accidentally deadlock eachother (at least if they update two tables inside the transaction and you don't use hints to make sure that tables are always read in the same order). For the rowlock case, it's less obvious, but indexes can bite you here. – Fa