Cause of a process being a deadlock victim

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:

1. Could the time it takes for a transaction to execute make the associated process more likely to be flagged as a deadlock victim.
2. If I execute the select with a NOLOCK hint, will this remove the problem?
3. 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?

Solution 1:

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.

Solution 2:

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.

Solution 3:

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.