鎖死指南

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse AnalyticsAnalytics 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 資料庫引擎執行個體中，當工作階段取得非資料庫資源 (例如記憶體或執行緒) 時，可能會鎖死。

Diagram showing a transaction deadlock.

在上圖中，在 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 的箭頭所表示)。
  • 因為在有資源可用之前，沒有一項工作可以繼續，而在有工作繼續之前，沒有一項資源可以釋放，所以會有死結狀態。

Diagram showing the tasks in a deadlock state.

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 下是用來控制多個使用中要求的交錯情形。 如需詳細資訊，請參閱使用 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 正在等待使用者資源。 這就是死結狀態，邏輯上可用下圖說明：

    Diagram of the logical flow of a stored procedure in MARS.

鎖死偵測

上節列出的所有資源都參與 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 擴充事件是擷取鎖死資訊的建議方法。

Deadlock 擴充事件

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

此外，從 SQL Server 2012 (11.x) 開始，當發生鎖死時，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 就會顯示涉及鎖死之工作和資源的圖形化描述，如以下範例所示：

A screenshot from SSMS of a XEvent Deadlock Graph visual diagram.

下列查詢可檢視 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;

以下為結果集。

A screenshot from SSMS of the system_health xEvent query result.

下列範例顯示選取上方結果第一個連結後的輸出：

<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。 使用此等追蹤旗標可能會導致效能問題。 請改用 Deadlock 擴充事件來擷取必要資訊。

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

屬性 追蹤旗標 1204 和追蹤旗標 1222 僅追蹤旗標 1204 僅追蹤旗標 1222
輸出格式 輸出是在 SQL Server 錯誤記錄檔中擷取。 聚焦於死結所涉及的節點。 每一個節點有一個專用區段，最後區段描述死結犧牲者。 以類似 XML 格式傳回不符合 XML 結構描述定義 (XSD) 結構描述的資訊。 此格式有三大區段。 第一個區段宣告死結犧牲者。 第二個區段描述死結所涉及的每一個處理序。 第三個區段描述與追蹤旗標 1204 中的節點同義的資源。
識別屬性 SPID:<x> ECID:<x>。 識別平行處理序中的系統處理序識別碼執行緒。 SPID:<x> ECID:0 項目代表主執行緒，其中 <x> 會以 SPID 值取代。 SPID:<x> ECID:<y> 項目代表相同 SPID 的子執行緒，其中 <x> 會以 SPID 值取代，而 <y> 會大於 0。

BatchID (追蹤旗標 1222 的 sbid)。 識別程式碼執行從中要求或保留鎖定的批次。 停用 Multiple Active Result Sets (MARS) 時，BatchID 值為 0。 啟用 MARS 時，作用中批次的值為 1 到 n。 如果工作階段中沒有作用中批次，BatchID 為 0。

模式。 指定執行緒所要求、授與或等待之特定資源的鎖定類型。 模式可為 IS (意圖共用)、S (共用)、U (更新)、IX (意圖獨佔)、SIX (共用意圖獨佔) 和 X (獨佔)。

Line # (追蹤旗標 1222 的 line)。 列出發生死結時正在執行之目前陳述式批次中的行號。

Input Buf (追蹤旗標 1222 的 inputbuf)。 列出目前批次中的所有陳述式。		 節點。 代表死結鏈結中的項目號碼。

清單。 鎖定擁有者可以是這些清單的一部分：

授與清單。 列舉資源的目前擁有者。

轉換清單。 列舉嘗試將其鎖定轉換為更高層的目前擁有者。

等待清單。 列舉資源的目前最新鎖定要求。

陳述式類型。 描述執行緒有權限的 DML 陳述式的類型 (SELECT、INSERT、UPDATE 或 DELETE)。

犧牲者資源擁有者。 指定 SQL Server 選擇作為犧牲者來中斷鎖死循環的參與執行緒。 選擇的執行緒和所有現存的子執行緒會終止。

下一分支。 代表相同 SPID 中兩個以上涉及鎖死循環的子執行緒。		 deadlock victim。 代表被選為鎖死犧牲者之工作 (請參閱 sys.dm_os_tasks (Transact-SQL)) 的實體記憶體位址。 在未解決的鎖死案例中，它可能會是 0 (零)。 回復中的工作不可選為死結犧牲者。

executionstack。 代表發生鎖死時正在執行的 Transact-SQL 程式碼。

priority。 代表死結優先權。 在特定案例中，SQL Server 資料庫引擎可能會選擇在較短時間內變更鎖死優先順序，以達到更佳的並行效果。

logused。 工作所使用的記錄檔空間。

owner id。具有要求控制權之交易的識別碼。

status。 工作的狀態。 它是下列其中一值：

>>pending。 等待工作者執行緒。

>>runnable。 可開始執行但等待配量。

>>running。 目前在排程器上執行。

>>suspended。 執行已暫停。

>>done。 工作已完成。

>>spinloop。 等待單一執行緒存取鎖變成可用。

waitresource。 工作所需的資源。

waittime。 等待資源的時間 (以毫秒為單位)。

schedulerid。 與這個工作相關聯的排程器。 請參閱 sys.dm_os_schedulers (Transact-SQL)。

hostname。 工作站的名稱。

isolationlevel。 目前交易隔離等級。

Xactid。 具有要求控制權之交易的識別碼。

currentdb。 資料庫的識別碼。

lastbatchstarted。 用戶端處理序上次啟動批次執行的時間。

lastbatchcompleted。 用戶端處理序上次完成批次執行的時間。

clientoption1 和 clientoption2。 此用戶端連接上的設定選項。 這是位元遮罩，其中包含通常由 SET 陳述式 (例如 SET NOCOUNT 和 SET XACTABORT) 所控制之選項的資訊。

associatedObjectId。 代表 HoBT (堆積或 B 型樹狀結構) 識別碼。
資源屬性 RID。 識別在資料表內保留或要求鎖定的單一資料列。 RID 是以 RID: db_id:file_id:page_no:row_no表示。 例如： RID: 6:1:20789:0 。

OBJECT。 識別保留或要求鎖定的資料表。 OBJECT 是以 OBJECT: db_id:object_id 表示。 例如： TAB: 6:2009058193 。

KEY。 識別在索引內保留或要求鎖定的索引鍵範圍。 KEY 是以 KEY: db_id:hobt_id (index key hash value) 表示。 例如： KEY: 6:72057594057457664 (350007a4d329) 。

PAG。 識別保留或要求鎖定的頁面資源。 PAG 是以 PAG: db_id:file_id:page_no 表示。 例如： PAG: 6:1:20789 。

EXT。 識別範圍結構。 EXT 是以 EXT: db_id:file_id:extent_no 表示。 例如： EXT: 6:1:9 。

DB。 識別資料庫鎖定。 DB 會以下列其中一種方式表示：

DB: db_id

DB: db_id[BULK-OP-DB]，其可識別備份資料庫所使用的資料庫鎖定。

DB: db_id[BULK-OP-LOG]，識別該特定資料庫備份記錄檔所使用的鎖定。

APP。 識別應用程式資源使用的鎖定。 APP 是以 APP: lock_resource 表示。 例如： APP: Formf370f478 。

METADATA。 代表死結所涉及的中繼資料資源。 因為 METADATA 有許多子資源，所以傳回的值視含有死結的子資源而定。 例如 METADATA.USER_TYPE 會傳回 user_type_id = *integer_value*。 如需有關 METADATA 資源和子資源的詳細資訊，請參閱 sys.dm_tran_locks (Transact-SQL)。

HOBT。 代表鎖死所涉及的堆積或 B 型樹狀結構。		 None 與此追蹤旗標互斥。 None 與此追蹤旗標互斥。

追蹤旗標 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 的輸出。

重要

SQL Profiler 會建立追蹤，這在 2016 年已取代為擴充事件。 擴充事件的效能負荷低得多，而且比追蹤更容易設定。 請考慮使用擴充事件鎖死事件，而不是追蹤。

A screenshot from SSMS of the visual deadlock graph from a SQL trace.

如需有關死結事件的詳細資訊，請參閱 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 的錯誤處理常式，讓應用程式得以處理死結的狀況並採取補救措施 (例如，將陷入死結的查詢自動重新送出)。 藉由自動重新送出查詢，使用者就不需要知道死結曾經發生。

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

將鎖死降至最低

雖然死結無法完全避免，但是遵守某些程式碼撰寫的慣例可以將死結產生的機會降到最低。 將死結降至最低可以提高交易的產能並降低系統額外負荷，因為較少的交易需要：

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

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

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

以相同的順序存取物件

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

A diagram of a deadlock.

在交易中避免使用者互動

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

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

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

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

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

使用較低的隔離等級

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

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

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

注意

部份應用程式則依靠讀取認可隔離的鎖定和鎖定行為。 對於這些應用程式，在啟用選項之前，需要進行部份變更。

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

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

使用繫結連接

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

停止交易

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

導致鎖死

注意

此範例可在已啟用 READ_COMMITTED_SNAPSHOT 時，用於具有預設結構描述和資料的 AdventureWorksLT2019 範例資料庫中。 若要下載此範例，請造訪 AdventureWorks 範例資料庫。

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

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

BEGIN TRAN

    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
    JOIN SalesLT.ProductModelProductDescription as pmpd on
        pd.ProductDescriptionID = pmpd.ProductDescriptionID
    JOIN SalesLT.ProductModel as pm on
        pmpd.ProductModelID = pm.ProductModelID
    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
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        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 [Deadlock_XML] = CAST(target_data AS 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 
  Deadlock_XML = x.Graph.query('(event/data/value/deadlock)[1]')  
, when_occurred = x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') 
, DB = DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) --Current database of the first listed process 
FROM (
 SELECT Graph.query('.') AS Graph 
 FROM cteDeadLocks 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 檔案，以取得視覺鎖死圖形。 鎖死圖形看起來應該如下圖所示。

A screenshot of a visual deadlock graph in an .xdl file in SSMS.

最佳化鎖定和鎖死

適用於：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

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

在工作階段 1 中：

--session 1
BEGIN TRAN foo;
UPDATE t2 SET b = b+ 10 WHERE a = 1;

在工作階段 2 中：

--session 2:
BEGIN TRAN 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 是作為鎖死報表而擷取，包含最佳化鎖定的特定元素和屬性：

A screenshot of the XML of a deadlock report showing the UnderlyingResource nodes and keylock nodes specific to optimized locking.

相關內容