第十六章——處理鎖、阻塞和死結（3）——使用SQLServer Profiler偵測死結

標籤：

原文: 第十六章——處理鎖、阻塞和死結（3）——使用SQLServer Profiler偵測死結

前言：

作為DBA，可能經常會遇到有同事或者客戶反映經常發生死結，影響了系統的使用。此時，你需要儘快偵測和處理這類問題。

死結是當兩個或者以上的事務互相阻塞引起的。在這種情況下兩個事務會無限期地等待對方釋放資源以便操作。下面是死結的：

本文將使用SQLServer Profiler來跟蹤死結。

 

準備工作：

為了偵測死結，我們需要先類比死結。本例將使用兩個不同的會話建立兩個事務。

 

步驟：

1、 開啟SQLServer Profiler

2、 選擇【建立跟蹤】，連到執行個體。

3、 然後選擇【空白】模版：

4、 在【事件選擇】頁中，展開Locks事件，並選擇以下事件：

1、 Deadlock graph

2、 Lock:Deadlock

3、 Lock:Deadlock Chain

5、 然後開啟TSQL事件，並選擇以下事件：

1、 SQL:StmtCompleted

2、 SQL:StmtStarting

6、 點擊【資料行篩選】，在跟蹤屬性中，選擇資料庫名為需要偵測的資料庫，這裡使用AdventureWorks。

7、 在【組織列】中，調整順序，如下：

8、 點擊運行。

9、 然後開啟SQLServer，並開啟兩個串連。

10、 在第一個視窗中輸入並執行下面指令碼：

USE AdventureWorks GOSET TRANSACTION ISOLATION LEVEL REPEATABLE READGOBEGIN TRANSACTIONSELECT  *FROM    Sales.SalesOrderDetailWHERE   SalesOrderDetailID = 121316

11、 然後在第二個視窗中輸入並執行下面指令碼： 

USE AdventureWorksGOSET TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN TRANSACTIONSELECT  *FROM    Sales.SalesOrderDetailWHERE   SalesOrderDetailID = 121317

12、現在回到第一個表單，並運行下面的指令碼： 

UPDATE Sales.SalesOrderDetailSET OrderQty=2WHERE SalesOrderDetailID=121317

13、在第二個視窗輸入下面語句： 

UPDATE Sales.SalesOrderDetailSET OrderQty=2WHERE SalesOrderDetailID=121316

14、 然後在第二個視窗就會看到下面的訊息： 

15、切換到SQLServer Profiler，可以看到下面的：

16、 點擊【Deadlock graph】時間，會顯示死結的映像：

17、可以儲存死結映像，右鍵然後選擇匯出事件數目據，並另存新檔xdl檔案：

下面是其XML格式：

 

分析：

在本文中，首先建立一個Profiler空白模版，然後選擇下面的事件進行監控：

 

1、 Deadlock graph

2、 Lock:Deadlock

3、 Lock:Deadlock Chain

4、 SQL:StmtCompleted

5、 SQL:StmtStarting

然後通過限定資料庫，來限制監控過得物件範圍。

在配置好之後，運行跟蹤，並在ssms中運行指令碼。SQLServer會自動處理和偵測這種類型的死結。然後會在第二個表單中收到1205的錯誤。

在SQLServer Profiler中，示範了如何收集死結事件，在跟蹤結果中可以看到兩個事務嘗試在一個擁有共用鎖定的鍵上添加排它鎖。通過死結映像，可以看到死結發生的細節。

為了避免或者最小化死結的發生，有一些建議可以參考：

1、 確保你的事務儘可能地小，這裡指範圍。

2、 使用較低隔離等級的事務。

3、 對於可能的查詢，使用NOLOCK查詢提示。

4、 正常化資料庫設計。

5、 在需要的列上建立索引，以便是表不需要經常掃描，減少鎖問題的發生。

6、 控制資料庫物件訪問的順序是相同的順序。

 

 

第十六章——處理鎖、阻塞和死結（3）——使用SQLServer Profiler偵測死結