鎖死指南

適用於： SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW)

本文會深入討論 SQL Server 資料庫引擎中的鎖死。 鎖死會導致資料庫中競爭且同時鎖定，通常是在多步驟異動中。 如需交易鎖定的詳細資訊，請參閱交易鎖定和資料列版本設定指南 (機器翻譯)。

如需在 Azure SQL Database 中發現和防止鎖死更具體的資訊，請參閱分析及防止 Azure SQL Database 中的鎖死 (機器翻譯)。

了解鎖死

當兩個或多個工作各自具鎖定某個資源，但其他工作嘗試要鎖定此資源，而造成工作永久封鎖彼此時，會發生鎖死。 例如：

• 交易 A 需要資料列 1 的共用鎖定。

• 交易 B 需要資料列 2 的共用鎖定。

• 交易 A 現在要求資料列 2 的獨佔鎖定，但會被封鎖直到交易 B 完成並釋出對資料列 2 的共用鎖定為止。

• 交易 B 現在要求資料列 1 的獨佔鎖定，但會被封鎖直到交易 A 完成並釋出對資料列 1 的共用鎖定為止。

等到異動 B 完成後，異動 A 才能完成，但異動 B 卻被異動 A 封鎖了。這個狀況也稱為「循環相依性」：異動 A 相依於異動 B，且異動 B 因為相依於異動 A 而形成封閉式循環。

在鎖死中的這兩個異動會一直等下去，除非由外部處理序解除此鎖死。 SQL Server 資料庫引擎鎖死監視器會定期檢查是否有工作處於鎖死狀態。 如果監視器偵測到循環相依性，它會選擇其中一個工作作為犧牲者，以錯誤來結束其交易。 這樣另一個工作便可以完成其交易。 因為錯誤而結束異動的應用程式可以重試異動，通常在另一個鎖死異動完成之後便會完成。

死結通常會和一般的封鎖產生混淆。 當交易要求鎖定的資源被另一個交易鎖定時，提出要求的交易會等待鎖定釋出。 除非設定了 LOCK_TIMEOUT，否則 SQL Server 異動預設不會逾時。 提出要求的異動會被封鎖，但非鎖死，因為提出要求的異動尚未封鎖目前擁有鎖定的異動。 最後，主控異動會完成並釋出鎖定，然後提出要求的異動會被授與鎖定並繼續進行。 鎖死會近乎立即解決，而封鎖理論上可以無限期持續。 死結 (Deadlock) 有時也稱為致命環節 (Deadly Embrace)。

鎖死可能發生在任何具有多執行緒的系統上，而不只是在關聯式資料庫管理系統，並且可能發生在資料庫物件鎖定之外的資源。 例如，在多執行緒作業系統中的一個執行緒可能取得一或多個資源，像是記憶體區塊。 若要取得的資源目前為另一個執行緒所擁有，前者的執行緒可能必須等候擁有資源的執行緒釋放目標資源。 等候的執行緒便是所謂的與擁有該特定資源的執行緒具有依存性。 在 SQL Server 資料庫引擎執行個體中，當工作階段取得非資料庫資源 (例如記憶體或執行緒) 時，可能會鎖死。

在上圖中，在 Part 資料表鎖定資源上，交易 T1 相依於交易 T2。 同樣的，在 Supplier 資料表鎖定資源上，交易 T2 相依於交易 T1。 由於這些相依性形成循環，異動 T1 與 T2 之間便構成鎖死。

當資料表已分割，且 ALTER TABLE 的 LOCK_ESCALATION 設定為 AUTO 時，也可能發生鎖死。 當 LOCK_ESCALATION 設定為 AUTO 時，會藉由讓 SQL Server 資料庫引擎鎖定 HoBT 層級 (而不是資料表層級) 的資料表分割區來增加並行。 但是，當個別交易在資料表中保留資料分割鎖定，而且想要鎖定其他交易資料分割上的某個地方時，這就會造成死結。 您可以將 設定 LOCK_ESCALATION 為 TABLE來避免這種類型的鎖死。 不過，此設定會藉由強制對分割區進行大型更新來等候資料表鎖定，以減少並行。

偵測和結束鎖死

當兩個或多個工作各自具鎖定某個資源，但其他工作嘗試要鎖定此資源，而造成工作永久封鎖彼此時，會發生鎖死。 下圖顯示死結狀態的高階檢視，其中：

• 工作 T1 有資源 R1 的鎖定 (由 R1 到 T1 的箭頭所表示)，並且已要求資源 R2 的鎖定 (由 T1 到 R2 的箭頭所表示)。

• 工作 T2 有資源 R2 的鎖定 (由 R2 到 T2 的箭頭所表示)，並且已要求資源 R1 的鎖定 (由 T2 到 R1 的箭頭所表示)。

• 因為在有資源可用之前，沒有一項工作可以繼續，而在有工作繼續之前，沒有一項資源可以釋放，所以會有死結狀態。

  

SQL Server 資料庫引擎會自動偵測 SQL Server 內的鎖死循環。 SQL Server 資料庫引擎會選擇其中一個工作階段作為鎖死犧牲者，讓目前交易終止並產生錯誤，中斷鎖死。

可能鎖死的資源

每個使用者工作階段可能有一或多個工作代表工作階段執行，其中每個工作可能會取得或等待取得資源。 下列類型的資源會導致封鎖，而造成死結。

• 鎖定。 等待取得像是物件、分頁、資料列、中繼資料和應用程式等資源的鎖定，可能會導致鎖死。 例如，交易 T1 有資料列 r1 的共用 (S) 鎖定，並且正在等待取得 r2 的獨佔 (X) 鎖定。 交易 T2 有 r2 的共用 (S) 鎖定，並且正在等待取得資料列 r1 的獨佔 (X) 鎖定。 這樣會產生鎖定循環，因為 T1 和 T2 都在等待對方釋放已鎖定的資源。

• 工作者執行緒。 等待可用背景工作執行緒的佇列工作，可能會導致鎖死。 如果佇列工作擁有正在封鎖所有工作者執行緒的資源，便會產生鎖死。 例如，工作階段 S1 啟動交易，並且取得資料列 r1 的共用 (S) 鎖定，然後進入睡眠。 在所有可用工作者執行緒上執行的使用中工作階段，正在嘗試取得資料列 r1 的獨佔 (X) 鎖定。 因為工作階段 S1 無法取得工作者執行緒，所以它無法認可異動並釋放資料列 r1 的鎖定。 這樣會產生死結。

• 記憶體。 當並行要求正在等待記憶體授權，但可用記憶體不足而無法滿足授權時，便會發生鎖死。 例如，兩個並行查詢 Q1 和 Q2 以使用者定義函式執行，分別取得 10 MB 和 20 MB 記憶體。 如果每個查詢需要 30 MB，而可用的總記憶體是 20 MB，則 Q1 和 Q2 必須等待對方釋出記憶體，這樣會導致鎖死。

• 與平行查詢執行相關的資源。 與交換通訊埠建立關聯的協調器、產生器或取用者執行緒，若包含至少一個不屬於平行查詢的其他處理序，通常可能會彼此封鎖，而導致鎖死。 而且，當平行查詢開始執行時，SQL Server 會根據目前工作負載來判斷平行程度或背景工作執行緒數目。 如果系統工作負載意外變更，例如有新查詢開始在伺服器上執行或系統的工作者執行緒用盡，此時會發生死結。

• Multiple Active Result Set (MARS) 資源。 這些資源在 MARS 下是用來控制多個使用中要求的交錯情形。 如需詳細資訊，請參閱在 SQL Server Native Client 使用 Multiple Active Result Sets (MARS)。

  • 使用者資源。 當執行緒正在等待的資源可能受使用者應用程式控制時，此資源會視為外部或使用者資源，並且如同鎖定處理。

  • 工作階段 Mutex。 工作階段中執行的工作為交錯的，這表示同時只能有一個工作在此工作階段執行。 工作必須具有對工作階段 Mutex 的獨佔存取權才能執行。

  • 交易 Mutex。 交易中執行的所有工作為交錯的，這表示同時只能有一個工作在此交易執行。 工作必須具有對交易 Mutex 的獨佔存取權才能執行。

    工作必須取得工作階段 Mutex，才能在 MARS 下執行。 如果工作在交易下執行，則它必須接著取得交易 Mutex。 這可保證在給定工作階段和給定交易中，同時只有一個工作使用中。 一旦取得所需的 Mutex，工作即可執行。 當工作完成或在要求中途退出時，會以取得 Mutex 的相反順序，先釋放異動 Mutex，接著再釋放工作階段 Mutex。 不過，這些資源可能會發生死結。 在下列虛擬程式碼中，兩個工作 (使用者要求 U1 和使用者要求 U2) 在同一個工作階段中執行。

    U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");
    U2:    Rs2=Command2.Execute("select colA from sometable");
    

    從使用者要求 U1 執行的預存程序已取得工作階段 Mutex。 如果預存程序花很長的時間執行，則 SQL Server 資料庫引擎會假設此預存程序正在等待使用者輸入。 當使用者正在等待 U2 的結果集時，使用者要求 U2 正在等待工作階段 Mutex，而 U1 正在等待使用者資源。 這就是死結狀態，邏輯上可用下圖說明：

    

鎖死偵測

可能鎖死的資源部分中列出的所有資源都參與 SQL Server 資料庫引擎鎖死偵測配置。 鎖死偵測由鎖定監視器執行緒所執行，它會定期在 SQL Server 資料庫引擎執行個體的所有工作中啟動搜尋。 下列幾點描述搜尋程序：

• 預設間隔是 5 秒。

• 如果鎖定監視器執行緒發現死結，死結偵測間隔會從 5 秒降低，最低降至 100 毫秒，視死結頻率而定。

• 如果鎖定監視器執行緒停止尋找鎖死，SQL Server 資料庫引擎會將搜尋間隔增加為 5 秒。

• 如果剛偵測到鎖死，則會假設後續還有必須等待鎖定的執行緒進入鎖死循環。 在偵測到鎖死之後，前面幾個鎖定等待會立即觸發鎖死搜尋，而不需等到下個鎖死偵測間隔。 例如，如果目前間隔是 5 秒，並且剛偵測到鎖死，則下個鎖定等待會立即啟動鎖死偵測設定。 如果這個鎖定等待是鎖死的一部分，則會立即偵測到它，而不需等到下個鎖死搜尋期間。

SQL Server 資料庫引擎通常只會執行定期鎖死偵測。 由於系統會遇到的死結數量通常很少，週期的死結偵測即可協助將系統在死結偵測上的額外負荷降低。

當鎖定監視執行緒為特定的執行緒啟動死結搜尋時，便對執行緒正在等候的資源進行識別。 而後再由鎖定監視找出該特定資源的擁有者執行緒，並繼續為這些執行緒進行遞迴的死結搜尋直到找出循環為止。 以此方式識別到的循環即構成死結。

在偵測到鎖死之後，SQL Server 資料庫引擎會選擇其中一個執行緒作為鎖死犧牲者，結束鎖死。 SQL Server 資料庫引擎會結束目前為此執行緒所執行的批次、復原鎖死犧牲者的交易，並且將 1205 錯誤傳回應用程式。 回復死結犧牲者的交易，將會釋放交易所持有的所有鎖定。 這可讓其他執行緒的交易變成解除封鎖的狀態並繼續進行。 1205 死結犧牲者錯誤會將與死結相關的執行緒和資源資訊記錄在錯誤記錄檔中。

SQL Server 資料庫引擎預設會選擇執行復原成本最低之交易的工作階段，作為鎖死犧牲者。 或者，使用者可使用 SET DEADLOCK_PRIORITY 陳述式來指定在發生死結時工作階段的優先順序。 DEADLOCK_PRIORITY 可以設為 LOW、NORMAL 或 HIGH，或設為 -10 到 10 範圍內的任何整數值。 鎖死優先權預設為 NORMAL。 如果兩個工作階段有不同的死結優先權，優先權較低的工作階段會被選為死結犧牲者。 如果兩個工作階段有相同的死結優先權，則會選擇回復成本最低之交易的工作階段。 如果死結循環中相關的工作階段具有相同的死結優先權和相同成本，則會隨機選擇犧牲者。

使用通用語言執行平台 (CLR) 時，鎖死監視器會為 Managed 程序內所存取的同步處理資源 (監視器、讀取器/寫入器鎖定和執行緒聯結) 自動偵測是否有鎖死。 不過，死結是透過在選為死結犧牲者的程序中擲回例外狀況來解決。 例外狀況並不會自動釋放犧牲者目前所擁有的資源；您必須明確釋放資源，了解這點很重要。 與例外狀況行為一致，用來識別死結犧牲者的例外狀況可以在發生後解除。

鎖死資訊工具

若要檢視鎖死資訊，SQL Server 資料庫引擎以 system_health XEvent 工作階段、兩個追蹤旗標及 SQL Profiler 中鎖死圖形事件的形式，提供監視工具。

Deadlock 擴充事件

從 SQL Server 2012 (11.x) 開始，應使用 xml_deadlock_report 擴充事件 (XEvent)，而不是 SQL 追蹤或 SQL Profiler 中的鎖死圖表事件類別。

當發生鎖死時，system_health 工作階段已經會擷取所有包含鎖死圖表的 xml_deadlock_report XEvent。 由於預設會啟用 system_health 工作階段，因此無須設定另外的 XEvent 工作階段來擷取鎖死資訊。 不需要採取其他動作使用 xml_deadlock_report XEvent 擷取鎖死資訊。

所擷取的 Deadlock Graph 通常有三個不同的節點：

• victim-list. 死結犧牲者處理序識別碼。
• process-list. 涉及死結之所有處理序的相關資訊。
• resource-list. 涉及死結之資源的相關資訊。

開啟 system_health 工作階段檔案或通道緩衝區，如果已記錄 xml_deadlock_report XEvent，Management Studio 就會顯示涉及鎖死之工作和資源的圖形化描述，如以下範例所示：

下列查詢可檢視 system_health 工作階段通道緩衝區所擷取的所有鎖死事件：

SELECT xdr.value('@timestamp', 'datetime') AS [Date],
       xdr.query('.') AS [Event_Data]
FROM (SELECT CAST ([target_data] AS XML) AS Target_Data
      FROM sys.dm_xe_session_targets AS xt
           INNER JOIN sys.dm_xe_sessions AS xs
               ON xs.address = xt.event_session_address
      WHERE xs.name = N'system_health'
            AND xt.target_name = N'ring_buffer') AS XML_Data
      CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY [Date] DESC;

以下為結果集。

下列範例顯示選取結果第一行 Event_Data 中連結後的輸出：

<event name="xml_deadlock_report" package="sqlserver" timestamp="2022-02-18T08:26:24.698Z">
  <data name="xml_report">
    <type name="xml" package="package0" />
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="process27b9b0b9848" />
        </victim-list>
        <process-list>
          <process id="process27b9b0b9848" taskpriority="0" logused="0" waitresource="KEY: 5:72057594214350848 (1a39e6095155)" waittime="1631" ownerId="11088595" transactionname="SELECT" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27b9f79fac0" lockMode="S" schedulerid="9" kpid="15336" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2022-02-18T00:26:22.893" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="7908" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088595" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p1" line="3" stmtstart="78" stmtend="180" sqlhandle="0x0300050020766505ca3e07008ba8000001000000000000000000000000000000000000000000000000000000">
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x020000006263ec01ebb919c335024a072a2699958d3fcce60000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
    EXEC p1 4
END
   </inputbuf>
          </process>
          <process id="process27b9ee33c28" taskpriority="0" logused="252" waitresource="KEY: 5:72057594214416384 (e5b3d7e750dd)" waittime="1631" ownerId="11088593" transactionname="UPDATE" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27ba15a4490" lockMode="X" schedulerid="6" kpid="5584" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-02-18T00:26:22.890" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="15316" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088593" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p2" line="3" stmtstart="76" stmtend="150" sqlhandle="0x03000500599a5906ce3e07008ba8000001000000000000000000000000000000000000000000000000000000">
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x02000000008fe521e5fb1099410048c5743ff7da04b2047b0000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
    EXEC p2 4
END
   </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="72057594214350848" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="cidx" id="lock27b9dd26a00" mode="X" associatedObjectId="72057594214350848">
            <owner-list>
              <owner id="process27b9ee33c28" mode="X" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9b0b9848" mode="S" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057594214416384" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="idx1" id="lock27afa392600" mode="S" associatedObjectId="72057594214416384">
            <owner-list>
              <owner id="process27b9b0b9848" mode="S" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9ee33c28" mode="X" requestType="wait" />
            </waiter-list>
          </keylock>
        </resource-list>
      </deadlock>
    </value>
  </data>
</event>

如需詳細資訊，請參閱使用 system_health 工作階段。

追蹤旗標 1204 和追蹤旗標 1222

發生鎖死時，追蹤旗標 1204 和追蹤旗標 1222 會傳回在 SQL Server 錯誤記錄檔中擷取到的資訊。 追蹤旗標 1204 報告鎖死所涉及的每一個節點格式化的鎖死資訊。 追蹤旗標 1222 先按處理序再按資源來格式化鎖死資訊。 可同時啟用兩種追蹤旗標來取得相同鎖死事件的兩種表示法。

除了定義追蹤旗標 1204 和 1222 的屬性之外，下表還顯示其相似性和差異。

追蹤旗標 1204 範例

下列範例顯示追蹤旗標 1204 開啟時的輸出。 在此案例中，節點 1 中的資料表是一個不含索引的堆積，節點 2 中的資料表是一個含非叢集索引的堆積。 當發生死結時，正在更新節點 2 中的索引鍵。

Deadlock encountered .... Printing deadlock information
Wait-for graph

Node:1

RID: 6:1:20789:0               CleanCnt:3 Mode:X Flags: 0x2
 Grant List 0:
   Owner:0x0315D6A0 Mode: X
     Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event:
BEGIN TRANSACTION
   EXEC usp_p2
 Requested By:
   ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0
     Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)

Node:2

KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0
 Grant List 0:
   Owner:0x0315D140 Mode: X
     Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4
   SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event:
     BEGIN TRANSACTION
       EXEC usp_p1
 Requested By:
   ResType:LockOwner Stype:'OR'Xdes:0x04D9E258
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x04D9E258
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

追蹤旗標 1222 範例

下列範例顯示追蹤旗標 1222 開啟時的輸出。 在此案例中，有一個資料表是不含索引的堆積，另一個資料表是含非叢集索引的堆積。 在第二份資料表中，當發生死結時，正在更新索引鍵。

deadlock-list
 deadlock victim=process689978
  process-list
   process id=process6891f8 taskpriority=0 logused=868
   waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444
   transactionname=user_transaction
   lasttranstarted=2022-02-05T11:22:42.733 XDES=0x3a3dad0
   lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54
   sbid=0 ecid=0 priority=0 transcount=2
   lastbatchstarted=2022-02-05T11:22:42.733
   lastbatchcompleted=2022-02-05T11:22:42.733
   clientapp=Microsoft SQL Server Management Studio - Query
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user
   isolationlevel=read committed (2) xactid=310444 currentdb=6
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2022.dbo.usp_p1 line=6 stmtstart=202
     sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000
     UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;
     frame procname=adhoc line=3 stmtstart=44
     sqlhandle=0x01000600856aa70f503b8104000000000000000000000000
     EXEC usp_p1
    inputbuf
      BEGIN TRANSACTION
       EXEC usp_p1
   process id=process689978 taskpriority=0 logused=380
   waitresource=KEY: 6:72057594057457664 (350007a4d329)
   waittime=5015 ownerId=310462 transactionname=user_transaction
   lasttranstarted=2022-02-05T11:22:44.077 XDES=0x4d9e258 lockMode=U
   schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0
   priority=0 transcount=2 lastbatchstarted=2022-02-05T11:22:44.077
   lastbatchcompleted=2022-02-05T11:22:44.077
   clientapp=Microsoft SQL Server Management Studio - Query
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user
   isolationlevel=read committed (2) xactid=310462 currentdb=6
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2022.dbo.usp_p2 line=6 stmtstart=200
     sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000
     UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;
     frame procname=adhoc line=3 stmtstart=44
     sqlhandle=0x01000600d688e709b85f8904000000000000000000000000
     EXEC usp_p2
    inputbuf
      BEGIN TRANSACTION
        EXEC usp_p2
  resource-list
   ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2022.dbo.T2
   id=lock3136940 mode=X associatedObjectId=72057594057392128
    owner-list
     owner id=process689978 mode=X
    waiter-list
     waiter id=process6891f8 mode=U requestType=wait
   keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2022.dbo.T1
   indexname=nci_T1_COL1 id=lock3136fc0 mode=X
   associatedObjectId=72057594057457664
    owner-list
     owner id=process6891f8 mode=X
    waiter-list
     waiter id=process689978 mode=U requestType=wait

Profiler 鎖死圖形事件

這是 SQL Profiler 中的一個事件，SQL Profiler 會顯示涉及死結之工作和資源的圖形化描述。 下列範例顯示開啟 Deadlock Graph 事件時 SQL Profiler 的輸出。

如需有關死結事件的詳細資訊，請參閱 Lock:Deadlock 事件類別。 如需有關執行 SQL Profiler 鎖死圖形的詳細資訊，請參閱儲存鎖死圖形 (SQL Server Profiler)。

擴充事件中有 SQL 追蹤事件類別的同等項目，請參閱 查看 SQL 追蹤事件類別的擴充事件同等項目。 建議透過 SQL 追蹤使用擴充事件。

處理鎖死

當 SQL Server 資料庫引擎的執行個體選擇交易作為鎖死犧牲者時，會終止目前的批次、復原交易，然後將錯誤訊息 1205 傳回應用程式。

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

由於任何提交 Transact-SQL 查詢的應用程式都可能被選為鎖死犧牲者，應用程式應該要有錯誤處理常式來截獲錯誤訊息 1205。 如果應用程式不捕捉此錯誤，就不知道其異動已經復原而繼續執行，此時就會發生錯誤。

實作捕捉錯誤訊息 1205 的錯誤處理常式，讓應用程式得以處理死結的狀況並採取補救措施 (例如，將陷入死結的查詢自動重新送出)。 透過自動重新提交查詢，使用者就不必知道鎖死確切的發生。

應用程式應該在重新送出查詢之前稍做停頓。 這可讓死結中的另一筆交易有機會完成動作，並釋放它讓死結循環形成的鎖定。 這樣一來，當重新送出的查詢要求鎖定時，就比較不會再度發生死結的情形。

使用 TRY 處理...抓住

您可以使用 TRY...CATCH 來處理鎖死。 區塊可以攔截 CATCH 1205 鎖死犧牲者錯誤，而且異動可以回復，直到線程解除鎖定為止。

如需詳細資訊，請參閱錯誤處理。

將鎖死降至最低

即使鎖死無法完全避免，但遵循特定程式碼撰寫的慣例可以將產生鎖死的機會降至最低。 將死結降至最低可以提高交易的產能並降低系統額外負荷，因為較少的交易需要：

• 回復，將交易所進行的工作全部恢復。
• 由應用程式重新送出，因為發生死結時已將交易回復。

若要協助將死結降至最低：

• 以相同的順序來存取物件。
• 在交易中避免使用者互動。
• 將交易維持在單一批次中且愈短愈好。
• 使用較低的隔離等級。
• 使用資料列版本設定基礎的隔離等級。
  • 將 READ_COMMITTED_SNAPSHOT 資料庫選項設為 on，以允許讀取認可交易使用資料列版本設定。
  • 使用快照隔離。
• 使用繫結連接。

以相同的順序存取物件

如果所有同時發生的交易都以相同的順序來存取物件，就比較不會發生死結。 例如，如果同時發生的兩筆交易都取得 Supplier 資料表的鎖定，再取得 Part 資料表的鎖定，其中一筆交易便被封鎖於 Supplier 資料表直到另一筆交易完成為止。 第一筆異動認可或回復之後，第二筆才會繼續，這樣就不會發生鎖死。 使用預存程序來進行所有的資料修改動作可將物件的存取順序標準化。

在交易中避免使用者互動

避免撰寫包含使用者互動的交易，因為沒有使用者介入的批次執行速度比使用者必須對查詢做出手動回應的批次更快，例如對應用程式所要求的參數提示做出回覆。 例如，交易如果正在等候使用者輸入，而使用者去用餐，或甚至回家渡假，交易就會被使用者延遲而無法完成。 如此便會降低系統產能，因為交易所持有的任何鎖定只有在交易被認可或回復之後才會釋放。 即使並未發生鎖死的狀況，存取相同資源的其他異動仍會在等候異動完成時遭到封鎖。

將交易維持在單一批次中且愈短愈好

死結通常會在許多長時間執行的交易同時執行於相同的資料庫時發生。 交易的時間愈久，持有的獨占或更新鎖定就愈久，因而封鎖了其他的活動並導致發生死結狀況的可能性。

將交易維持在單一批次中可降低交易期間的網路來回次數，因而降低了完成交易以及釋放鎖定的延遲可能性。

如需更新鎖定的詳細資訊，請參閱交易鎖定和資料列版本設定指南 (機器翻譯)。

使用較低的隔離等級

判斷是否可以較低的隔離等級執行交易。 實作讀取認可讓交易可以對另一筆交易先前讀取 (未修改) 的資料進行讀取，而不必等待前一筆交易完成。 較低的隔離等級 (如讀取認可)，則持有共用鎖定的期間比使用較高的隔離等級(如序列化) 更短。 如此就可減少鎖定競爭。

使用以資料列版本設定為基礎的隔離等級

當 READ_COMMITTED_SNAPSHOT 資料庫選項設定為 ON 時，在讀取認可隔離等級下執行的交易在讀取作業期間，會使用資料列版本設定，而不是使用共用鎖定。

快照隔離也使用資料列版本設定，不使用讀取作業期間的共用鎖定。 ALLOW_SNAPSHOT_ISOLATION 資料庫選項必須設定為 ON，交易才能在快照集隔離下執行。

實作這些隔離等級可將讀取和寫入作業之間發生的死結降到最低。

使用繫結連接

使用繫結連接，則同一個應用程式所開啟的二或多個連接可以互相合作。 如果先前由主要連接獲得鎖定，則會讓次要連接持有獲得的鎖定，反之亦然。 所以它們不會互相鎖定。

停止交易

在鎖死情節中，系統會自動停止並復原犧牲者交易。 在鎖死案例中不需要停止異動。

導致鎖死

若要造成鎖死，您必須將兩個工作階段連線至 AdventureWorksLT2019 資料庫。 我們將這些工作階段稱為工作階段 A 和工作階段 B。只要在 SQL Server Management Studio (SSMS) 中建立兩個查詢視窗，您就可以建立這兩個工作階段。

在工作階段 A 中，執行下列 Transact-SQL。 此程式碼會開始進行明確交易，並執行會更新 SalesLT.Product 資料表的單一陳述式。 為此，交易會在資料表 SalesLT.Product 的一個資料列上取得更新 (U) 鎖定 (會轉換成獨佔 (X) 鎖定)。 我們讓交易保持開啟。

BEGIN TRANSACTION;

UPDATE SalesLT.Product
    SET SellEndDate = SellEndDate + 1
WHERE Color = 'Red';

接著，在工作階段 B 中，執行下列 Transact-SQL。 此程式碼不會明確開始交易。 相對地，會以自動認可交易模式運作。 此陳述式會更新 SalesLT.ProductDescription 資料表。 此更新會取得資料表 SalesLT.ProductDescription 上 72 個資料列的更新 (U) 鎖定。 查詢會聯結至其他資料表，包括 SalesLT.Product 資料表。

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Silver';

若要完成此更新，工作階段 B 需要資料表 SalesLT.Product 上資料列的共用 (S) 鎖定，包括工作階段 A 所鎖定的資料列。工作階段 B 會在 SalesLT.Product 上遭到封鎖。

返回工作階段 A。執行下列 Transact-SQL 陳述式。 這會在開啟的異動中執行第二個 UPDATE 陳述式。

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Red';

工作階段 A 中的第二個 update 陳述式將會被 SalesLT.ProductDescription 上的工作階段 B 封鎖。

工作階段 A 和工作階段 B 現在會互相封鎖。 這兩個交易各需要另一個交易鎖定的資源，因此都無法繼續。

幾秒鐘之後，鎖死監視器會識別出工作階段 A 和工作階段 B 中的異動互相封鎖，且兩者都無法進行。 您應該會看到死結發生，且工作階段 A 被選擇作為死結犧牲者。 工作階段 B 成功完成。 錯誤訊息會出現在工作階段 A 中，其文字類似於，例如：

Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

如果未引發鎖死，請確認 READ_COMMITTED_SNAPSHOT 已在範例資料庫中啟用。 鎖死可能發生在任何資料庫設定下，但此範例要求啟用 READ_COMMITTED_SNAPSHOT。

您就可以在 system_health 擴充事件會工作階段的 ring_buffer 目標中檢視鎖死的詳細資料，這預設會在 SQL Server 中啟用且為使用中。 請考慮下列查詢：

WITH cteDeadLocks ([Deadlock_XML])
AS (SELECT CAST (target_data AS XML) AS [Deadlock_XML]
    FROM sys.dm_xe_sessions AS xs
         INNER JOIN sys.dm_xe_session_targets AS xst
             ON xs.[address] = xst.event_session_address
    WHERE xs.[name] = 'system_health'
          AND xst.target_name = 'ring_buffer')
SELECT x.Graph.query('(event/data/value/deadlock)[1]') AS Deadlock_XML,
       x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') AS when_occurred,
       DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) AS DB --Current database of the first listed process
FROM (SELECT Graph.query('.') AS Graph
      FROM cteDeadLocks AS c
      CROSS APPLY c.[Deadlock_XML].nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS Deadlock_Report(Graph)) AS x
ORDER BY when_occurred DESC;

您可以選取顯示為超連結的儲存格，檢視 SSMS 內 Deadlock_XML 資料行中的 XML。 將此輸出儲存為 .xdl 檔案並關閉，然後在 SSMS 中重新開啟 .xdl 檔案，以取得視覺鎖死圖形。 鎖死圖形看起來應該如下圖所示。

最佳化鎖定和鎖死

適用於：Azure SQL Database

最佳化鎖定導入了不同的鎖定機制方法，可變更涉及獨佔 TID 鎖定的鎖死報告方式。 在鎖死報表 <resource-list> 中的每個資源下，每個 <xactlock> 元素報告鎖死中每個成員鎖定的基礎資源和特定資訊。

請考慮以下啟用最佳化鎖定的範例：

CREATE TABLE t2
(
    a INT PRIMARY KEY NOT NULL,
    b INT NULL
);

INSERT INTO t2
VALUES (1, 10),
(2, 20),
(3, 30);
GO

兩個工作階段中的下列 Transact-SQL 命令會在資料表 t2 上產生鎖死：

在工作階段 1 中：

--session 1
BEGIN TRANSACTION foo;

UPDATE t2
    SET b = b + 10
WHERE a = 1;

在工作階段 2 中：

--session 2:
BEGIN TRANSACTION bar;

UPDATE t2
    SET b = b + 10
WHERE a = 2;

在工作階段 1 中：

--session 1:
UPDATE t2
    SET b = b + 100
WHERE a = 2;

在工作階段 2 中：

--session 2:
UPDATE t2
    SET b = b + 20
WHERE a = 1;

此競爭 UPDATE 陳述式的案例會導致鎖死。 在此情況下，鍵鎖定資源導致鎖死，其中每個工作階段持有對其自己 TID 的 X 鎖定，同時等候對另一個 TID 的 S 鎖定。 以下 XML 是作為鎖死報表而擷取，包含最佳化鎖定的特定元素和屬性：

相關內容

• 擴充事件概觀
• sys.dm_tran_locks (Transact-SQL) (機器翻譯)
• Deadlock Graph 事件類別
• 讀取可重複的隔離等級發生鎖死
• Lock:Deadlock Chain 事件類別
• Lock:Deadlock 事件類別
• SET DEADLOCK_PRIORITY (Transact-SQL)
• 分析並防止 Azure SQL Database 中的鎖死
• 在 SQL Server Management Studio (SSMS) 開啟、檢視及列印死結檔案