T-SQL查詢進階—理解SQL Server中的鎖

簡介

    在SQL Server中，每一個查詢都會找到最短路徑實現自己的目標。如果資料庫只接受一個串連一次只執行一個查詢。那麼查詢當然是要多快好省的完成工作。但對於大多數資料庫來說是需要同時處理多個查詢的。這些查詢並不會像紳士那樣排隊等待執行，而是會找最短的路徑執行。因此，就像十字路口需要一個紅綠燈那樣，SQL Server也需要一個紅綠燈來告訴查詢:什麼時候走，什麼時候不可以走。這個紅綠燈就是鎖。

   

    圖1.查詢可不會像紳士們那樣按照次序進行排隊

 

為什麼需要鎖

    在開始談鎖之前，首先要簡單瞭解一下事務和事務的ACID屬性。可以參看我之前的一篇關於ACID的文章。如果你瞭解了事務之間的影響方式，你就應該知道在資料庫中，理論上所有的事務之間應該是完全隔離的。但是實際上，要實現完全隔離的成本實在是太高(必須是序列化的隔離等級才能完全隔離，這個並發性有點….)。所以，SQL Server預設的Read Commited是一個比較不錯的在隔離和並發之間取得平衡的選擇。

    SQL Server通過鎖，就像十字路口的紅綠燈那樣，告訴所有並發的串連，在同一時刻上，那些資源可以讀取，那些資源可以修改。前面說到，查詢本身可不是什麼紳士，所以需要被監管。當一個事務需要訪問的資源加了其所不相容的鎖，SQL Server會阻塞當前的事務來達成所謂的隔離性。直到其所請求資源上的鎖被釋放，2所示。

   

    圖2.SQL Server通過阻塞來實現並發

 

如何查看鎖

    瞭解SQL Server在某一時間點上的加鎖情況無疑是學習鎖和診斷資料庫死結和效能的有效手段。我們最常用的查看資料庫鎖的手段不外乎兩種:

    使用sys.dm_tran_locks這個DMV

         SQL Server提供了sys.dm_tran_locks這個DMV來查看當前資料庫中的鎖,前面的圖2就是通過這個DMV來查看的.

         這裡值得注意的是sys.dm_tran_locks這個DMV看到的是在查詢時間點的資料庫鎖的情況，並不包含任何曆史鎖的記錄。可以理解為資料庫在查詢時間點加鎖情況的快照。sys.dm_tran_locks所包含的資訊分為兩類，以resource為開頭的描述鎖所在的資源的資訊，另一類以request開頭的資訊描述申請的鎖本身的資訊。3所示。更詳細的說明可以查看MSDN(http://msdn.microsoft.com/en-us/library/ms190345.aspx)

      

       圖3.sys.dm_tran_locks

 

        這個DMV包含的資訊比較多，所以通常情況下，我們都會寫一些語句來從這個DMV中提取我們所需要的資訊。4所示。

       

         圖4.寫語句來提取我們需要的鎖資訊

 

    使用Profiler來捕捉鎖資訊

       我們可以通過Profiler來捕捉鎖和死結的相關資訊，5所示。

      

       圖5.在Profiler中捕捉鎖資訊

 

     但預設如果不過濾的話，Profiler所捕捉的鎖資訊包含SQL Server內部的鎖，這對於我們查看鎖資訊非常不方便，所以往往需要篩選列，6所示。

   

    圖6.篩選掉資料庫鎖的資訊

 

    所捕捉到的資訊7所示。

   

    圖7.Profiler所捕捉到的資訊

 

鎖的粒度

    鎖是加在資料庫物件上的。而資料庫物件是有粒度的，比如同樣是1這個單位，1行，1頁，1個B樹，1張表所含的資料完全不是一個粒度的。因此，所謂鎖的粒度，是鎖所在資源的粒度。所在資源的資訊也就是前面圖3中以Resource開頭的資訊。

    對於查詢本身來說，並不關心鎖的問題。就像你開車並不關心哪個路口該有紅綠燈一樣。鎖的粒度和鎖的類型都是由SQL Server進行控制的（當然你也可以使用鎖提示，但不推薦）。鎖會給資料庫帶來阻塞，因此越大粒度的鎖造成更多的阻塞，但由於大粒度的鎖需要更少的鎖，因此會提升效能。而小粒度的鎖由於鎖定更少資源，會減少阻塞，因此提高了並發，但同時大量的鎖也會造成效能的下降。因此鎖的粒度對於效能和並發的關係8所示。

   

    圖8.鎖粒度對於效能和並發的影響

 

    SQL Server決定所加鎖的粒度取決於很多因素。比如鍵的分布，請求行的數量，行密度，查詢條件等。但具體判斷條件是微軟沒有公布的秘密。開發人員不用擔心SQL Server是如何決定使用哪個鎖的。因為SQL Server已經做了最好的選擇。

    在SQL Server中，鎖的粒度如表1所示。

 

資源	說明
RID	用於鎖定堆中的單個行的行標識符。
KEY	索引中用於保護可序列化事務中的鍵範圍的行鎖。
PAGE	資料庫中的 8 KB 頁，例如資料頁或索引頁。
EXTENT	一組連續的八頁，例如資料頁或索引頁。
HoBT	堆或 B 樹。 用於保護沒有叢集索引的表中的 B 樹（索引）或堆資料頁的鎖。
TABLE	包括所有資料和索引的整個表。
FILE	資料庫檔案。
APPLICATION	應用程式專用的資源。
METADATA	中繼資料鎖。
ALLOCATION_UNIT	配置單位。
DATABASE	整個資料庫。

    表1.SQL Server中鎖的粒度

 

    鎖的升級

       前面說到鎖的粒度和效能的關係。實際上，每個鎖會佔96位元組的記憶體，如果有大量的小粒度鎖，則會佔據大量的記憶體。

       下面我們來看一個例子，當我們選擇幾百行資料時(總共3W行),SQL Server會加對應行數的Key鎖，9所示

       

         圖9.341行，則需要動用341個key鎖

 

    但當所取得的行的數目增大時，比如說6000（表中總共30000多條資料）,此時如果用6000個鍵鎖的話，則會佔用大約96*6000=600K左右的記憶體，所以為了平衡效能與並發之間的關係，SQL Server使用一個表鎖來替代6000個key鎖，這就是所謂的鎖定擴大。10所示。

   

    圖10.使用一個表鎖代替6000個鍵鎖

 

    雖然使用一個表鎖代替了6000個鍵鎖，但是會影響到並發，我們對不在上述查詢中行做更新(id是50001，不在圖10中查詢的範圍之內)，發現會造成阻塞，11所示。

   

    圖11.鎖定擴大提升效能以減少並發為代價

 

 

鎖模式

    當SQL Server請求一個鎖時，會選擇一個影響鎖的模式。鎖的模式決定了鎖對其他任何鎖的相容層級。如果一個查詢發現請求資源上的鎖和自己申請的鎖相容，那麼查詢就可以執行下去，但如果不相容，查詢會被阻塞。直到所請求的資源上的鎖被釋放。從大類來看，SQL Server中的鎖可以分為如下幾類:

     共用鎖定(S鎖）:用於讀取資源所加的鎖。擁有共用鎖定的資源不能被修改。共用鎖定預設情況下是讀取了資源馬上被釋放。比如我讀100條資料，可以想像成讀完了第一條，馬上釋放第一條，然後再給第二條資料上鎖，再釋放第二條，再給第三條上鎖。以此類推直到第100條。這也是為什麼我在圖9和圖10中的查詢需要將隔離等級設定為可重複讀，只有設定了可重複讀以上層級的隔離等級或是使用提示時，S鎖才能持續到事務結束。實際上，在同一個資源上可以加無數把S鎖。

    獨佔鎖定(X鎖): 和其它任何鎖都不相容，包括其它獨佔鎖定。排它鎖用於資料修改，當資源上加了獨佔鎖定時，其他請求讀取或修改這個資源的事務都會被阻塞，知道獨佔鎖定被釋放為止。

    更新鎖定(U鎖) :U鎖可以看作是S鎖和X鎖的結合，用於更新資料，更新資料時首先需要找到被更新的資料，此時可以理解為被尋找的資料上了S鎖。當找到需要修改的資料時，需要對被修改的資源上X鎖。SQL Server通過U鎖來避免死結問題。因為S鎖和S鎖是相容的，通過U鎖和S鎖相容，來使得更新尋找時並不影響資料尋找，而U鎖和U鎖之間並不相容，從而減少了死結可能性。這個概念12所示。

   

     圖12.如果沒有U鎖，則S鎖和X鎖修改資料很容易造成死結

 

    意圖鎖定(IS,IU,IX):意圖鎖定與其說是鎖，倒不如說更像一個指標。在SQL Server中，資源是有層次的，一個表中可以包含N個頁，而一個頁中可以包含N個行。當我們在某一個行中加了鎖時。可以理解成包含這個行的頁，和表的一部分已經被鎖定。當另一個查詢需要鎖定頁或是表時，再一行行去看這個頁和表中所包含的資料是否被鎖定就有點太痛苦了。因此SQL Server鎖定一個粒度比較低的資源時，會在其父資源上加上意圖鎖定，告訴其他查詢這個資源的某一部分已經上鎖。比如，當我們更新一個表中的某一行時，其所在的頁和表都會獲得意向獨佔鎖定，13所示。

   

    圖13.當更新一行時，其所在的頁和表都會獲得意圖鎖定

 

    其它類型的構架鎖，關鍵範圍鎖定和大容量更新鎖定就不詳細討論了，參看MSDN（http://msdn.microsoft.com/zh-cn/library/ms175519.aspx）

 

    鎖之間的相容性微軟提供了一張詳細的表，14所示。

   

    圖14.鎖的相容性列表

 

理解死結

    當兩個進程都持有一個或一組鎖時，而另一個進程持有的鎖和另一個進程視圖獲得的鎖不相容時。就會發生死結。這個概念15所示。

   

     圖15.死結的簡單示意

 

     下面我們根據圖15的概念，來類比一個死結，16所示。

    

     圖16.類比一個死結

 

     可以看到，出現死結後，SQL Server並不會袖手旁觀讓這兩個進程無限等待下去，而是選擇一個更加容易Rollback的事務作為犧牲品，而另一個事務得以正常執行。

 

總結

    本文簡單介紹了SQL Server中鎖的概念,原理，以及鎖的粒度，模式，相容性和死結。透徹的理解鎖的概念是資料庫效能調優以及解決死結的基礎。

本文範例程式碼