[SQL Server][DeadLock]deadlock_priority 優先升等商務艙

在繁忙的線上交易資料庫(OLTP)世界裡,當兩個以上Session都在等待存取彼此鎖定的資源時,資料庫就會出現死結而有交易需要被犧牲。就像最近聯合航空強拉旅客下機的事件裡,發生超額訂位且check in後,似乎有航空公司處理的標準;在SQL DataBase Engine中,也有一套選擇下機旅客的標準,只記得會選擇交易復原成本(Cost)較低的犧牲(買促銷票的),有沒有其他要注意的點?

 

如果交易來自後台很硬的預存程序,相較Prepared型或是Ad-hoc Query的交易,預存程序會不會比較占優勢而不被犧牲?! 另外最近因為解題新認識了一個免費升等頭等艙的方法(Set Deadlock priority),可以降低重要的交易被犧牲的機率,一起筆記紀錄。

 


準備環境

USE TempDB
GO
--1.建立資料表
CREATE TABLE t1 (c1 varchar(10))
INSERT t1 SELECT '1'

CREATE TABLE t2 (c1 varchar(10))
INSERT t2 SELECT '1';

--2.建立預存程序
CREATE procedure usp_lock
as

--先更新T1再更新T2
BEGIN TRAN 

UPDATE t1 set c1 = '2'

WAITFOR DELAY '00:01:00'

UPDATE t2 set c1 = '2'
ROLLBACK

 

打開SSMS管理工具,Ctrl + N 準備2個新查詢視窗呼叫:

1.Session ID: 55 (Ad-hoc Query)

--先更新T2再更新T1
print convert(varchar, getdate(), 126)
BEGIN TRAN

UPDATE t2
SET c1 = '2'

WAITFOR DELAY '00:00:20'

UPDATE t1
SET c1 = '2'

ROLLBACK

 

2.Session ID: 99 (Stored Procedure)

USE [tempdb]
GO
print convert(varchar, getdate(), 126)
DECLARE	@return_value int
EXEC @return_value = [dbo].[usp_lock]
SELECT
	'Return Value' = @return_value
GO

 

這邊我們利用Trace Flag 1204觀察,然後依序執行上述兩個查詢,發生死結事件後,從SQL紀錄中可以查詢到兩個交易的成本都是224

 


差別待遇

會不會出現內線交易?在DB肚子裡的預存程序比較不會被犧牲?前面我們發現兩個交易的復原成本相同,這次試試分別先後Check-in,海水退潮就知道誰是內線了。

 

1.先執行臨時性查詢的交易(Ad-hoc Query),再執行預存程序(SP)

後開始執行的預存程序Session id=99被犧牲!!

2.先執行預存程序(SP),再執行臨時性的查詢(Ad-hoc Query)

Ad-hoc Query Session id=55被犧牲

兩個測試都反覆執行了3次,在復原成本相同的情境下,都是晚執行的交易被犧牲, DataBase Engine果然公正的不分藍綠。

 


優先順序升等

透過SET交易連接(Session)的死結優先順序,我們可以強制讓臨時性查詢交易優先登機、升等而不被犧牲。

先查詢預設的優先順序:

select deadlock_priority from sys.dm_exec_sessions where session_id = @@spid

 

預設的deadlock_priority是0!

這邊我們先執行預存程序(SP): Session id=99,但是將Ad-hoc Query的Session id=55提升優先順序至10

set deadlock_priority 10

 

雖然預存程序先執行,但被犧牲的還是預存程序!

DeadLock Priority預設是Normal,也就是0,10分也是最高了,不能再高了。

 


小結:

  • 邏輯不要寫在交易中,交易越短越好。
  • 預存程序負責拿手的資料存取及更新。
  • 線上交易鎖定資源越小越好,交易處理越快越好。
  • 死結優先順序 >  復原成本 >  執行順序。
  • 被犧牲後,SQL 1205訊息總是告訴我們重新搭下一班飛機。

 

人生第一次商務艙,用哩程數升等。

2009-09年BR台北往巴黎班機。

 

 


參考:

SET DEADLOCK_PRIORITY (Transact-SQL)