SQL Server 2005 SP3 多執行緒 INSERT 死鎖診斷實戰

案例背景

• 環境：SQL Server 2005 SP3
• 應用：20 條執行緒並發向同一表 INSERT
• 現象：主鍵不重複，卻頻繁發生死鎖 (Error 1205)
• 對比：相同程式對 Oracle 運行正常

診斷流程

步驟 1：啟用死鎖追蹤

-- 方法一：追蹤旗標
DBCC TRACEON(1204, 1222, -1);

-- 方法二：SQL Server Profiler 事件
-- Locks → Deadlock graph

步驟 2：分析死鎖圖與錯誤日誌

死鎖圖顯示：共享鎖 (S) 與排他鎖 (X) 相互等待

錯誤日誌關鍵資訊：

• waitresource = KEY: 9:72057594058113024 (主鍵索引)
• lockMode = S (共享鎖)
• transactionname = INSERT
• isolationlevel = read committed (2)

步驟 3：根因假設與驗證

初步判斷： 主鍵上發生 S/X 鎖衝突，推測原因：

1. 觸發器執行時間過長
2. 觸發器中讀取同一表
3. 主鍵生成過程中查詢該表

深入詢問： 確認表結構、觸發器腳本、主鍵生成邏輯。

步驟 4：問題解決

「原來是其他人在這個表中建立了觸發器，我不知道，並且這個觸發器還訪問了這個表。」

移除觸發器後，死鎖問題消失。

死鎖核心機制

讀寫/寫讀衝突循環：

Session A: 讀取資源 1 (S) → 請求資源 2 (X)
Session B: 寫入資源 2 (X) → 請求資源 1 (S)
結果：雙方互相等待，形成死鎖

預防最佳實務

1. 縮短交易時間：減少鎖持有期間
2. 避免觸發器中存取基礎表：若必要，使用 READPAST、NOLOCK 謹慎處理
3. 統一存取順序：所有交易依相同順序存取資源
4. 適當隔離等級：評估 READ COMMITTED SNAPSHOT (RCSI)
5. 主鍵設計：避免熱點頁 (如遞增身分識別碼 + 高並發 INSERT)

啟示

「死鎖常見場景是讀寫寫讀衝突，循環引用造成相互資源排斥。建議解決方式：縮短交易處理時間，減少循環引用。」