【茶包射手日記】只涉及單一資料表的Deadlock

在我原本狹隘的SQL知識裡，Deadlock發生情境需要兩個Process A與B跟兩個Table X與Y搭配演出: A鎖定住X想更新Y，B鎖定Y等著要更新X，產生無解的僵持，再由SQL二者擇一選為犠牲者，令其失敗來成立另一個Process。

直到最近處理一起Deadlock案例，才又長了見識。一個處理流水序號的Stored Procedure，讀取與更新對象只限同一Table，並不構成井底之蛙心中的Deadlock成立要件: 兩個鎖定對象、相反的讀取/更新順序，但Deadlock卻硬生生地發生了!

試著用以下方式模擬重現問題。以下的SQL指令，會在一個Transaction中先讀取LockLab的特定計數欄位，再將其更新加1，為確保不會有Phantom Read及Non-Repeatable Read，隔離層級拉高到Serializable(關於隔離層級: 小朱有篇鎖定使用的藝術 (Part 2) - 隔離層次 (Isolation Level)可參考)。為故意製造Deadlock，指令中再加入WAITFOR拖長Transaction的時間到10秒，同時開兩個SSMS執行，就能輕易讓二者強碰相咬。(真實案例因執行時間很短，數千到上萬次才會發生一次Deadlock)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRAN

DECLARE @i INT;

SELECT @i = Seq FROM LockLab WHERE Code='JEFF' AND YearMon='201212'

SET @i = @i + 1;

WAITFOR DELAY '00:00:10'

UPDATE LockLab SET Seq = @i WHERE Code = 'JEFF' AND YearMon='201212'

--其他程式邏輯(略)

COMMIT TRAN

執行結果如下圖所示，同時執行兩份SSMS，其中一個成功，另一個因Deadlock被選為犠牲者:

用SQL Profiler調出事故現場軌跡圖。兩個Process都放了Shared Lock(S)在PK_LockLab上，當要更新再對PK_LockLab放上Exclusive Lock(X)時形成對峙，造成Deadlock!!

原本腦中死板板地只有兩個Process加兩個Table的典型Deadlock案例，百思不得其解，兩組完全相同的SQL指令對同一個資料表先讀後寫，順序完全一致，怎麼會冒出Deadlock? 思索好久才恍然大悟，是鎖定升級造成的!! 初期的SELECT動作，因宣告了SERIALIZABLE隔離層級，SELECT時對PK_LockLab放上了Shared Lock；之後要UPDATE時，再升級成Exlusive Lock，但此時另一個Process已放了Shared Lock，故要等待對方釋放Lock才能繼續。然而不久之後，對方也想放Exclusive Lock，卻卡在前Process的Shared Lock。碰! Deadlock!!!

想通了這點，問題其實不難解。在此情境下，我們可在SELECT時透過UPDLOCK提示要求SQL直接使用Update Lock(U)，避開先S後X的兩階段鎖定過程，便能排除形成Deadlock的條件。將T-SQL改成以下寫法，就能避免Deadlock囉! (但第二個執行的Process需等待第一個Process執行完畢才能SELECT成功，故總共要20秒才能執行完畢，合理。)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRAN

DECLARE @i INT;

SELECT @i = Seq FROM LockLab (UPDLOCK) WHERE Code='JEFF' AND YearMon='201212'

SET @i = @i + 1;

WAITFOR DELAY '00:00:10'

UPDATE LockLab SET Seq = @i WHERE Code = 'JEFF' AND YearMon='201212'

--其他程式邏輯(略)

COMMIT TRAN

在MSDN Lock Modes說明中，也提到了這點:

更新 (U)鎖定模式
用於可更新的資源上。防止當多個工作階段正在讀取、鎖定及後來可能更新資源時發生常見的死結。

回頭想想，過去咬定Deadlock"一個巴掌拍不響"的迷思，恐怕曾導致自己在處理Deadlock議題時多次誤入歧途而不自知，難免心頭一驚。但至少今天起對Deadlock的形成情境又有了新的認識，猶未晚矣~