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?
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.
Explanation. Deadlock information can be captured in the SQL Server Error Log or by using Profiler / Server Side Trace.
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.
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.
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:
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.If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With