最近的案子中,在測試及正式環境都碰到了幾次資料庫交易死結(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