本篇和各位分享 Deadlock 實際案例以及解決辦法 ,
以下涉及到的資料內容已全數變造過 , 示意圖供觀眾更好理解情境。

先和各位說明下何謂 Deadlock , 不熟悉的朋友可能會把它和 Blocking 混為一談 ,
實際上是完全不一樣的東西。

Deadlock : 俗稱「死結」, 當兩個或多個交易互相等待彼此持有的鎖，造成一個永遠無法完成的循環等待，就發生了 Deadlock。
Blocking : 俗稱「阻塞」, 當一個交易（Transaction）持有鎖，而另一個交易嘗試存取相同的資源（例如表或資料列）時，必須等待前一個交易釋放鎖，這就是 Blocking。

語畢, 接下來我們進入重點 ↓↓↓

上圖是我用擴充事件抓了半個月Deadlock的筆數紀錄 ,
欄位解釋 :
procname of victimProcess : 發生死結後的「犧牲者」(s掉的sp)。
procname of perpetratorProcess : 發生死結後有順利執行的sp , 此處就稱它為「加害者」。
objectname of keylock : 發生死結的資料表。
indexname of keylock_1 : 犧牲者對該索引發出鎖的請求 , 準備更新該索引。
indexname of keylock_2 : 加害者對該索引發出鎖的請求 , 準備更新該索引。

這次事件非常單純 , 2支sp請求更新時等待彼此持有的鎖 , 由於MSSQL內部的 lock monitor thread(鎖監視執行緒)
預設每5秒會偵測一次 , 哪裡有死結就會自動犧牲成本較低的一方並回滾交易 , 目的是讓系統能繼續維持運作。

這邊給各位看下2支sp的部分內容 , 從擴充事件捕捉到出現問題的code。

1. victim_SP :

UPDATE DEADLOCK_TABLE WITH (ROWLOCK)
    SET
    Column_B = @Column_B,
    Column_H = @Column_H
WHERE Uni_Id = @Uni_Id

2. perpetrator_SP

UPDATE DEADLOCK_TABLE WITH (ROWLOCK)
    SET 
    Column_A = @Column_A,
    Column_B = @Column_B,  
    Column_C = @Column_C,
    Column_D = @Column_D,
    Column_E = @Column_E,
    Column_F = @Column_F,
    Column_G = @Column_G
WHERE WorkDay = @WorkDay AND Uni_Id = @Uni_Id

另外 DEADLOCK_TABLE 該表的叢集索引 PK_DEADLOCK_TABLE 的 Key 值順序為 :
WorkDay -> Uni_Id -> .....

非叢集索引
IDX_DEADLOCK_TABLE 為

CREATE NONCLUSTERED INDEX [IDX_DEADLOCK_TABLE] ON [dbo].[DEADLOCK_TABLE]
(
	[Uni_Id] ASC
)
INCLUDE([Column_B], [Column_H])
WITH .....

雖然程式有做retry的機制 , 但看了資料庫端的Log數據其實覺得煩躁 , 我要讓筆數歸零不發生死結 ,
解法其實不難 , 能盡量減少死結的方法有以下幾種 :

1. 按照相同的順序存取物件。
2. 避免交易中的使用者互動。
3. 保持交易簡短並集中於一批。
4. 使用較低的隔離等級。
   ......
   https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-deadlocks-guide?view=sql-server-ver17
   取自官網的文獻

理論大家都知道 , 但實際遇到了問題最難的通常都是第一步「問題是什麼?」, 找到核心問題非常重要。

在測試環境各自帶入參數執行一遍sp並打開實際執行計畫(此處不方便提供畫面) , 可以知道說victim_SP這支sp使用的是 IDX_DEADLOCK_TABLE 這個索引找到資料並更改 , 也因為這個索引內有包含Column_B、Column_H這兩個欄位 , 所以該索引除了用來找資料外 , 索引內部的資料也同時要做Update , 更新完後再回頭至 PK_DEADLOCK_TABLE 叢集索引Update資料表的實際資料。

而perpetrator_SP這支sp的執行計畫是走 PK_DEADLOCK_TABLE , 那也因為 IDX_DEADLOCK_TABLE 索引內有含 Column_B 欄位 , 所以Update完 PK_DEADLOCK_TABLE 後也需要到 IDX_DEADLOCK_TABLE 更新資料。

這邊小結下 , 因為下where條件的關係 , 所以更新順序為:
victim_SP更新順序為 : IDX_DEADLOCK_TABLE -> PK_DEADLOCK_TABLE
perpetrator_SP 更新順序為 : PK_DEADLOCK_TABLE -> IDX_DEADLOCK_TABLE

更新資料的基本邏輯是:
對目標取鎖 -> 更新資料 -> 釋放鎖
在更新當下需遵循ACID原則 , 在極短的時間差內同時執行這2支sp因為資源競爭、等待對方釋放鎖的關係所以造成死結。

其實要改善這死結的情況方法非常多 , 這次選擇不動程式從索引下手 , 因為 Column_B 為關鍵欄位 , 2支sp都需要到那2個索引Update , 我查出 DEADLOCK_TABLE 這張表的相依sp , IDX_DEADLOCK_TABLE索引其實是可以把INCLUDE的欄位給移除的 , 後續我把IDX_DEADLOCK_TABLE調整成以下

CREATE NONCLUSTERED INDEX [IDX_DEADLOCK_TABLE] ON [dbo].[DEADLOCK_TABLE]
(
	[Uni_Id] ASC
)
WITH .....

之後就沒有再發生相同的Deadlock事件 , 同時也觀察有使用到 IDX_DEADLOCK_TABLE 這個索引做查詢的sp沒有因為移除欄位而發生效能問題 , 這次的結果讓人滿意。

結論 :
歸納下這次引發的死結原因就是以「相反順序更新相同資料」, 龐大的系統下一定會有些程式邏輯出現矛盾現象 , 發生問題我們就大膽推論小心求證 , 不需要責怪當初的開發人員 , 引發死結的原因非常多種可能 , 掌握官方給的守則其實是可以解決大部分的問題 , 如有錯誤請直接指正 , 謝謝。