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 theSET 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:
- 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.