[SQL Server][DeakLock]觀察死結的工具(一)首部曲

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

訊息 1205,層級 13,狀態 45,行 15                                                                                                                                                                       交易 (處理序識別碼 59) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。

 

如果單就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