[SQL SERVER][Memo]如何解釋 trace flag 死結資訊

[SQL SERVER][Memo]如何解釋 trace flag 死結資訊

網友問題,為何單純的查詢會發生deadlock呢?

SQL Server中,只要有兩個以上的Process,因為某些資源而產生循環相依時,

就會發生死結,SQL Server預設隔離等級(read commited),

主要確保使用者讀取資料的一致性和正確性,

也保證允許相同交易內所讀取資料會相同。

 

透過網友所提供資訊來看,這deadlock類型應該是內部平行查詢死結,

而這有可能是SQL Server parallelism synchronization 程式上的Bug,

下面我將嘗試解釋該如何閱讀該死結資訊,

並說明內部平行查詢死結應該會有那些明顯特性,

同時建議應該如何處理才能減少內部平行查詢死結發生頻率。

 

實體作業採用平行處理

image

SQL Server使用多執行序,加快讀取速度。

 

 

 

 

如何解釋死結資訊

deadlock victim 告訴你那個process被kill

image

這裡可以看到process4009948被SQL Server Lock Manager Kill。

 

 

 

process-list 會顯示相關process ID、spid、waitresource、status、prioriry..等資訊。

image

page lock,等待資源"PAGE: 21:1:916625"(可以使用DBCC PAGE查看更詳細資訊),

死結優先權=0、SPID=3540。

 

 

 

這次死結中總共有三個process 參與(擷取部分)

image

process3f43948 spid=6054。

 

 

 

image

processcb522e2c8 沒有任何waitresource,且spid和 process3f43948相同。

 

 

 

executionStack會告訴你process最後執行的陳述句內容和行數

image

可以看到引起死結的語法,都是 select count(*)…

 

 

 

resource-list 直接告訴你死結的原因(和process list有關聯),確認process 週期

image

Pcb522e2c8 已經取得 IX Lock 但需要 S Lock ,可是被P4009948封鎖,類型:Lock轉換。

P4009948 已經取得 IX Lock 但需要 S Lock,可是被P3f43948封鎖,類型:等待。

P3f43948 執行PipeGetRow exchange ,可是被Pcb522e2c8封鎖,類型:等待。

整個週期如下

Pcb522e2c8->P4009948->P3f43948->Pcb522e2c8

這裡就形成了資源循環相依的狀況。

 

 

 

看到這裡你應該知道內部平行死結的特性有那些

1.process list區段中,超過兩個以上不同的process id,卻有相同SPID和SBID。

2.process list區段中,有些process 沒有任何watiresource。

3.resource-list區段中,會出現exchangeEvent或threadpool關鍵字

 

 

如何減少內部平行查詢死結

1.改寫查詢SQL(如果可以的話)。

2.針對查詢SQL建立正確Index,減少整體成本(使用 seek),

不讓SQL Server使用平行執行計畫。

3.壓抑平行查詢,maxdop=1。

4.多善用try catch預防處理死結(如果不幸發生,也不要讓前端系統掛掉)。 


--Error 1205 is a deadlock
IF ( ERROR_NUMBER() =1205 )    
--query again

 

 

 

參考

[SQL SERVER][Maintain]使用trace flag擷取死結資訊

偵測與結束死結

您可能會收到錯誤訊息 8650,當您執行查詢,會使用內部查詢的平行處理原則

Today's Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks"