RiCo技術農場

現實世界中很多人似乎對Locks、Blocks、DeadLocks傻傻分不清，

甚至抱怨資料庫為什麼要有Locks、Blocks、DeadLocks行為存在，

以往我執行相關SQL Tuning專案時，可說常常為了這三各行為花了不少時間向使用者解釋，

下面我大概簡單說明一下。

由於資料庫是服務多人，所以資料庫存在Lock行為是相當正常且必要的機制(除非你不在乎資料正確和一致性)，

SQL Server 透過Lock Manager管理各資源物件的鎖定模式和粒度，

將可確保每位使用者所取得資料內容是一致且正確的(SQL Server預設為 read committed)，

但長時間的Lock可能會造成Blocks其他 Process，

長時間的Blocks又可能會產生 DeadLocks ，

當 DeadLocks 產生時，這時 Lock Manager 就會先依照死結優先權，

選擇結束優先權較低 Process ，如果兩個 Process 的死結優先權相同的話，

那就會在估算那各 Rollback Cost 較低並結束該Process ，

並讓另一各 Process 可以順利完成。

 

而我之前處理SQL Tuning時，使用者詢問一個有趣的問題，

如果兩個交易處理作業發生非預期DeadLocks情況，

但某交易作業相當重要一定完成，

可否控制 Lock Manager 不要選擇結束該交易作業，

並結束另一交易作業好讓重要的作業可以順利完成，

答案當然是可以的，可以透過 DEADLOCK_PRIORITY 來達到這樣的需求，

下面我簡單測試驗證。

 

模擬測試

session1和session2 同時執行下面更新作業

BEGIN tran
UPDATE A SET c2='rico' WHERE c1=1

(session1)

 

 

 

BEGIN tran
update B SET c2='sherry' WHERE c1=1

 

(session2 )

 

 

 

然後session1和session2 分別執行下面的讀取作業

SELECT *
from B

 

(session1)

 

 

 

SELECT *
from A

 

(session2 )

 

 

 

死結發生

可以看到SQL Server 拋出1205錯誤訊息，Lock Manager自動選擇結束 SPID 56(session2) 交易作業，

但session2作業相當重要，下面透過 DEADLOCK_PRIORITY 來控制死結行為，並讓session2作業順利完成。

 

 

使用DEADLOCK_PRIORITY 模擬測試

session1和session2 同時執行下面更新作業

--這個變數必須設成(-10 至10) 範圍內的整數值
SET DEADLOCK_PRIORITY -5  ;
BEGIN tran
UPDATE A SET c2='rico' WHERE c1=1

(session1)

--這個變數必須設成(-10 至10) 範圍內的整數值
SET DEADLOCK_PRIORITY 5  ;
BEGIN tran
update B SET c2='sherry' WHERE c1=1

 

(session2 )

因為session2作業相當重要，所以設定優先權等級高於session1作業。

這時session1和session2 分別執行下面的讀取作業

SELECT *
from B

 

(session1)

 

 

 

SELECT *
from A

(session2 )

死結發生

這時可以看到這時選擇結束 SPID 52(session1) 交易作業，

重要的session2作業正常執行(如下圖)。

結論:

大部分死結問題都是可以解決並預防的，

而發生死結的問題大部分都是因為不良的SQL和索引設計造成(資料庫設計佔少數)，

當然還有應用程式撰寫問題也不在少數(個人經驗...勿戰)，

所以SQL Tuning 個人認為極為重要。

 

 

 

參考

[SQL SERVER][Maintain]使用trace flag擷取死結資訊

[SQL SERVER][TS] 找出 blocking 源頭SQL

SET DEADLOCK_PRIORITY (Transact-SQL)