Sql-Server

消除內部查詢死鎖，我可以避免限制並行性嗎？

  • December 3, 2018

我有一個更新語句正在生成內部查詢並行死鎖。基於此連結，我相信我解決此問題的兩個選項是通過查詢提示強制 SQL Server 避免並行性OPTION (MAXDOP 1)或添加索引以降低成本，因此 SQL Server 自行決定不使用並行計劃。

只是為了看看效果，我將最大並行度設置為 1，它將執行時間從 0.5 秒增加到 1 秒。由於這個查詢經常執行，我不願意降低已經很差的性能。到目前為止，我一直無法有意義地提高更新語句的性能以消除並行性。查詢如下，這裡是執行計劃的連結。

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;

正如您將在計劃中看到的那樣，大約 50% 的估計成本用於更新非聚集索引中包含的列。這是考慮優化的最佳位置，因為我在其他地方優化時運氣不佳？還有一個觸發器可以記錄我上傳的計劃中未包含的表的更改。我不認為觸發器會導致嚴重的性能問題，但它確實存在。

這是從系統健康擴展事件中提取的死鎖報告。正如你所知道的，我對 SQL 進行了一些修飾，而不是上面顯示的內容。這些更改只是為了改進別名並刪除普遍存在的WITH(NOLOCK)查詢提示，不幸的是，這些提示是該程式碼庫的歷史遺產的一部分。

<event name="xml_deadlock_report" package="sqlserver" timestamp="2018-12-03T20:25:08.807Z">
 <data name="xml_report">
   <type name="xml" package="package0" />
   <value>
     <deadlock>
       <victim-list>
         <victimProcess id="process3b5d29468" />
       </victim-list>
       <process-list>
         <process id="process3b5d29468" taskpriority="0" logused="0" waitresource="PAGE: 10:1:18686 " waittime="4963" ownerId="3780998682" transactionname="UPDATE" lasttranstarted="2018-12-03T13:25:03.703" XDES="0x3cb856fb0" lockMode="U" schedulerid="2" kpid="8340" status="suspended" spid="352" sbid="0" ecid="18" priority="0" trancount="0" lastbatchstarted="2018-12-03T13:25:03.710" lastbatchcompleted="2018-12-03T13:25:03.707" lastattention="1900-01-01T00:00:00.707" clientapp=".Net SqlClient Data Provider" hostname="HWVPAPAC001" hostpid="5008" isolationlevel="read committed (2)" xactid="3780998682" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
           <executionStack>
             <frame procname="Foo.dbo.CallListDistributor_CallList_Rebuild_V2_Step6_Clear" line="16" stmtstart="888" stmtend="4200" sqlhandle="0x03000a0026ffbb1ed19a9e00eca2000001000000000000000000000000000000000000000000000000000000">
WITH toupdate as 
      (
             SELECT 
             a.CallList_Queue_ID
             , a.LastOpportunityCreateDate
             , a.LastOpportunity_ID
             , newLastOpportunityCreateDate = Max(c.CreateDate)
             , newLastOpportunity_ID = Max(b.Opportunity_ID)
             FROM tbl_CallList_Queue a WITH(NOLOCK)
             INNER JOIN tbl_CallList_Queue_Opportunity b WITH(NOLOCK)
             ON a.CallList_Queue_ID = b.CallList_Queue_ID
             INNER JOIN tbl_Opportunity c WITH(NOLOCK)
             ON b.Opportunity_ID = c.Opportunity_ID   
             WHERE 
             a.CallList_ID = @tmpcalllist_id
             AND
             (
                    @tmpcustomer_id IS NULL
                    OR 
                    a.Customer_ID = @tmpcustomer_id
             )
             GROUP BY
             a.CallList_Queue_ID
             , a.LastOpportunityCreateDate
             , a.LastOpportunity_ID
             HAVING    </frame>
             <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000a00197a883660fd14321400000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1    </frame>
             <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
           </executionStack>
           <inputbuf>
(@p0 int,@p1 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1   </inputbuf>
         </process>
         <process id="process3c1ea6ca8" taskpriority="0" logused="0" waitresource="PAGE: 10:1:127895 " waittime="3736" ownerId="3780998682" transactionname="UPDATE" lasttranstarted="2018-12-03T13:25:03.703" XDES="0x1b6c813050" lockMode="U" schedulerid="5" kpid="9608" status="suspended" spid="352" sbid="0" ecid="21" priority="0" trancount="0" lastbatchstarted="2018-12-03T13:25:03.710" lastbatchcompleted="2018-12-03T13:25:03.707" lastattention="1900-01-01T00:00:00.707" clientapp=".Net SqlClient Data Provider" hostname="HWVPAPAC001" hostpid="5008" isolationlevel="read committed (2)" xactid="3780998682" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
           <executionStack>
             <frame procname="Foo.dbo.CallListDistributor_CallList_Rebuild_V2_Step6_Clear" line="16" stmtstart="888" stmtend="4200" sqlhandle="0x03000a0026ffbb1ed19a9e00eca2000001000000000000000000000000000000000000000000000000000000">
WITH toupdate as 
      (
             SELECT 
             a.CallList_Queue_ID
             , a.LastOpportunityCreateDate
             , a.LastOpportunity_ID
             , newLastOpportunityCreateDate = Max(c.CreateDate)
             , newLastOpportunity_ID = Max(b.Opportunity_ID)
             FROM tbl_CallList_Queue a WITH(NOLOCK)
             INNER JOIN tbl_CallList_Queue_Opportunity b WITH(NOLOCK)
             ON a.CallList_Queue_ID = b.CallList_Queue_ID
             INNER JOIN tbl_Opportunity c WITH(NOLOCK)
             ON b.Opportunity_ID = c.Opportunity_ID   
             WHERE 
             a.CallList_ID = @tmpcalllist_id
             AND
             (
                    @tmpcustomer_id IS NULL
                    OR 
                    a.Customer_ID = @tmpcustomer_id
             )
             GROUP BY
             a.CallList_Queue_ID
             , a.LastOpportunityCreateDate
             , a.LastOpportunity_ID
             HAVING    </frame>
             <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000a00197a883660fd14321400000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1    </frame>
             <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
           </executionStack>
           <inputbuf>
(@p0 int,@p1 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1   </inputbuf>
         </process>
         <process id="process23d219468" taskpriority="0" logused="0" waitresource="PAGE: 10:1:90295 " waittime="4970" ownerId="3780999522" transactionname="UPDATE" lasttranstarted="2018-12-03T13:25:03.733" XDES="0x2a4ece8040" lockMode="U" schedulerid="10" kpid="4084" status="suspended" spid="356" sbid="0" ecid="17" priority="0" trancount="0" lastbatchstarted="2018-12-03T13:25:03.740" lastbatchcompleted="2018-12-03T13:25:03.740" lastattention="1900-01-01T00:00:00.740" clientapp=".Net SqlClient Data Provider" hostname="HWVPAPAC001" hostpid="5008" isolationlevel="read committed (2)" xactid="3780999522" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
           <executionStack>
             <frame procname="Foo.dbo.CallListDistributor_CallList_Rebuild_V2_Step6_Clear" line="16" stmtstart="888" stmtend="4200" sqlhandle="0x03000a0026ffbb1ed19a9e00eca2000001000000000000000000000000000000000000000000000000000000">
WITH toupdate as 
      (
             SELECT 
             a.CallList_Queue_ID
             , a.LastOpportunityCreateDate
             , a.LastOpportunity_ID
             , newLastOpportunityCreateDate = Max(c.CreateDate)
             , newLastOpportunity_ID = Max(b.Opportunity_ID)
             FROM tbl_CallList_Queue a WITH(NOLOCK)
             INNER JOIN tbl_CallList_Queue_Opportunity b WITH(NOLOCK)
             ON a.CallList_Queue_ID = b.CallList_Queue_ID
             INNER JOIN tbl_Opportunity c WITH(NOLOCK)
             ON b.Opportunity_ID = c.Opportunity_ID   
             WHERE 
             a.CallList_ID = @tmpcalllist_id
             AND
             (
                    @tmpcustomer_id IS NULL
                    OR 
                    a.Customer_ID = @tmpcustomer_id
             )
             GROUP BY
             a.CallList_Queue_ID
             , a.LastOpportunityCreateDate
             , a.LastOpportunity_ID
             HAVING    </frame>
             <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000a00197a883660fd14321400000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1    </frame>
             <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
           </executionStack>
           <inputbuf>
(@p0 int,@p1 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1   </inputbuf>
         </process>
         <process id="process2a1982d848" taskpriority="0" logused="0" waitresource="PAGE: 10:1:18686 " waittime="4970" ownerId="3780999522" transactionname="UPDATE" lasttranstarted="2018-12-03T13:25:03.733" XDES="0x1bd782e040" lockMode="U" schedulerid="5" kpid="8040" status="suspended" spid="356" sbid="0" ecid="18" priority="0" trancount="0" lastbatchstarted="2018-12-03T13:25:03.740" lastbatchcompleted="2018-12-03T13:25:03.740" lastattention="1900-01-01T00:00:00.740" clientapp=".Net SqlClient Data Provider" hostname="HWVPAPAC001" hostpid="5008" isolationlevel="read committed (2)" xactid="3780999522" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
           <executionStack>
             <frame procname="Foo.dbo.CallListDistributor_CallList_Rebuild_V2_Step6_Clear" line="16" stmtstart="888" stmtend="4200" sqlhandle="0x03000a0026ffbb1ed19a9e00eca2000001000000000000000000000000000000000000000000000000000000">
WITH toupdate as 
      (
             SELECT 
             a.CallList_Queue_ID
             , a.LastOpportunityCreateDate
             , a.LastOpportunity_ID
             , newLastOpportunityCreateDate = Max(c.CreateDate)
             , newLastOpportunity_ID = Max(b.Opportunity_ID)
             FROM tbl_CallList_Queue a WITH(NOLOCK)
             INNER JOIN tbl_CallList_Queue_Opportunity b WITH(NOLOCK)
             ON a.CallList_Queue_ID = b.CallList_Queue_ID
             INNER JOIN tbl_Opportunity c WITH(NOLOCK)
             ON b.Opportunity_ID = c.Opportunity_ID   
             WHERE 
             a.CallList_ID = @tmpcalllist_id
             AND
             (
                    @tmpcustomer_id IS NULL
                    OR 
                    a.Customer_ID = @tmpcustomer_id
             )
             GROUP BY
             a.CallList_Queue_ID
             , a.LastOpportunityCreateDate
             , a.LastOpportunity_ID
             HAVING    </frame>
             <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000a00197a883660fd14321400000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1    </frame>
             <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
           </executionStack>
           <inputbuf>
(@p0 int,@p1 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1   </inputbuf>
         </process>
         <process id="process11628a0ca8" taskpriority="0" logused="0" waitresource="PAGE: 10:1:127895 " waittime="4966" ownerId="3780999522" transactionname="UPDATE" lasttranstarted="2018-12-03T13:25:03.733" XDES="0x28b357ac90" lockMode="U" schedulerid="7" kpid="6404" status="suspended" spid="356" sbid="0" ecid="24" priority="0" trancount="0" lastbatchstarted="2018-12-03T13:25:03.740" lastbatchcompleted="2018-12-03T13:25:03.740" lastattention="1900-01-01T00:00:00.740" clientapp=".Net SqlClient Data Provider" hostname="HWVPAPAC001" hostpid="5008" isolationlevel="read committed (2)" xactid="3780999522" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
           <executionStack>
             <frame procname="Foo.dbo.CallListDistributor_CallList_Rebuild_V2_Step6_Clear" line="16" stmtstart="888" stmtend="4200" sqlhandle="0x03000a0026ffbb1ed19a9e00eca2000001000000000000000000000000000000000000000000000000000000">
WITH toupdate as 
      (
             SELECT 
             a.CallList_Queue_ID
             , a.LastOpportunityCreateDate
             , a.LastOpportunity_ID
             , newLastOpportunityCreateDate = Max(c.CreateDate)
             , newLastOpportunity_ID = Max(b.Opportunity_ID)
             FROM tbl_CallList_Queue a WITH(NOLOCK)
             INNER JOIN tbl_CallList_Queue_Opportunity b WITH(NOLOCK)
             ON a.CallList_Queue_ID = b.CallList_Queue_ID
             INNER JOIN tbl_Opportunity c WITH(NOLOCK)
             ON b.Opportunity_ID = c.Opportunity_ID   
             WHERE 
             a.CallList_ID = @tmpcalllist_id
             AND
             (
                    @tmpcustomer_id IS NULL
                    OR 
                    a.Customer_ID = @tmpcustomer_id
             )
             GROUP BY
             a.CallList_Queue_ID
             , a.LastOpportunityCreateDate
             , a.LastOpportunity_ID
             HAVING    </frame>
             <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000a00197a883660fd14321400000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1    </frame>
             <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
           </executionStack>
           <inputbuf>
(@p0 int,@p1 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1   </inputbuf>
         </process>
         <process id="process3bd04b088" taskpriority="0" logused="0" waitresource="PAGE: 10:1:90295 " waittime="3736" ownerId="3780998682" transactionname="UPDATE" lasttranstarted="2018-12-03T13:25:03.703" XDES="0x2391ef5ca0" lockMode="U" schedulerid="8" kpid="8168" status="suspended" spid="352" sbid="0" ecid="23" priority="0" trancount="0" lastbatchstarted="2018-12-03T13:25:03.710" lastbatchcompleted="2018-12-03T13:25:03.707" lastattention="1900-01-01T00:00:00.707" clientapp=".Net SqlClient Data Provider" hostname="HWVPAPAC001" hostpid="5008" isolationlevel="read committed (2)" xactid="3780998682" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
           <executionStack>
             <frame procname="Foo.dbo.CallListDistributor_CallList_Rebuild_V2_Step6_Clear" line="16" stmtstart="888" stmtend="4200" sqlhandle="0x03000a0026ffbb1ed19a9e00eca2000001000000000000000000000000000000000000000000000000000000">
WITH toupdate as 
      (
             SELECT 
             a.CallList_Queue_ID
             , a.LastOpportunityCreateDate
             , a.LastOpportunity_ID
             , newLastOpportunityCreateDate = Max(c.CreateDate)
             , newLastOpportunity_ID = Max(b.Opportunity_ID)
             FROM tbl_CallList_Queue a WITH(NOLOCK)
             INNER JOIN tbl_CallList_Queue_Opportunity b WITH(NOLOCK)
             ON a.CallList_Queue_ID = b.CallList_Queue_ID
             INNER JOIN tbl_Opportunity c WITH(NOLOCK)
             ON b.Opportunity_ID = c.Opportunity_ID   
             WHERE 
             a.CallList_ID = @tmpcalllist_id
             AND
             (
                    @tmpcustomer_id IS NULL
                    OR 
                    a.Customer_ID = @tmpcustomer_id
             )
             GROUP BY
             a.CallList_Queue_ID
             , a.LastOpportunityCreateDate
             , a.LastOpportunity_ID
             HAVING    </frame>
             <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000a00197a883660fd14321400000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1    </frame>
             <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
           </executionStack>
           <inputbuf>
(@p0 int,@p1 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1   </inputbuf>
         </process>
         <process id="process1f85527c28" taskpriority="0" logused="10000" waittime="5003" schedulerid="8" kpid="7980" status="suspended" spid="352" sbid="0" ecid="25" priority="0" trancount="0" lastbatchstarted="2018-12-03T13:25:03.710" lastbatchcompleted="2018-12-03T13:25:03.707" lastattention="1900-01-01T00:00:00.707" clientapp=".Net SqlClient Data Provider" hostname="HWVPAPAC001" hostpid="5008" isolationlevel="read committed (2)" xactid="3780998682" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
           <executionStack>
             <frame procname="Foo.dbo.CallListDistributor_CallList_Rebuild_V2_Step6_Clear" line="16" stmtstart="888" stmtend="4200" sqlhandle="0x03000a0026ffbb1ed19a9e00eca2000001000000000000000000000000000000000000000000000000000000">
WITH toupdate as 
      (
             SELECT 
             a.CallList_Queue_ID
             , a.LastOpportunityCreateDate
             , a.LastOpportunity_ID
             , newLastOpportunityCreateDate = Max(c.CreateDate)
             , newLastOpportunity_ID = Max(b.Opportunity_ID)
             FROM tbl_CallList_Queue a WITH(NOLOCK)
             INNER JOIN tbl_CallList_Queue_Opportunity b WITH(NOLOCK)
             ON a.CallList_Queue_ID = b.CallList_Queue_ID
             INNER JOIN tbl_Opportunity c WITH(NOLOCK)
             ON b.Opportunity_ID = c.Opportunity_ID   
             WHERE 
             a.CallList_ID = @tmpcalllist_id
             AND
             (
                    @tmpcustomer_id IS NULL
                    OR 
                    a.Customer_ID = @tmpcustomer_id
             )
             GROUP BY
             a.CallList_Queue_ID
             , a.LastOpportunityCreateDate
             , a.LastOpportunity_ID
             HAVING    </frame>
             <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000a00197a883660fd14321400000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1    </frame>
             <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
           </executionStack>
           <inputbuf>
(@p0 int,@p1 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1   </inputbuf>
         </process>
         <process id="process3b3b22ca8" taskpriority="0" logused="10000" waittime="5057" schedulerid="7" kpid="5344" status="suspended" spid="352" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-03T13:25:03.710" lastbatchcompleted="2018-12-03T13:25:03.707" lastattention="1900-01-01T00:00:00.707" clientapp=".Net SqlClient Data Provider" hostname="HWVPAPAC001" hostpid="5008" loginname="NGIC\AGTC-APP-HCI-Prod" isolationlevel="read committed (2)" xactid="3780998682" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
           <executionStack>
             <frame procname="Foo.dbo.CallListDistributor_CallList_Rebuild_V2_Step6_Clear" line="16" stmtstart="888" stmtend="4200" sqlhandle="0x03000a0026ffbb1ed19a9e00eca2000001000000000000000000000000000000000000000000000000000000">
WITH toupdate as 
      (
             SELECT 
             a.CallList_Queue_ID
             , a.LastOpportunityCreateDate
             , a.LastOpportunity_ID
             , newLastOpportunityCreateDate = Max(c.CreateDate)
             , newLastOpportunity_ID = Max(b.Opportunity_ID)
             FROM tbl_CallList_Queue a WITH(NOLOCK)
             INNER JOIN tbl_CallList_Queue_Opportunity b WITH(NOLOCK)
             ON a.CallList_Queue_ID = b.CallList_Queue_ID
             INNER JOIN tbl_Opportunity c WITH(NOLOCK)
             ON b.Opportunity_ID = c.Opportunity_ID   
             WHERE 
             a.CallList_ID = @tmpcalllist_id
             AND
             (
                    @tmpcustomer_id IS NULL
                    OR 
                    a.Customer_ID = @tmpcustomer_id
             )
             GROUP BY
             a.CallList_Queue_ID
             , a.LastOpportunityCreateDate
             , a.LastOpportunity_ID
             HAVING    </frame>
             <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000a00197a883660fd14321400000000000000000000000000000000000000000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1    </frame>
             <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
           </executionStack>
           <inputbuf>
(@p0 int,@p1 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[CallListDistributor_CallList_Rebuild_V2_Step6_Clear] @calllist_id = @p0, @customer_id = @p1   </inputbuf>
         </process>
       </process-list>
       <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="process2a1982d848" mode="U" requestType="wait" />
           </owner-list>
           <waiter-list>
             <waiter id="process3b5d29468" 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="process11628a0ca8" mode="U" requestType="wait" />
           </owner-list>
           <waiter-list>
             <waiter id="process3c1ea6ca8" 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="process3b3b22ca8" mode="U" />
           </owner-list>
           <waiter-list>
             <waiter id="process23d219468" 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="process3b3b22ca8" mode="U" />
           </owner-list>
           <waiter-list>
             <waiter id="process2a1982d848" 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="process3b3b22ca8" mode="U" />
           </owner-list>
           <waiter-list>
             <waiter id="process11628a0ca8" 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="process23d219468" mode="U" requestType="wait" />
           </owner-list>
           <waiter-list>
             <waiter id="process3bd04b088" mode="U" requestType="wait" />
           </waiter-list>
         </pagelock>
         <exchangeEvent id="Port2832c76700" WaitType="e_waitPortClose" nodeId="45">
           <owner-list>
             <owner id="process3bd04b088" />
             <owner id="process3b5d29468" />
             <owner id="process3c1ea6ca8" />
           </owner-list>
           <waiter-list>
             <waiter id="process1f85527c28" />
           </waiter-list>
         </exchangeEvent>
         <exchangeEvent id="Port2832c76100" WaitType="e_waitPortOpen" nodeId="11">
           <owner-list>
             <owner id="process1f85527c28" />
           </owner-list>
           <waiter-list>
             <waiter id="process3b3b22ca8" />
           </waiter-list>
         </exchangeEvent>
       </resource-list>
     </deadlock>
   </value>
 </data>
</event>

這是一個普通的死鎖。這是資源列表，轉換為顯示每個程序的 spid：

<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>

您可以在上面看到 spid 356 具有 352 正在等待的頁 U 鎖，並且 spid 352 具有 spid 356 正在等待的頁 U 鎖。下面的“exchangeEvent”鎖在 spid 352 的程序中沒有顯示任何這樣的所有權週期：

 <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>

我手頭沒有確切的數字，但這個儲存過程每天至少執行 500 次，但對於我們的大客戶來說可能更多。

在這種規模下，我只需將其包裝在事務中，並在事務頂部呼叫sp_getapplock並強制它一次執行一個。

引用自：https://dba.stackexchange.com/questions/224036

相關問答

Sql-Server

並行執行儲存過程

  • October 18, 2021
檢視問答
Sql-Server

如何將所有複製設置為 DEADLOCK_PRIORITY LOW？

  • May 18, 2021
檢視問答
Sql-Server

主鍵列並發更新導致死鎖

  • March 20, 2021
檢視問答
Sql-Server

具有可重複讀隔離級別的死鎖

  • January 8, 2021
檢視問答
Sql-Server

在後台作業中擷取死鎖事件？

  • December 14, 2020
檢視問答
Sql-Server

死鎖鏈，但沒有死鎖？

  • December 4, 2020
檢視問答