最近的案子中，在測試及正式環境都碰到了幾次資料庫交易死結(DeadLock)而有交易被犧牲，有一次還碰上了查詢交易的死結(內部平行查詢死結intra-Query Parallel Deadlock)，由於SQL Server發生死結(DeadLock)的原因很多，因為經驗不足，自己沒碰過的碰過的多，踏出解問題的第一步就是紀錄死結資訊。
來筆記幾種觀察死結問題的工具。
- Trace flag(1222,1204)
- SQL Profiler
- Extended events
我們把內容分成4篇筆記，讓也是工程師的老婆輕鬆動手做。
第一篇先準備好死結的環境。
死結:
常見的是循環類型死結，交易彼此間使用的資源互斥卻互鎖，這個資源可能是各種資料物件(資料表、分頁、資料列)，也可能是執行緒、記憶體甚至外部資源。
發生的原因是當二個或多個交易各自具有某個資源的鎖定，但交易間想嘗試鎖定對方交易已鎖定的資源，而造成永久封鎖(Blocking)導入僵局，也就是死結。簡單畫一個圖例：
交易間陷入了僵局
- (1)Transaction 1持有Resource1的鎖定(hold)，但交易尚未結束，因此準備進行下一個指令(3)。
- (2)Transaction 2持有Resource2的鎖定(hold)，但交易尚未結束，因此準備進行下一個指令(4)。
- (3)Transaction 1準備鎖定Resource2，但Resource2被Transaction 2鎖定，因此被暫時封鎖而等待(wait)。
- (4)Transaction 2準備鎖定Resource1，但Resource1被Transaction 1鎖定，因此被暫時封鎖而等待(wait)。
- 可以發現Trsnaction 1與2使用的資源有互斥(mutual exclusion)，因此陷入了你等我我等你的迴圈等待(circular waiting)狀態，沒有一項工作可以繼續執行，出現了死結狀態。
好在這時候SQL Server會跳出來公正的仲裁，正所謂犧牲小我完成大我，SQL Server會將復原交易成本較低的交易犧牲(victim)，避免僵局時間太久而讓交通大打結。
Transaction 2 被迫選擇犧牲(查詢交易與其他更新或寫入交易衝突時，查詢交易的復原成本也是最低的)
循環死結:
循環死結，簡單的互鎖
複雜的三角關係，多個交易間的封鎖僵局。
建立死結(DeadLock)的環境
為了讓之後的筆記順利完成，今天先準備環境。
1.建立死結測試所需要的資料庫
CREATE DATABASE [DeadLockDb]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'DeadLockDb', FILENAME = N'C:\temp\db\DeadLockDb.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'DeadLockDb_log', FILENAME = N'C:\temp\db\DeadLockDb_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%)
GO
2.建立死結測試所需要的兩個資料表
create table t1
(
c1 int identity,
c2 varchar(30)
)
create table t2
(
c1 int identity,
c3 varchar(30)
)
3.建立死結測試所需要的測試資料
insert t1 VALUES('Stanley')
insert t2 VALUES('Suzie')
執行測試死結的語法
只要讓兩個交易互斥鎖定物件時間長 (15秒)而且存取Table資源的順序不同。
查詢視窗1(先更新t1再更新t2)
BEGIN Tran Tran_one
UPDATE t1
set c2 = 'lock'
WAITFOR DELAY '00:00:15';
UPDATE t2
set c3 = 'lock'
ROLLBACK
查詢視窗2(先更新t2再更新t1):必須在15秒內執行
BEGIN Tran Tran_two
UPDATE t2
set c3 = 'lock'
WAITFOR DELAY '00:00:15';
UPDATE t1
set c2 = 'lock'
ROLLBACK
查詢視窗1 x 2的執行結果:
查詢視窗2出現了以下的錯誤訊息:
如果單就log觀察，我們AP只能收到以上1205還有被犧牲的錯誤訊息，後面我們來筆記幾種觀察死結的工具。
小結:
解決循環死結的方向:
- 程式Handle 1205 Error發生後，等待幾秒鐘，自動重新執行。
- 以相同的順序來存取物件、降低鎖定粒度(Table lock -> Key Lock)、使用較低的隔離層級以及降低鎖定的時間(效能調校)等。
參考:
technet偵測與結束死結
https://technet.microsoft.com/zh-tw/library/ms178104(v=sql.105).aspx
Deadlocking
https://technet.microsoft.com/en-us/library/ms177433(v=sql.105).aspx
處理死結
https://technet.microsoft.com/zh-tw/library/ms177453(v=sql.105).aspx
將死結數量降至最低
https://technet.microsoft.com/zh-tw/library/ms191242(v=sql.105).aspx