Cause of a process being a deadlock victim

Question

I have a process with a Select which takes a long time to finish  on the order of 5 to 10 minutes  I am currently not using NOLOCK as a hint to the MS SQL database engine At the same time we have another process doing updates and inserts into the same database and same tables  The first process has started  recently to end prematurely with a message  SQLEXCEPTION  Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim   This first process is running at other sites in identical conditions but with smaller databases and thus the select statement in question takes a much shorter period of time  on the order of 30 seconds or so       In these other sites  I don t get the deadlock message in these other sites     I also did not get this message at the site that is having the problem initially  but  I assume  as the database has grown  I believe I must have crossed some threshold       Here are my questions   Could the time it takes for a transaction to execute make the associated process more likely to be flagged as a deadlock victim  If I execute the select with a NOLOCK hint  will this remove the problem  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

User · Answer

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

User · Answer

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 PRIORITY statement    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     you should first try to eliminate the deadlock properly  by investigating the root cause dirty reads are inconsistent reads  the proper way to specify dirty reads is to use transaction isolation levels there is a much better solution  read committed snapshot       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

User · Answer

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

User · Answer

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

[sql-server] Cause of a process being a deadlock victim

Examples related to sql-server

Examples related to deadlock