分析並防止 Azure SQL Database 中的鎖死
本文說明如何在 Azure SQL Database 中識別死結、使用死結圖形和查詢存放區來識別死結中的查詢，以及規劃和測試變更以防止死結再次發生。
本文著重於識別和分析因鎖定爭用而發生的死結。 在可能發生死結的資源中深入了解其他類型的死結。
死結在 Azure SQL Database 中是如何發生的
Azure SQL Database 中的各個新資料庫預設都會啟用讀取認可快照集 (RCSI) 資料庫設定。 在讀取資料和寫入資料間工作階段的封鎖會在 RCSI 下最小化，因為 RCSI 使用資料列版本控制來增加並行。 不過，封鎖和鎖死仍可能會發生在 Azure SQL Database 的資料庫中，因為：
- 修改資料的查詢可能會彼此封鎖。
- 查詢可能在增加封鎖的隔離等級下執行。 隔離等級可透過用戶端程式庫方法、查詢提示或 Transact-SQL 中的 SET 陳述式來指定。
- RCSI 可能會停用，導致資料庫使用共用 (S) 鎖定來保護在讀取認可隔離等級下執行的 SELECT 陳述式。 這可能會增加封鎖與鎖死。
範例死結
當兩或多個工作因為各自具有某個資源的鎖定，但其他工作嘗試要鎖定該資源，因而永久封鎖彼此時，就會發生死結。 死結也稱為循環相依性：如果是兩個工作的死結，交易 A 相依於交易 B，並且交易 B 也因為相依於交易 A 而封閉了這個循環。
例如：
- 工作階段 A 開始明確的交易，並執行 update 陳述式，而取得資料表
SalesLT.Product上一個資料列的更新 (U) 鎖定 (會轉換成獨佔 (X) 鎖定)。 - 工作階段 B 執行會修改資料表
SalesLT.ProductDescription的 update 陳述式。 update 陳述式會聯結至資料表SalesLT.Product，以尋找要更新的正確資料列。- 工作階段 B 取得資料表
SalesLT.ProductDescription上 72 個資料列的更新 (U) 鎖定。 - 工作階段 B 需要資料表
SalesLT.Product上資料列的共用鎖定，包括工作階段 A 所鎖定的資料列。工作階段 B 會在SalesLT.Product上遭到封鎖。
- 工作階段 B 取得資料表
- 工作階段 A 繼續其交易，且此時對
SalesLT.ProductDescription資料表執行更新。 工作階段 A 在SalesLT.ProductDescription上遭到工作階段 B 封鎖。
死結中的所有交易都會無限期地等候，除非其中一個參與的交易復原，例如，因為其工作階段已終止。
資料庫引擎死結監視器會定期檢查是否有工作處於死結狀態。 死結監視器若偵測到循環相依性，將會選擇其中一個工作作為犧牲者，並終止其交易，然後產生錯誤 1205：「交易 (程序識別碼 N) 已鎖死於另一個程序的鎖定資源上，並且被選擇作為死結犧牲者。 請重新執行交易。」以這種方式中斷死結，可讓死結中的一或多個其他工作完成其交易。
注意
請在本文的死結程序清單一節中，深入了解選擇死結犧牲者的條件。
交易被選擇作為死結犧牲者的應用程式應重試交易，這類重試通常會在死結中涉及的其他交易完成之後完成。
最佳做法是在重試之前導入短暫的隨機延遲，以避免再次遇到相同的死結。 深入了解如何設計暫時性錯誤的重試邏輯。
Azure SQL Database 中的預設隔離等級
Azure SQL Database 中的新資料庫依預設會啟用讀取認可快照集 (RCSI)。 RCSI 會變更讀取認可隔離等級的行為，以使用資料列版本設定來提供陳述式層級的一致性，而不對 SELECT 陳述式使用共用 (S) 鎖定。
RCSI 啟用時：
- 讀取資料的陳述式不會封鎖修改資料的陳述式。
- 修改資料的陳述式不會封鎖讀取資料的陳述式。
依預設，Azure SQL Database 中的新資料庫也會啟用快照集隔離等級。 快照集隔離是額外的資料列型隔離等級，可提供資料的交易層級一致性，並使用資料列版本來選取要更新的資料列。 若要使用快照集隔離，查詢或連線必須明確地將其交易隔離等級設定為 SNAPSHOT。 此動作僅能在資料庫啟用快照集隔離時完成。
您可以使用 Transact-SQL 來識別 RCSI 和/或快照集隔離是否已啟用。 連線至您 Azure SQL Database 中的資料庫，並執行下列查詢：
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO
如果已啟用 RCSI，is_read_committed_snapshot_on 資料行會傳回值 1。 如果已啟用快照集隔離，snapshot_isolation_state_desc 資料行會傳回值 ON。
如果 Azure SQL Database 中的資料庫已停用 RCSI，請先調查 RCSI 停用的原因，再將其重新啟用。 已撰寫的應用程式程式碼可能預期讀取資料的查詢會遭到寫入資料的查詢封鎖，而導致在啟用 RCSI 的情況下，競爭條件的結果不正確。
解譯死結事件
在 Azure SQL Database 中的死結管理員偵測到死結，並選取交易作為犧牲者之後，就會發出死結事件。 換句話說，如果您設定了死結的警示，則在個別的死結解決之後，就會引發通知。 使用者無須對該死結採取任何動作。 撰寫應用程式時應納入重試邏輯，以便在收到錯誤 1205 之後自動繼續執行：「交易 (程序識別碼 N) 已鎖死於另一個程序的鎖定資源上，並且被選擇作為死結犧牲者。 請重新執行該交易。」
但設定警示有其效用，因為死結可能會再次發生。 死結警示可讓您調查資料庫中是否發生重複的死結模式；如果有，您可以選擇採取動作以防止死結再次發生。 請在本文的死結的監視和警示一節中深入了解警示。
防止死結的最佳方法
要防止死結再次發生，風險最低的方法通常是微調非叢集索引，以最佳化死結所涉及的查詢。
- 此方法的風險很低，因為微調非叢集索引不需要變更查詢程式碼本身，因而在重寫 Transact-SQL 時，能夠降低因使用者錯誤而將不正確的資料傳回給使用者的風險。
- 有效的非叢集索引微調，可協助查詢更有效率地尋找要讀取和修改的資料。 減少查詢所需存取的資料量，可降低封鎖的可能性，且通常會防止死結。
在某些情況下，建立或調整叢集索引可以減少封鎖和死結。 由於叢集索引包含在所有非叢集索引定義中，因此，對於具有現有非叢集索引的較大資料表，建立或修改叢集索引可能會是 IO 密集型且耗時的作業。 深入了解叢集索引設計指導方針。
索引微調無法成功防止死結時，有其他方法可供使用：
- 如果只有在為其中一個涉及死結的查詢選擇特定計劃時才會發生死結，則透過查詢存放區強制執行查詢計劃或許可防止死結再次發生。
- 針對涉及死結的一或多個交易重寫 Transact-SQL，可能也有助於防止死結。 將明確交易分成較小的交易需要謹慎的編碼和測試，以確保執行並行修改時的資料有效性。
請在本文的防止死結再次發生一節中深入了解這些方法。
死結的監視和警示
在本文中，我們將使用 AdventureWorksLT 範例資料庫來設定死結的警示、造成範例死結、分析範例死結的死結圖形，以及測試變更以防止死結再次發生。
在本文中我們將使用 SQL Server Management Studio (SSMS) 用戶端，因為其中包含以互動式視覺模式顯示死結圖形的功能。 您可以使用其他用戶端 (例如 Azure Data Studio) 來演示範例，但這樣可能只能以 XML 的形式檢視死結圖形。
建立 AdventureWorksLT 資料庫
若要按照範例操作，請在 Azure SQL Database 中建立新的資料庫，然後選取 [範例] 資料作為 [資料來源]。
如需如何使用 Azure 入口網站、Azure CLI 或 PowerShell 建立 AdventureWorksLT 的詳細指示，請在快速入門：建立 Azure SQL Database 單一資料庫中選取您選擇的方法。
在 Azure 入口網站中設定死結警示
若要設定死結事件的警示，請遵循使用 Azure 入口網站建立 Azure SQL Database 和 Azure Synapse Analytics 的警示一文中的步驟。
選取 [死結] 作為警示的訊號名稱。 設定 [動作群組]，以使用您選擇的方法獲取通知，例如電子郵件/SMS/推播/語音動作類型。
使用擴充事件收集 Azure SQL Database 中的死結圖形
死結圖形是涉及死結之程序和鎖定的相關資訊的豐富來源。 若要在 Azure SQL Database 中使用擴充事件 (XEvents) 收集死結圖形，請擷取 sqlserver.database_xml_deadlock_report 事件。
您可以使用信號緩衝區目標或事件檔案目標來收集 XEvents 的死結圖形。 下表摘要說明選取適當目標型別的考量：
| 方法 | 優點 | 考量 | 使用方式情節 |
|---|---|---|---|
| 信號緩衝區目標 |
|
|
|
| 事件檔案目標 |
|
|
|
選取您想要使用的目標類型：
信號緩衝區目標既方便又容易設定，但容量有限，而可能會導致較舊的事件遺失。 信號緩衝區不會將事件保存到儲存體，且在 XEvents 工作階段停止時會清除信號緩衝區目標。 這表示當資料庫引擎因故 (例如容錯移轉) 重新啟動時，收集的任何 XEvents 都將無法使用。 如果您無法立即設定事件檔案目標的 XEvents 工作階段，信號緩衝區目標將最適合用於學習和短期需求。
此範例程式碼會建立一個 XEvents 工作階段，使用信號緩衝區目標擷取記憶體中的死結圖形。 信號緩衝區目標允許的記憶體上限為 4 MB，而且當資料庫上線時 (例如在容錯移轉之後)，工作階段將會自動執。
若要為寫入至信號緩衝區目標的 sqlserver.database_xml_deadlock_report 事件建立並啟動 XEvents 工作階段，請連線至您的資料庫，並執行下列 Transact-SQL：
CREATE EVENT SESSION [deadlocks] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=ON, MAX_MEMORY=4 MB)
GO
ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = START;
GO
在 AdventureWorksLT 中造成死結
注意
此範例可在已啟用 RCSI 時，用於具有預設結構描述和資料的 AdventureWorksLT 資料庫中。 如需建立資料庫的指示，請參閱建立 AdventureWorksLT 資料庫。
若要造成死結，您必須將兩個工作階段連線至 AdventureWorksLT 資料庫。 我們將這兩個工作階段稱為工作階段 A 和工作階段 B。
在工作階段 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 被選擇作為死結犧牲者。 錯誤訊息會出現在工作階段 A 中，其文字類似於：
訊息 1205、層級 13、狀態 51、第 7 行 交易 (程序識別碼 91) 已鎖死於另一個程序的鎖定資源上，並且被選擇作為死結犧牲者。 請重新執行該交易。
工作階段 B 將會順利完成。
如果您在 Azure 入口網站中設定死結警示，您應該會在死結發生之後立即收到通知。
檢視 XEvents 工作階段的死結圖形
如果您已設定 XEvents 工作階段以收集死結，且在工作階段啟動後發生了死結，您可以檢視死結圖形的互動式圖形顯示，以及死結圖形的 XML。
您可以使用不同的方法來取得信號緩衝區目標和事件檔案目標的死結資訊。 選取您用於 XEvents 工作階段的目標：
如果您設定寫入至信號緩衝區的 XEvents 工作階段，您可以使用下列 Transact-SQL 來查詢死結資訊。 在執行查詢之前，請將 @tracename 的值取代為 xEvents 工作階段的名稱。
DECLARE @tracename sysname = N'deadlocks';
WITH ring_buffer AS (
SELECT CAST(target_data AS XML) as rb
FROM sys.dm_xe_database_sessions AS s
JOIN sys.dm_xe_database_session_targets AS t
ON CAST(t.event_session_address AS BINARY(8)) = CAST(s.address AS BINARY(8))
WHERE s.name = @tracename and
t.target_name = N'ring_buffer'
), dx AS (
SELECT
dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
FROM ring_buffer
CROSS APPLY rb.nodes('/RingBufferTarget/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata)
)
SELECT
d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO
以 XML 格式檢視和儲存死結圖形
檢視 XML 格式的死結圖形，可讓您複製與死結相關的 Transact-SQL 陳述式的 inputbuffer。 您也可能偏好使用文字格式來分析死結。
如果您使用 Transact-SQL 查詢傳回了死結圖形資訊，若要檢視死結圖形 XML，請從任何資料列選取 deadlock_xml 資料行中的值，以在 SSMS 的新視窗中開啟死結圖形的 XML。
此範例死結圖形的 XML 為：
<deadlock>
<victim-list>
<victimProcess id="process24756e75088" />
</victim-list>
<process-list>
<process id="process24756e75088" taskpriority="0" logused="6528" waitresource="KEY: 8:72057594045202432 (98ec012aa510)" waittime="192" ownerId="1011123" transactionname="user_transaction" lasttranstarted="2022-03-08T15:44:43.490" XDES="0x2475c980428" lockMode="U" schedulerid="3" kpid="30192" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:49.250" lastbatchcompleted="2022-03-08T15:44:49.210" lastattention="1900-01-01T00:00:00.210" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1011123" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="792" sqlhandle="0x02000000c58b8f1e24e8f104a930776e21254b1771f92a520000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
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' </inputbuf>
</process>
<process id="process2476d07d088" taskpriority="0" logused="11360" waitresource="KEY: 8:72057594045267968 (39e18040972e)" waittime="2641" ownerId="1013536" transactionname="UPDATE" lasttranstarted="2022-03-08T15:44:46.807" XDES="0x2475ca80428" lockMode="S" schedulerid="2" kpid="94040" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:46.807" lastbatchcompleted="2022-03-08T15:44:46.760" lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1013536" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="798" sqlhandle="0x020000002c85bb06327c0852c0be840fc1e30efce2b7c8090000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
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'; </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594045202432" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.ProductDescription" indexname="PK_ProductDescription_ProductDescriptionID" id="lock2474df12080" mode="U" associatedObjectId="72057594045202432">
<owner-list>
<owner id="process2476d07d088" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process24756e75088" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594045267968" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.Product" indexname="PK_Product_ProductID" id="lock2474b588580" mode="X" associatedObjectId="72057594045267968">
<owner-list>
<owner id="process24756e75088" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process2476d07d088" mode="S" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
若要將死結圖形儲存為 XML 檔案：
- 選取 [檔案]，然後選取 [另存新檔...]。
- 將 [另存新檔] 值保留為預設的 [XML 檔案 (*.xml)]
- 將 [檔案名稱] 設定為您選擇的名稱。
- 選取 [儲存]。
將死結圖形儲存為可在 SSMS 中以互動方式顯示的 XDL 檔案
檢視死結圖形的互動式表示法，有助於快速概覽死結中涉及的程序和資源，以及快速識別死結犧牲者。
若要將死結圖形儲存為可由 SSMS 以圖形方式顯示的檔案：
從任何資料列選取
deadlock_xml資料行中的值，以在 SSMS 的新視窗中開啟死結圖形的 XML。選取 [檔案]，然後選取 [另存新檔...]。
將 [存檔類型] 設定為 [所有檔案]。
將 [檔案名稱] 設定為您選擇的名稱，並將副檔名設定為 .xdl。
選取 [儲存]。
選取視窗頂端的索引標籤上的 X，或依序選取 [檔案] 和 [關閉]，以關閉檔案。
依序選取 [檔案]、[開啟]、[檔案]，以在 SSMS 中重新開啟檔案。 選取您以
.xdl副檔名儲存的檔案。死結圖形此時會顯示在 SSMS 中，以視覺表示法呈現死結所涉及的程序和資源。
分析 Azure SQL Database 的死結
死結圖形通常有三個節點：
- Victim-list。 死結犧牲者處理序識別碼。
- Process-list。 涉及死結之所有處理序的相關資訊。 死結圖形使用「程序」一詞來代表執行交易的工作階段。
- Resource-list。 涉及死結之資源的相關資訊。
分析死結時，逐步查看這些節點會很有用。
死結犧牲者清單
死結犧牲者清單會顯示被選為死結犧牲者的程序。 在死結圖形的視覺表示法中，程序會以橢圓形表示。 死結犧牲者程序會在橢圓形上繪製 "X"。
在死結圖形的 XML 檢視中，victim-list 節點會為作為死結犧牲者的程序提供識別碼。
在我們的範例死結中，犧牲者程序識別碼為 process24756e75088。 我們可以在檢查 process-list 和 resource-list 節點時使用此識別碼，以深入了解犧牲者程序及其鎖定或要求鎖定的資源。
死結程序清單
死結程序清單是涉及死結之交易相關資訊的豐富來源。
死結圖形的圖形表示法只會顯示死結圖形 XML 中包含的資訊子集。 死結圖形中的橢圓形代表程序，所顯示的資訊包括：
伺服器程序識別碼，也稱為工作階段識別碼或 SPID。
工作階段的死結優先順序。 如果兩個工作階段有不同的死結優先權，優先權較低的工作階段會被選為死結犧牲者。 在此範例中，這兩個工作階段具有相同的死結優先順序。
工作階段所使用的交易記錄數量 (以位元組為單位)。 如果兩個工作階段的死結優先順序相同，則死結監視器會選擇回復成本較低的工作階段作為死結的犧牲者。 成本是透過比較寫入每筆交易該時間點的記錄位元組數目來決定。
在我們的範例死結中，session_id 89 使用了較少量的交易記錄，而已被選取為死結犧牲者。
此外，您可以將滑鼠暫留在各個程序上，以檢視死結發生前在每個工作階段中執行的最後一個陳述式的輸入緩衝區。 輸入緩衝區會出現在工具提示中。
死結圖形的 XML 檢視中的程序可以使用其他資訊，包括：
- 工作階段的識別資訊，例如用戶端名稱、主機名稱和登入名稱。
- 死結發生前每個工作階段所執行的最後一個陳述式的查詢計劃雜湊。 查詢計劃雜湊可用來從查詢存放區中擷取查詢的詳細資訊。
在我們的範例死結中：
- 我們可以看到，這兩個工作階段都是在 chrisqpublic 登入下使用 SSMS 用戶端來執行的。
- 我們的死結犧牲者在死結發生前執行的最後一個陳述式的查詢計劃雜湊為 0x02b0f58d7730f798。 我們可以在輸入緩衝區中看到此陳述式的文字。
- 死結中其他工作階段所執行的最後一個陳述式的查詢計劃雜湊也是 0x02b0f58d7730f798。 我們可以在輸入緩衝區中看到此陳述式的文字。 在此案例中，這兩個查詢具有相同的查詢計劃雜湊，因為查詢幾乎完全相同，差別在於作為等號比較述詞的常值。
我們將在本文稍後使用這些值來尋找查詢存放區中的其他資訊。
死結程序清單中的輸入緩衝區限制
對於死結程序清單中的輸入緩衝區資訊，有一些需要注意的限制。
查詢文字可能會在輸入緩衝區中截斷。 輸入緩衝區限制為所執行之陳述式的前 4,000 個字元。
此外，涉及死結的某些陳述式可能不會包含在死結圖形中。 在我們的範例中，工作階段 A 在單一交易內執行了兩個 update 陳述式。 只有造成死結的第二個 update 陳述式才包含在死結圖形中。 工作階段 A 所執行的第一個 update 陳述式封鎖了工作階段 B，而在死結中發揮了作用。輸入緩衝區、query_hash 和工作階段 A 所執行之第一個陳述式的相關資訊未包含在死結圖形中。
若要識別在涉及死結的多陳述式交易中執行的完整 Transact-SQL，您必須在執行查詢的預存程序或應用程式程式碼中找出相關資訊，或使用擴充事件執行追蹤，以擷取涉及死結的工作階段在死結發生時所執行的完整陳述式。 如果涉及死結的陳述式已截斷，而只有部分 Transact-SQL 出現在輸入緩衝區中，您可以使用執行計劃在查詢存放區中找出陳述式的 Transact-SQL。
死結資源節點
死結資源清單會顯示死結中的程序所擁有和等候的鎖定資源。
在死結的視覺表示法中，資源以矩形表示：
注意
您可能會注意到，資料庫名稱在 Azure SQL Database 資料庫的死結圖形中會以唯一識別碼表示。 這是 sys.databases 和 sys.dm_user_db_resource_governance 動態管理檢視中所列之資料庫的 physical_database_name。
在此範例死結中：
死結犧牲者，我們稱之為工作階段 A：
- 擁有
SalesLT.Product資料表PK_Product_ProductID索引之索引鍵的獨佔 (X) 鎖定。 - 要求
SalesLT.ProductDescription資料表PK_ProductDescription_ProductDescriptionID索引之索引鍵的更新 (U) 鎖定。
- 擁有
另一個程序，我們稱之為工作階段 B：
- 擁有
SalesLT.ProductDescription資料表PK_ProductDescription_ProductDescriptionID索引之索引鍵的更新 (U) 鎖定。 - 要求
SalesLT.ProductDescription資料表PK_ProductDescription_ProductDescriptionID索引之索引鍵的共用 (S) 鎖定。
- 擁有
我們可以在 resource-list 節點的死結圖形的 XML 中看到相同的資訊。
在查詢存放區中尋找查詢執行計劃
檢查涉及死結之陳述式的查詢執行計劃，通常有其效用。 使用死結圖形程序清單的 XML 檢視中的查詢計劃雜湊，通常就可在查詢存放區中找到這些執行計劃。
此 Transact-SQL 查詢會尋找與我們針對範例死結找到的查詢計劃雜湊相符的查詢計劃。 連線至 Azure SQL Database 中的使用者資料庫，以執行查詢。
DECLARE @query_plan_hash binary(8) = 0x02b0f58d7730f798
SELECT
qrsi.end_time as interval_end_time,
qs.query_id,
qp.plan_id,
qt.query_sql_text,
TRY_CAST(qp.query_plan as XML) as query_plan,
qrs.count_executions
FROM sys.query_store_query as qs
JOIN sys.query_store_query_text as qt on qs.query_text_id=qt.query_text_id
JOIN sys.query_store_plan as qp on qs.query_id=qp.query_id
JOIN sys.query_store_runtime_stats qrs on qp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qrsi on qrs.runtime_stats_interval_id=qrsi.runtime_stats_interval_id
WHERE query_plan_hash = @query_plan_hash
ORDER BY interval_end_time, query_id;
GO
您可能無法從查詢存放區取得查詢執行計劃，視您的查詢存放區 CLEANUP_POLICY 或 QUERY_CAPTURE_MODE settings 而定。 在此情況下，您通常可以藉由顯示查詢的估計執行計劃來取得所需的資訊。
尋找增加封鎖的模式
檢查涉及死結的查詢執行計劃時，請查看可能導致封鎖和死結的模式。
資料表或索引掃描。 在 RCSI 下執行修改資料的查詢時，會使用封鎖掃描來選取要更新的資料列，在封鎖掃描中，當資料值被讀取時，就會在資料列上進行更新 (U) 鎖定。 如果資料列不符合更新條件，就會釋放更新鎖定，並且會鎖定及掃描下一個資料列。
微調索引可協助修改查詢更有效率地尋找資料列，以減少發出的更新鎖定數目。 這可降低封鎖和死結的機率。
參照多個資料表的索引檢視。 當您修改在索引檢視中參考的資料表時，資料庫引擎也必須維護索引檢視。 這需要獲取更多鎖定，而可能會導致封鎖和死結增加。 索引檢視也可能會導致更新作業在讀取認可的隔離等級下內部執行。
修改外部索引鍵條件約束中參考的資料行。 當您修改 FOREIGN KEY 條件約束中參考之資料表中的資料行時，資料庫引擎必須尋找參考資料表中的相關資料列。 資料列版本無法用於這些讀取。 在啟用串聯更新或刪除的情況下，隔離等級可能會升級為可在陳述式執行期間序列化，以防止虛設插入。
鎖定提示。 尋找指定需要更多鎖定之隔離等級的資料表提示。 這些提示包括
HOLDLOCK(相當於可序列化)、SERIALIZABLE、READCOMMITTEDLOCK(停用 RCSI) 和REPEATABLEREAD。 此外，PAGLOCK、TABLOCK、UPDLOCK和XLOCK之類的提示可能會增加封鎖和死結的風險。如果這些提示已就緒，請研究實作這些提示的原因。 這些提示可能會防止競爭情形，並確保資料有效性。 您可以視需要使用本文的防止死結再次發生一節中的替代方法，保留這些提示，並防止未來發生死結。
注意
在交易鎖定和資料列版本設定指南中，深入了解使用資料列版本設定修改資料時的行為。
檢查交易的完整程式碼時，請在執行計劃或應用程式查詢程式碼中尋找其他有問題的模式：
交易中的使用者互動。 明確多陳述式交易內的使用者互動，會大幅拉長交易的持續時間。 這會使這些交易更容易重疊，且更容易發生封鎖和死結。
同樣地，保留開啟的交易，並在交易中途查詢不相關的資料庫或系統，會大幅提高封鎖和死結的機率。
以不同順序存取物件的交易。 當並行的明確多陳述式交易依循相同模式，並以相同順序存取物件時，較不可能發生死結。
防止死結再次發生
有許多技術可用來防止死結再次發生，包括索引微調、透過查詢存放區強制執行計劃，以及修改 Transact-SQL 查詢。
檢閱資料表的叢集索引。 大部分資料表都會受益於叢集索引，但資料表常會意外實作為堆積。
檢查叢集索引的方法之一，是使用 sp_helpindex 系統預存程序。 例如，我們可藉由執行下列陳述式，來檢視
SalesLT.Product資料表上的索引摘要：exec sp_helpindex 'SalesLT.Product'; GO檢閱 index_description 資料行。 資料表只能有一個叢集索引。 如果已實作資料表的叢集索引，則 index_description 將包含 'clustered' 一詞。
如果沒有叢集索引，資料表將是堆積。 在此情況下，請檢查資料表是否刻意建立為堆積，以解決特定效能問題。 請考慮根據叢集索引設計指導方針來實作叢集索引。
在某些情況下，建立或微調叢集索引或許可減少或消除死結中的封鎖。 在其他情況下，您可能需要採用其他技術，例如此清單中的其他技術。
建立或修改非叢集索引。 微調非叢集索引可協助您的修改查詢更快找到要更新的資料，而減少所需的更新鎖定數目。
在我們的範例死結中，位於查詢存放區中的查詢執行計劃包含對
PK_Product_ProductID索引的叢集索引掃描。 死結圖形表示等候此索引的共用 (S) 鎖定為死結中的元件。
正在執行此索引掃描，因為我們的更新查詢需要修改名為
vProductAndDescription的索引檢視。 如本文的尋找增加封鎖的模式一節所述，參考多個資料表的索引檢視可能會提高封鎖和死結的可能性。如果我們在
AdventureWorksLT資料庫中建立下列非叢集索引，以「涵蓋」索引檢視所參考之SalesLT.Product的資料行，將有助於查詢更有效率地尋找資料列：CREATE INDEX ix_Product_ProductID_Name_ProductModelID on SalesLT.Product (ProductID, Name, ProductModelID); GO建立此索引後，死結就不會再次發生。
當死結涉及修改外部索引鍵條件約束中參考的資料行時，請確定 FOREIGN KEY 的參考資料表上的索引支援有效率地尋找相關的資料列。
雖然索引在某些情況下可以大幅改善查詢效能，但索引也具有額外負荷和管理成本。 請參閱一般索引設計指導方針，以利在建立索引之前評估索引的效益，特別是廣泛索引和大型資料表的索引。
評估索引檢視的值。 另一個防止範例死結再次發生的選項是卸除
SalesLT.vProductAndDescription索引檢視。 如果目前未使用該索引檢視，這將可減輕長時間維護索引檢視的額外負荷。使用快照集隔離。 在某些情況下，將交易隔離等級設定為涉及死結之一或多個交易的快照集，或許可防止封鎖和死結再次發生。
在資料庫中停用讀取認可快照集時，對 SELECT 陳述式使用這項技術最有可能成功。 讀取認可快照集停用時，使用讀取認可隔離等級的 SELECT 查詢需要共用 (S) 鎖定。 對這些交易使用快照集隔離可消除共用鎖定的需求，因而能夠防止封鎖和死結。
在已啟用讀取認可快照集隔離的資料庫中，SELECT 查詢不需要共用 (S) 鎖定，因此在修改資料的交易之間更有可能發生死結。 如果多個修改資料的交易之間發生死結，快照集隔離可能會導致更新衝突，而不是死結。 這同樣需要其中一個交易重試其作業。
透過查詢存放區強制執行計劃。 您可能會發現死結中的其中一個查詢有多個執行計劃，而只有在使用特定計劃時才會發生死結。 您可以藉由在查詢存放區中強制執行計劃來防止死結再次發生。
修改 Transact-SQL。 您可能需要修改 Transact-SQL，以防止死結再次發生。 Transact-SQL 的修改應謹慎完成，且應該嚴格測試變更，以確保並行執行修改時可保有資料正確性。 重寫 Transact-SQL 時，請考慮：
- 在交易中排序陳述式，使其以相同的順序存取物件。
- 盡可能將交易分成較小的交易。
- 如有必要，請使用查詢提示將效能最佳化。 您可以使用查詢存放區來套用提示，而不變更應用程式程式碼。
更多方法，請參閱死結指南中的將死結降至最低。
注意
在某些情況下，如果其中一個工作階段必須在未重試的情況下順利完成，或是其中一個涉及死結的查詢不重要而一律應被選為犧牲者，您可以對一或多個涉及死結的工作階段調整死結優先順序。 雖然這不會防止死結再次發生，但或許可降低對未來死結的影響。
卸除 XEvents 工作階段
您可能會希望收集死結資訊的 XEvents 工作階段能夠在重要資料庫上長時間執行。 請注意，如果您使用事件檔案目標，這可能會在發生多個死結時產生大型檔案。 您可以從 Azure 儲存體中刪除作用中追蹤的 Blob 檔案 (目前正在寫入的檔案除外)。
當您想要移除 XEvents 工作階段時，無論選取的目標類型為何，用來卸除工作階段的 Transact-SQL 都相同。
若要移除 XEvents 工作階段，請執行下列 Transact-SQL。 在執行程式碼之前，請將工作階段的名稱取代為適當的值。
ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = STOP;
GO
DROP EVENT SESSION [deadlocks] ON DATABASE;
GO
使用 Azure 儲存體總管
Azure 儲存體總管是一個獨立應用程式，可讓您輕鬆使用 Azure 儲存體中的 Blob 所儲存的事件檔案目標。 您可以使用 Azure 儲存體總管來：
- 建立 Blob 容器，用以保存 XEvent 工作階段資料。
- 取得 Blob 容器的共用存取簽章 (SAS)。
- 如使用擴充事件收集 Azure SQL Database 中的死結圖形中所述，需要讀取、寫入和列出權限。
- 在建立資料庫範圍的認證時，從
Query string中移除任何前置?字元，以將該值作為秘密。
- 從 Blob 容器檢視和下載擴充事件檔案。
下一步
深入了解 Azure SQL Database 中的效能：
意見反應
即將登場：在 2024 年，我們將逐步淘汰 GitHub 問題作為內容的意見反應機制，並將它取代為新的意見反應系統。 如需詳細資訊，請參閱：https://aka.ms/ContentUserFeedback。
提交並檢視相關的意見反應