The reason that the process is a victim of deadlock

Question

I have a process with Select that takes a long time to complete, on the order of 5-10 minutes.
I am not currently using NOLOCK as a hint for the MS SQL database engine.
At the same time, we have another process that makes updates and inserts them into the same database and the same tables. the process began, recently ended prematurely message

SQLEXCEPTION: The transaction was blocked when resources were blocked by another process and was selected as the victim of the deadlock.

This first process runs on other sites under the same conditions, but with smaller databases, and therefore the selection request in question takes a much shorter amount of time (about 30 seconds or so). On these other sites, I do not receive a deadlock message on these other sites. I also did not receive this message on the site that had the problem initially, but I suppose, as the database grows, I suppose that I must have crossed some threshold. Here are my questions:

• Can the time required to complete a transaction make the associated process more likely to be flagged as a deadlock victim.
• If I made a selection using the NOLOCK hint, will this fix the problem?
• I suspect that the datetime field, which is checked as part of the WHERE clause in the select statement, causes a slow search time. Can I create an index based on this field? Is appropriate?

Answer 1

Q1: Can the time it takes to complete a transaction make the associated process more likely to be flagged as a victim of a deadlock.

No. SELECT is a victim because it only read data, so the transaction is lower cost associated with it, so it is selected as a victim:

By default, the Database Engine chooses the session that performs the least expensive rollback transaction as the victim of a deadlock. Alternatively, the user can specify the priority of sessions in a deadlock situation using the SET DEADLOCK_PRIORITY . DEADLOCK_PRIORITY can be set to LOW, NORMAL or HIGH, or, alternatively, any integer value in the range (-10 to 10) can be set.

Q2. If I made a selection using the NOLOCK hint, will this fix the problem?

No. For several reasons:

• you must first try to fix the deadlock properly by investigating the root cause
• dirty reads incompatible readings .
• The correct way to specify dirty reads is to use transaction isolation levels
• there is a much better solution: read the captured snapshot .

Q3. I suspect that the datetime field, which is checked as part of the WHERE clause in the select statement, causes a slow search time. Can I create an index based on this field? Is appropriate?

Maybe. The cause of the deadlock will almost certainly be a poorly indexed database. 10 minutes of requests are acceptable in such narrow conditions that I am 100% sure that in your case it is unacceptable.

With a confidence of 99%, I declare that your dead end is surrounded by a large table scan that contradicts updates. Start by capturing a dead end graph to analyze the cause. You will most likely have to optimize your database schema. Before making any changes, read this section, “Index Development,” and the subheadings.

Answer 2

Here's how this particular deadlock problem actually occurred and how it was actually resolved. This is a fairly active database with 130,000 transactions that occur daily. The indexes in the tables in this database were originally grouped. The client asked us to make the indexes nonclustered. As soon as we did this, a dead end began. When we restored the indexes as grouped, the lock stopped.

Answer 3

The answers here are worth a try, but you should also review your code. In particular, read Polyfun's answer here: How to get rid of the deadlock in SQL Server 2005 and C #?

It explains the concurrency problem and how using "c (updlock)" in your queries can fix your deadlock situation - depending on what your code is doing. If your code runs this template, this is probably the best fix before resorting to dirty reads, etc.