Solving a SQL Server Deadlock situation

Question

I am trying to find a solution that will resolve a recurring deadlock situation in SQL server. I have done some analysis on the deadlock graph generated by the profiler trace and have come up with this information:

The first process (spid 58) is running this query:

UPDATE cds.dbo.task_core
    SET nstate = 1 
    WHERE nmboxid = 89 AND ndrawerid = 1 
        AND nobjectid IN (SELECT
                              nobjectid 
                              FROM (SELECT
                                        nobjectid, count(nobjectid) AS counting
                                        FROM cds.dbo.task_core
                                        GROUP BY nobjectid
                                   ) task_groups
                              WHERE task_groups.counting > 1
                         )          

The second process (spid 86) is running this query:

INSERT INTO task_core (…) VALUES (…)

spid 58 is waiting for a Shared Page lock on CDS.dbo.task_core (spid 86 holds a conflicting intent exclusive (IX) lock)

spid 86 is waiting for an Intent Exclusive (IX) page lock on CDS.dbo.task_core (spid 58 holds a conflicting Update lock)

Answer 1

Off the cuff, I would guess that your innermost subquery in spid 58 is waiting on the INSERT (spid 86).

Assuming dirty reads are ok in the subquery, try adding "WITH (NOLOCK)".

(
    SELECT nobjectid, count(nobjectid) AS counting
    FROM cds.dbo.task_core WITH (NOLOCK)
    GROUP BY nobjectid
)

Answer 2

You don't need to have the derived table in the subquery, your original query can be:

UPDATE cds.dbo.task_core
    SET nstate = 1 
    WHERE nmboxid = 89 AND ndrawerid = 1 
        AND nobjectid IN (SELECT
                              nobjectid
                              FROM cds.dbo.task_core
                              GROUP BY nobjectid
                              HAVING COUNT(nobjectid)>1
                         )

but that won't prevent the deadlock. Can you add a WHERE onto the subquery? like:

UPDATE cds.dbo.task_core
    SET nstate = 1 
    WHERE nmboxid = 89 AND ndrawerid = 1 
        AND nobjectid IN (SELECT
                              nobjectid
                              FROM cds.dbo.task_core
                              WHERE nmboxid = 89 AND ndrawerid = 1   --<<<<<<<
                              GROUP BY nobjectid
                              HAVING COUNT(nobjectid)>1
                         )

this might (if an index can be used) prevent the table scan and allow the INSERT process.

Answer 3

It's good that you posted the statements and the resources. To fully understand the issue, the plans would be also of use. But I'm going to make an (educated) guess and diagnose the deadlock cause as a large scan occurring in the UPDATE's subquery:

SELECT nobjectid 
    FROM (SELECT nobjectid, count(nobjectid) AS counting
          FROM cds.dbo.task_core
           GROUP BY nobjectid
    ) task_groups
    WHERE task_groups.counting > 1

This query has to scan the entire task_core table. Always. You are hitting the deadlock at a page because the full table scan is optimized to use page locking, but you can just as well hit it a row level if you add the ROWLOCK hint.

To eliminate the deadlock, you must eliminate the conflict during the full table scan that occurs in the update. Rather than using dirty reads, you can try using row-level versioning, enable read committed snapshot isolation in the database, see Understanding Row Versioning-Based Isolation Levels.

But a much better solution would be not to scan in the first place. First, revisit the business logic requirement and your data model. Everytime you see an update that needs to look at an entire table to make a decision, that is a very very smelly code stink. If you truly find that the update cannot be rewritten in a more sensible way (I doubt), then you should consider using an indexed view. BIG_COUNT(*) expressions are allowed in indexed views and they would speed up the query significantly, in addition to eliminating the deadlock cause.

Answer 4

I suspect, as others here, that the performance of parts of your query could be improved to reduce the likelihood of deadlocks. However, I also suspect that there may be legitimate and unavoidable occasions when there really is a 'need' for a deadlock. It very much depends on your insert statement and how many rows you are adding at once, if a multi-part key in in use (e.g. are records only being added at the end of the table due to an identity key, or are they being inserted across the table due to a composite key).

The best example of why I think you may have a real problem here is: what if you are inserting a record that has nmboxid = 89 AND ndrawerid = 1, and bounces the count of objectid > 1?

I don't mean to dissuade you from investigating a proper resolution to the root cause; but on the other hand, I wonder if the easiest solution (at least as a first step) is proper handling when the deadlock does occur.