在繁忙的線上交易資料庫(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)