SQL Server 死結 (Page鎖)診斷

標籤：style   blog   http   io   使用   ar   strong   資料   sp   

在資料庫中開啟死結監測可以收集到資料庫發生的死結情況。開啟的方式有2種：

1 開啟1222監控

執行SQL語句：

Dbcc traceon(1222,-1);

 然後在系統日誌裡查看死結的資訊。

2 啟動SQL Profiler(建議使用):

下面就是一個發生死結的執行個體圖：

下面提供對這個死結分析思路，如有不當之處，還望大家批評指正。

一共3個問題，下面逐個回答。

第一個問題：被鎖定資源是什嗎？

上面寫的很清楚，是一個Page 鎖， 那麼Page 鎖是什麼呢？

通常死結是你操作A表，然後又要操作B表，而另外一個進程先操作的B表，然後等待你釋放A表的鎖而導致的死結，這時看的圖上面就會明確說Table Lock，而不是Page Lock。

要解釋Page Lock 得先解釋下什麼是Page。

在SQL Server 中，資料存放的時候是放在一個8K的資料單元裡，這個資料單元稱為 Page.

讀取資料時，不是一筆一筆的讀取，而是一個Page 一個Page 的讀取，所以SQL Server 的文檔中都會說，可能讀取了一些不需要的資料。

知道了Page 之後，怎麼會有Page 鎖呢？ Table Lock ，Row Lock 比較容易理解，但是Page Lock 就不太容易理解，實際上，這時SQL Server 對於大表採取的一種它認為性價比最好的策略。如果採取表鎖，那麼一旦Update 資料，則別人就不能Select 了，否則會出現資料不一致的情況（就是所謂的髒讀，幻讀），但是你有可能會說，你修改你的，我讀取的並不是你要改的那一筆，你不要鎖定我，應該採取Row Lock，但是想象一下，對於一個有100萬筆資料的表來說，採取行鎖，成本得有多高？所以SQL Server採取了一個中庸的方式，使用8K的資料頁Page作為鎖的單位，這樣就平衡了並發與效能的問題，性價比最高。

定位Page

一個表裡有很多Page，那麼本例中鎖住的Page 到底是什麼呢？

執行SQL語句查詢DB的名字

Select db_name(5)

 

開啟這個資料庫，執行如下SQL語句，可以查詢這個Page 屬於誰.

     select object_name(i.object_id), i.name     from sys.partitions as p     inner join sys.indexes as i on i.object_id = p.object_id and i.index_id = p.index_id     where p.partition_id = ‘72057594048544768‘  

 是一個非聚集的索引。

Page裡放了什嗎？

目前我們已經瞭解了，它鎖定的是一個索引的一個資料頁(如果您對索引的Page 組織圖感興趣，請參考 索引內部結構執行個體)。 那麼鎖住的2個Page裡面存放了什麼資訊？

執行如下SQL:

DBCC TRACEON(3604)DBCC Page (DBName,1,46574417,1) 

 結果如下：

執行如下SQL:

DBCC Page (DBName,1,93865146,1) 

 結果如下：

第二個問題：是誰在鎖住這些頁？

查看之後發現：

1個SQL語句是Select

2個SQL 陳述式是Update

Select 語句為什麼要鎖定這2頁？

本例中的Select 語句使用了本索引，使用它來快速定位，那麼它根據索引的分支節點尋找，然後到指定的Page 中去讀取尋找，因為是Top 500，所以，可能找到一批資料，但是還不夠500，所以接著往其他頁裡找，直到找滿500筆為止

Update 語句為什麼要鎖定這2頁？

找到這筆資料直接修改不就完了嗎？並不是的，修改一個索引的值，至少要經過如下3個步驟：

第三個問題： Exchange Event是啥意思？

本例分析總結：

某個Index 裡有多個資料頁，而 Update 語句正在更新這個Index 裡的一筆資料，鎖定了2個頁，與Select 中使用的2頁發生互搶，形成死結。

解決方案：

調整語句，調整Select 嗎？

讓它不使用這個索引？索引建立就是為了給Select 用的，這個Select 不用，其他Select 也會用到。

調整 Update 嗎？

因為更新的欄位是包含在索引中的，無論怎麼調整，更新時總是會同步修改索引的。

結語：

死結是不可避免的，我們要做的是降低發生的頻率，這是我們常聽到的，但是為什麼呢？ 如果是更新表的順序不一致，那麼應該可以通過修改提交更新SQL可以消除，但是本例呢？你打算通過什麼方式讓這種情況永不存在？

降低發生的頻率是對我們的要求，比如本例，怎麼降低？

    降低SQL 執行的頻率：

    發出Select 語句和Update 語句太頻繁，業務上是否真的這麼頻繁？是否都定相同的時間來執行？比如Update和Select 都是5秒輪一次？

    提高SQL執行的效率：

Select 語句返回的資料有多少筆？能否再減少一些？返回有多少欄位？能否減少一些？

Select 語句的查詢計劃是否應該最佳化？最多3-5頁面讀取就應該返回結果，這個Select有多少logic read ?是否需要添加其他準確定位的索引？（難道一個Select 我們就添加一個Index嗎？當然不是，這是在頻繁出現這種死結的情況使用的，因為既然頻繁出現，說明這個 Select執行更頻繁，很容易和Update 撞在一起。）

能不能Select 不要鎖？

業務上是否可以允許小範圍的髒讀？

SQL Server 死結 (Page鎖)診斷