SQL Server 死鎖原理與實戰演示

什麼是死鎖？

死鎖是指兩個或多個進程在執行過程中，因為互相持有對方需要的鎖資源，導致雙方都無法繼續執行，形成永久阻塞的狀態。SQL Server 能夠自動偵測死鎖，並選擇終止其中一個事務（犧牲者）來解除死鎖。

經典死鎖場景演示

以下步驟將重現一個典型的死鎖案例：

1. 建立測試資料庫 InvDB。
2. 執行腳本建立 person 表並插入兩筆測試資料。
   
3. 在 SSMS 中開啟兩個查詢視窗，分別執行以下更新語句（預設 READ COMMITTED 隔離級別）：
   

此時進程 T1 持有鎖 A 並請求鎖 B，進程 T2 持有鎖 B 並請求鎖 A，形成循環等待，SQL Server 將介入並終止其中一個進程。

執行結果

其中一個進程正常完成更新，另一個進程被選為犧牲者並回滾，返回錯誤 1205：

(1 row(s) affected)
Msg 1205, Level 13, State 45, Line 8
Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

使用 SQL Server Profiler 分析死鎖圖

1. 啟動 Profiler，選擇四種死鎖相關事件：
   
2. 再次執行死鎖腳本，即可看到圖形化的死鎖關係圖：
   

有趣的現象：第二次執行不再死鎖

再次執行相同語句時，SQL Server 2008 的查詢優化器會識別出 UPDATE 語句實際上不需要修改任何資料（資料已是目標值），因此不會獲取排他鎖，自然也就避免了死鎖。這展示了優化器的智慧行為。