在SQL Server中，每一個(gè)查詢(xún)都會(huì)找到最短路徑實(shí)現(xiàn)自己的目標(biāo)。如果數(shù)據(jù)庫(kù)只接受一個(gè)連接一次只執(zhí)行一個(gè)查詢(xún)。那么查詢(xún)當(dāng)然是要多快好省的完成工作。但對(duì)于大多數(shù)數(shù)據(jù)庫(kù)來(lái)說(shuō)是需要同時(shí)處理多個(gè)查詢(xún)的。這些查詢(xún)并不會(huì)像紳士那樣排隊(duì)等待執(zhí)行，而是會(huì)找最短的路徑執(zhí)行。因此，就像十字路口需要一個(gè)紅綠燈那樣，SQL Server也需要一個(gè)紅綠燈來(lái)告訴查詢(xún):什么時(shí)候走，什么時(shí)候不可以走。這個(gè)紅綠燈就是鎖。

圖1.查詢(xún)可不會(huì)像紳士們那樣按照次序進(jìn)行排隊(duì)

在開(kāi)始談鎖之前，首先要簡(jiǎn)單了解一下事務(wù)和事務(wù)的ACID屬性。如果你了解了事務(wù)之間的影響方式，你就應(yīng)該知道在數(shù)據(jù)庫(kù)中，理論上所有的事務(wù)之間應(yīng)該是完全隔離的。但是實(shí)際上，要實(shí)現(xiàn)完全隔離的成本實(shí)在是太高(必須是序列化的隔離等級(jí)才能完全隔離，這個(gè)并發(fā)性有點(diǎn)….)。所以，SQL Server默認(rèn)的Read Commited是一個(gè)比較不錯(cuò)的在隔離和并發(fā)之間取得平衡的選擇。SQL Server通過(guò)鎖，就像十字路口的紅綠燈那樣，告訴所有并發(fā)的連接，在同一時(shí)刻上，那些資源可以讀取，那些資源可以修改。前面說(shuō)到，查詢(xún)本身可不是什么紳士，所以需要被監(jiān)管。當(dāng)一個(gè)事務(wù)需要訪問(wèn)的資源加了其所不兼容的鎖，SQL Server會(huì)阻塞當(dāng)前的事務(wù)來(lái)達(dá)成所謂的隔離性。直到其所請(qǐng)求資源上的鎖被釋放，如圖2所示。

圖2.SQL Server通過(guò)阻塞來(lái)實(shí)現(xiàn)并發(fā)

了解SQL Server在某一時(shí)間點(diǎn)上的加鎖情況無(wú)疑是學(xué)習(xí)鎖和診斷數(shù)據(jù)庫(kù)死鎖和性能的有效手段。我們最常用的查看數(shù)據(jù)庫(kù)鎖的手段不外乎兩種:

使用sys.dm_tran_locks這個(gè)DMV

SQL Server提供了sys.dm_tran_locks這個(gè)DMV來(lái)查看當(dāng)前數(shù)據(jù)庫(kù)中的鎖,前面的圖2就是通過(guò)這個(gè)DMV來(lái)查看的.

這里值得注意的是sys.dm_tran_locks這個(gè)DMV看到的是在查詢(xún)時(shí)間點(diǎn)的數(shù)據(jù)庫(kù)鎖的情況，并不包含任何歷史鎖的記錄。可以理解為數(shù)據(jù)庫(kù)在查詢(xún)時(shí)間點(diǎn)加鎖情況的快照。sys.dm_tran_locks所包含的信息分為兩類(lèi)，以resource為開(kāi)頭的描述鎖所在的資源的信息，另一類(lèi)以request開(kāi)頭的信息描述申請(qǐng)的鎖本身的信息。如圖3所示。更詳細(xì)的說(shuō)明可以查看MSDN(http://msdn.microsoft.com/en-us/library/ms190345.aspx)

圖3.sys.dm_tran_locks

  這個(gè)DMV包含的信息比較多，所以通常情況下，我們都會(huì)寫(xiě)一些語(yǔ)句來(lái)從這個(gè)DMV中提取我們所需要的信息。如圖4所示。

圖4.寫(xiě)語(yǔ)句來(lái)提取我們需要的鎖信息

使用Profiler來(lái)捕捉鎖信息

我們可以通過(guò)Profiler來(lái)捕捉鎖和死鎖的相關(guān)信息，如圖5所示。

圖5.在Profiler中捕捉鎖信息

  但默認(rèn)如果不過(guò)濾的話(huà)，Profiler所捕捉的鎖信息包含SQL Server內(nèi)部的鎖，這對(duì)于我們查看鎖信息非常不方便，所以往往需要篩選列，如圖6所示。

圖6.篩選掉數(shù)據(jù)庫(kù)鎖的信息

所捕捉到的信息如圖7所示。

圖7.Profiler所捕捉到的信息

鎖是加在數(shù)據(jù)庫(kù)對(duì)象上的。而數(shù)據(jù)庫(kù)對(duì)象是有粒度的，比如同樣是1這個(gè)單位，1行，1頁(yè)，1個(gè)B樹(shù)，1張表所含的數(shù)據(jù)完全不是一個(gè)粒度的。因此，所謂鎖的粒度，是鎖所在資源的粒度。所在資源的信息也就是前面圖3中以Resource開(kāi)頭的信息。

對(duì)于查詢(xún)本身來(lái)說(shuō)，并不關(guān)心鎖的問(wèn)題。就像你開(kāi)車(chē)并不關(guān)心哪個(gè)路口該有紅綠燈一樣。鎖的粒度和鎖的類(lèi)型都是由SQL Server進(jìn)行控制的（當(dāng)然你也可以使用鎖提示，但不推薦）。鎖會(huì)給數(shù)據(jù)庫(kù)帶來(lái)阻塞，因此越大粒度的鎖造成更多的阻塞，但由于大粒度的鎖需要更少的鎖，因此會(huì)提升性能。而小粒度的鎖由于鎖定更少資源，會(huì)減少阻塞，因此提高了并發(fā)，但同時(shí)大量的鎖也會(huì)造成性能的下降。因此鎖的粒度對(duì)于性能和并發(fā)的關(guān)系如圖8所示。

圖8.鎖粒度對(duì)于性能和并發(fā)的影響

SQL Server決定所加鎖的粒度取決于很多因素。比如鍵的分布，請(qǐng)求行的數(shù)量，行密度，查詢(xún)條件等。但具體判斷條件是微軟沒(méi)有公布的秘密。開(kāi)發(fā)人員不用擔(dān)心SQL Server是如何決定使用哪個(gè)鎖的。因?yàn)镾QL Server已經(jīng)做了最好的選擇。

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

表1.SQL Server中鎖的粒度

鎖的升級(jí)

前面說(shuō)到鎖的粒度和性能的關(guān)系。實(shí)際上，每個(gè)鎖會(huì)占96字節(jié)的內(nèi)存，如果有大量的小粒度鎖，則會(huì)占據(jù)大量的內(nèi)存。

下面我們來(lái)看一個(gè)例子，當(dāng)我們選擇幾百行數(shù)據(jù)時(shí)(總共3W行),SQL Server會(huì)加對(duì)應(yīng)行數(shù)的Key鎖，如圖9所示

圖9.341行，則需要?jiǎng)佑?41個(gè)key鎖

但當(dāng)所取得的行的數(shù)目增大時(shí)，比如說(shuō)6000（表中總共30000多條數(shù)據(jù)）,此時(shí)如果用6000個(gè)鍵鎖的話(huà)，則會(huì)占用大約96*6000=600K左右的內(nèi)存，所以為了平衡性能與并發(fā)之間的關(guān)系，SQL Server使用一個(gè)表鎖來(lái)替代6000個(gè)key鎖，這就是所謂的鎖升級(jí)。如圖10所示。

圖10.使用一個(gè)表鎖代替6000個(gè)鍵鎖

雖然使用一個(gè)表鎖代替了6000個(gè)鍵鎖，但是會(huì)影響到并發(fā)，我們對(duì)不在上述查詢(xún)中行做更新(id是50001，不在圖10中查詢(xún)的范圍之內(nèi))，發(fā)現(xiàn)會(huì)造成阻塞，如圖11所示。

圖11.鎖升級(jí)提升性能以減少并發(fā)為代價(jià)

當(dāng)SQL Server請(qǐng)求一個(gè)鎖時(shí)，會(huì)選擇一個(gè)影響鎖的模式。鎖的模式?jīng)Q定了鎖對(duì)其他任何鎖的兼容級(jí)別。如果一個(gè)查詢(xún)發(fā)現(xiàn)請(qǐng)求資源上的鎖和自己申請(qǐng)的鎖兼容，那么查詢(xún)就可以執(zhí)行下去，但如果不兼容，查詢(xún)會(huì)被阻塞。直到所請(qǐng)求的資源上的鎖被釋放。從大類(lèi)來(lái)看，SQL Server中的鎖可以分為如下幾類(lèi):

  共享鎖(S鎖）:用于讀取資源所加的鎖。擁有共享鎖的資源不能被修改。共享鎖默認(rèn)情況下是讀取了資源馬上被釋放。比如我讀100條數(shù)據(jù)，可以想像成讀完了第一條，馬上釋放第一條，然后再給第二條數(shù)據(jù)上鎖，再釋放第二條，再給第三條上鎖。以此類(lèi)推直到第100條。這也是為什么我在圖9和圖10中的查詢(xún)需要將隔離等級(jí)設(shè)置為可重復(fù)讀，只有設(shè)置了可重復(fù)讀以上級(jí)別的隔離等級(jí)或是使用提示時(shí)，S鎖才能持續(xù)到事務(wù)結(jié)束。實(shí)際上，在同一個(gè)資源上可以加無(wú)數(shù)把S鎖。

排他鎖(X鎖): 和其它任何鎖都不兼容，包括其它排他鎖。排它鎖用于數(shù)據(jù)修改，當(dāng)資源上加了排他鎖時(shí)，其他請(qǐng)求讀取或修改這個(gè)資源的事務(wù)都會(huì)被阻塞，知道排他鎖被釋放為止。

更新鎖(U鎖) :U鎖可以看作是S鎖和X鎖的結(jié)合，用于更新數(shù)據(jù)，更新數(shù)據(jù)時(shí)首先需要找到被更新的數(shù)據(jù)，此時(shí)可以理解為被查找的數(shù)據(jù)上了S鎖。當(dāng)找到需要修改的數(shù)據(jù)時(shí)，需要對(duì)被修改的資源上X鎖。SQL Server通過(guò)U鎖來(lái)避免死鎖問(wèn)題。因?yàn)镾鎖和S鎖是兼容的，通過(guò)U鎖和S鎖兼容，來(lái)使得更新查找時(shí)并不影響數(shù)據(jù)查找，而U鎖和U鎖之間并不兼容，從而減少了死鎖可能性。這個(gè)概念如圖12所示。

  圖12.如果沒(méi)有U鎖，則S鎖和X鎖修改數(shù)據(jù)很容易造成死鎖

意向鎖(IS,IU,IX):意向鎖與其說(shuō)是鎖，倒不如說(shuō)更像一個(gè)指示器。在SQL Server中，資源是有層次的，一個(gè)表中可以包含N個(gè)頁(yè)，而一個(gè)頁(yè)中可以包含N個(gè)行。當(dāng)我們?cè)谀骋粋€(gè)行中加了鎖時(shí)。可以理解成包含這個(gè)行的頁(yè)，和表的一部分已經(jīng)被鎖定。當(dāng)另一個(gè)查詢(xún)需要鎖定頁(yè)或是表時(shí)，再一行行去看這個(gè)頁(yè)和表中所包含的數(shù)據(jù)是否被鎖定就有點(diǎn)太痛苦了。因此SQL Server鎖定一個(gè)粒度比較低的資源時(shí)，會(huì)在其父資源上加上意向鎖，告訴其他查詢(xún)這個(gè)資源的某一部分已經(jīng)上鎖。比如，當(dāng)我們更新一個(gè)表中的某一行時(shí)，其所在的頁(yè)和表都會(huì)獲得意向排他鎖，如圖13所示。

圖13.當(dāng)更新一行時(shí)，其所在的頁(yè)和表都會(huì)獲得意向鎖

其它類(lèi)型的構(gòu)架鎖，鍵范圍鎖和大容量更新鎖就不詳細(xì)討論了，參看MSDN（http://msdn.microsoft.com/zh-cn/library/ms175519.aspx）

鎖之間的兼容性微軟提供了一張?jiān)敿?xì)的表，如圖14所示。

圖14.鎖的兼容性列表

當(dāng)兩個(gè)進(jìn)程都持有一個(gè)或一組鎖時(shí)，而另一個(gè)進(jìn)程持有的鎖和另一個(gè)進(jìn)程視圖獲得的鎖不兼容時(shí)。就會(huì)發(fā)生死鎖。這個(gè)概念如圖15所示。

  圖15.死鎖的簡(jiǎn)單示意

  下面我們根據(jù)圖15的概念，來(lái)模擬一個(gè)死鎖，如圖16所示。

  圖16.模擬一個(gè)死鎖

  可以看到，出現(xiàn)死鎖后，SQL Server并不會(huì)袖手旁觀讓這兩個(gè)進(jìn)程無(wú)限等待下去，而是選擇一個(gè)更加容易R(shí)ollback的事務(wù)作為犧牲品，而另一個(gè)事務(wù)得以正常執(zhí)行。