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所示。