Menu
A deadlock occurs when 2 processes are competing for exclusive access to a resource but is unable to obtain exclusive access to it because the other process is preventing it. This results in a standoff where neither process can proceed. The only way out of a deadlock is for one of the processes to be terminated.
SQL Server selects deadlock victim following these rules: The process with the lowest deadlock priority is set as deadlock victim. If the deadlock priority of all the processes involved in deadlock is same, then the process that is least expensive to rollback is selected as deadlock victim.
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.
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.
Q1:Could the time it takes for a transaction to execute make the associated process more likely to be flagged as a deadlock victim.
No. The SELECT is the victim because it had only read data, therefore the transaction has a lower cost associated with it so is chosen as the victim:
By default, the Database Engine chooses as the deadlock victim the session running the transaction that is least expensive to roll back. Alternatively, a user can specify the priority of sessions in a deadlock situation using the
SET DEADLOCK_PRIORITYstatement. DEADLOCK_PRIORITY can be set to LOW, NORMAL, or HIGH, or alternatively can be set to any integer value in the range (-10 to 10).
Q2. If I execute the select with a NOLOCK hint, will this remove the problem?
No. For several reasons:
Q3. I suspect that a datetime field that is checked as part of the WHERE clause in the select statement is causing the slow lookup time. Can I create an index based on this field? Is it advisable?
Probably. The cause of the deadlock is almost very likely to be a poorly indexed database.10 minutes queries are acceptable in such narrow conditions, that I'm 100% certain in your case is not acceptable.
With 99% confidence I declare that your deadlock is cased by a large table scan conflicting with updates. Start by capturing the deadlock graph to analyze the cause. You will very likely have to optimize the schema of your database. Before you do any modification, read this topic Designing Indexes and the sub-articles.
Here is how this particular deadlock problem actually occurred and how it was actually resolved. This is a fairly active database with 130K transactions occurring daily. The indexes in the tables in this database were originally clustered. The client requested us to make the indexes nonclustered. As soon as we did, the deadlocking began. When we reestablished the indexes as clustered, the deadlocking stopped.
The answers here are worth a try, but you should also review your code. Specifically have a read of Polyfun's answer here: How to get rid of deadlock in a SQL Server 2005 and C# application?
It explains the concurrency issue, and how the usage of "with (updlock)" in your queries might correct your deadlock situation - depending really on exactly what your code is doing. If your code does follow this pattern, this is likely a better fix to make, before resorting to dirty reads, etc.
Although @Remus Rusanu's is already an excelent answer, in case one is looking forward a better insight on SQL Server's Deadlock causes and trace strategies, I would suggest you to read Brad McGehee's How to Track Down Deadlocks Using SQL Server 2005 Profiler
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