patternsqlMinor

Eliminating a intra query deadlock, can I avoid restricting parallelism?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

candeadlockqueryeliminatingavoidparallelismrestrictingintra

Problem

I have an update statement that is generating intra query parallelism deadlock. Based on this link I believe my two options to resolve this issue is to force SQL Server to avoid parallelism via an OPTION (MAXDOP 1) query hint or to add an index to reduce the cost so SQL Server decides on its own not to use a parallel plan.

Just to see the impact I set the max degrees of parallelism to 1 and it doubled the execution time from 0.5 seconds to 1 second. Since this query is run frequently I don't feel comfortable degrading the already poor performance. So far I've been unable to meaningfully improve the performance of the update statement to remove the parallelism. The query is below, and here is a link to the execution plan.

DECLARE @calllist_id int = 1;
DECLARE @customer_id int = NULL;

WITH ToUpdate AS 
(
    SELECT 
        CLQ.CallList_Queue_ID
        , newLastOpportunityCreateDate = MAX(O.CreateDate)
        , newLastOpportunity_ID = MAX(CLQO.Opportunity_ID)
    FROM tbl_CallList_Queue CLQ
        INNER JOIN tbl_CallList_Queue_Opportunity CLQO ON CLQ.CallList_Queue_ID = CLQO.CallList_Queue_ID
        INNER JOIN tbl_Opportunity O ON CLQO.Opportunity_ID = O.Opportunity_ID   
    WHERE 
        CLQ.CallList_ID = @calllist_id
        AND
        (
             @customer_id IS NULL
             OR 
             CLQ.Customer_ID = @customer_id
        )
    GROUP BY
        CLQ.CallList_Queue_ID
        , CLQ.LastOpportunityCreateDate
        , CLQ.LastOpportunity_ID
    HAVING
    (
        CLQ.LastOpportunityCreateDate IS NULL
        OR
        CLQ.LastOpportunityCreateDate < MAX(O.CreateDate)
        OR
        CLQ.LastOpportunity_ID IS NULL
        OR
        CLQ.LastOpportunity_ID < MAX(CLQO.Opportunity_ID)
    )
)
UPDATE CLQ
SET 
    LastOpportunityCreateDate = TU.newLastOpportunityCreateDate
    , LastOpportunity_ID = TU.newLastOpportunity_ID
FROM tbl_CallList_Queue CLQ 
    INNER JOIN ToUpdate TU on CLQ.CallList_Queue_ID = TU.CallList_Queue_ID;

Solution

This is an ordinary deadlock. Here's the resource list, transformed to show the spid for each process:

You can see above that spid 356 has page U locks that 352 is waiting for, and spid 352 has page U locks that spid 356 is waiting for. The 'exchangeEvent' locks below don't show any such cycle of ownership among the processes for spid 352:

I don't have firm numbers at hand but this stored proc runs at least 500 times a day, but probably more for our bigger customers.

At this scale I would just wrap this in a transaction with a call to sp_getapplock at the top of the transaction and force it to run one-at-a-time.

Code Snippets

<resource-list>

      <pagelock fileid="1" pageid="18686" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock34211ea00" mode="U" associatedObjectId="72057605387976704">
        <owner-list>
          <owner id="356-2" mode="U" requestType="wait" />
        </owner-list>
        <waiter-list>
          <waiter id="352-1" mode="U" requestType="wait" />
        </waiter-list>
      </pagelock>
      <pagelock fileid="1" pageid="127895" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock111730a200" mode="U" associatedObjectId="72057605387976704">
        <owner-list>
          <owner id="356-3" mode="U" requestType="wait" />
        </owner-list>
        <waiter-list>
          <waiter id="352-2" mode="U" requestType="wait" />
        </waiter-list>
      </pagelock>
      <pagelock fileid="1" pageid="90295" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock281980c00" mode="U" associatedObjectId="72057605387976704">
        <owner-list>
          <owner id="352-5" mode="U" />
        </owner-list>
        <waiter-list>
          <waiter id="356-1" mode="U" requestType="wait" />
        </waiter-list>
      </pagelock>
      <pagelock fileid="1" pageid="18686" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock34211ea00" mode="U" associatedObjectId="72057605387976704">
        <owner-list>
          <owner id="352-5" mode="U" />
        </owner-list>
        <waiter-list>
          <waiter id="356-2" mode="U" requestType="wait" />
        </waiter-list>
      </pagelock>
      <pagelock fileid="1" pageid="127895" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock111730a200" mode="U" associatedObjectId="72057605387976704">
        <owner-list>
          <owner id="352-5" mode="U" />
        </owner-list>
        <waiter-list>
          <waiter id="356-3" mode="U" requestType="wait" />
        </waiter-list>
      </pagelock>
      <pagelock fileid="1" pageid="90295" dbid="10" subresource="FULL" objectname="Foo.dbo.tbl_CallList_Queue" id="lock281980c00" mode="U" associatedObjectId="72057605387976704">
        <owner-list>
          <owner id="356-1" mode="U" requestType="wait" />
        </owner-list>
        <waiter-list>
          <waiter id="352-3" mode="U" requestType="wait" />
        </waiter-list>
      </pagelock>

<exchangeEvent id="Port2832c76700" WaitType="e_waitPortClose" nodeId="45">
    <owner-list>
      <owner id="352-3" />
      <owner id="352-1" />
      <owner id="352-2" />
    </owner-list>
    <waiter-list>
      <waiter id="352-4" />
    </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port2832c76100" WaitType="e_waitPortOpen" nodeId="11">
    <owner-list>
      <owner id="352-4" />
    </owner-list>
    <waiter-list>
      <waiter id="352-5" />
    </waiter-list>
  </exchangeEvent>
</resource-list>

Context

StackExchange Database Administrators Q#224036, answer score: 3

Revisions (0)

No revisions yet.