[SQL SERVER][Performance]善用 DEADLOCK_PRIORITY

[SQL SERVER][Performance]善用 DEADLOCK_PRIORITY

現實世界中很多人似乎對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 )

 

 

 

死結發生

image

可以看到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 )


死結發生
image 
這時可以看到這時選擇結束 SPID 52(session1) 交易作業,
重要的session2作業正常執行(如下圖)。
image 
結論:
大部分死結問題都是可以解決並預防的,
而發生死結的問題大部分都是因為不良的SQL和索引設計造成(資料庫設計佔少數),
當然還有應用程式撰寫問題也不在少數(個人經驗...勿戰),
所以SQL Tuning 個人認為極為重要。

 

 

 

參考

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

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

SET DEADLOCK_PRIORITY (Transact-SQL)