SQL Server中的鎖的簡單學習

發表於2016-07-10

簡介

在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	用於鎖定堆中的單個行的行識別符號。
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所示。