Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SqlException: Deadlock

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?

like image 576
Harold Sota Avatar asked Nov 30 '10 10:11

Harold Sota


People also ask

How do you fix a deadlock problem?

Deadlock frequency can sometimes be reduced by ensuring that all applications access their common data in the same order - meaning, for example, that they access (and therefore lock) rows in Table A, followed by Table B, followed by Table C, and so on.

How can I get deadlock in SQL Server?

Explanation. Deadlock information can be captured in the SQL Server Error Log or by using Profiler / Server Side Trace.

How can avoid deadlock in SQL?

Useful ways to avoid and minimize SQL Server deadlocksTry to keep transactions short; this will avoid holding locks in a transaction for a long period of time. Access objects in a similar logical manner in multiple transactions. Create a covering index to reduce the possibility of a deadlock.

What causes deadlock in SQL Server?

A deadlock problem occurs when two (or more than two) operations already want to access resources locked by the other one. In this circumstance, database resources are affected negatively because both processes are constantly waiting for each other. This contention issue is terminated by the SQL Server intervention.


1 Answers

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.
like image 115
Dave Markle Avatar answered Oct 12 '22 19:10

Dave Markle