Solving a SQL Server Deadlock situation
- by mjh41
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)
