原文:SQL Server中的鎖的簡(jiǎn)單學(xué)習(xí)

簡(jiǎn)介

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

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

為什么需要鎖

??? 在開始談鎖之前，首先要簡(jiǎn)單了解一下事務(wù)和事務(wù)的ACID屬性。可以參看我之前的一篇關(guān)于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ō)到，查詢本身可不是什么紳士，所以需要被監(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看到的是在查詢時(shí)間點(diǎn)的數(shù)據(jù)庫(kù)鎖的情況，并不包含任何歷史鎖的記錄。可以理解為數(shù)據(jù)庫(kù)在查詢時(shí)間點(diǎn)加鎖情況的快照。sys.dm_tran_locks所包含的信息分為兩類，以resource為開頭的描述鎖所在的資源的信息，另一類以request開頭的信息描述申請(qǐng)的鎖本身的信息。如圖3所示。更詳細(xì)的說(shuō)明可以查看MSDN(http://msdn.microsoft.com/en-us/library/ms190345.aspx)

?????? 圖3.sys.dm_tran_locks

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

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

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

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

?????? 圖5.在Profiler中捕捉鎖信息

???? 但默認(rèn)如果不過(guò)濾的話，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樹，1張表所含的數(shù)據(jù)完全不是一個(gè)粒度的。因此，所謂鎖的粒度，是鎖所在資源的粒度。所在資源的信息也就是前面圖3中以Resource開頭的信息。

??? 對(duì)于查詢本身來(lái)說(shuō)，并不關(guān)心鎖的問(wèn)題。就像你開車并不關(guān)心哪個(gè)路口該有紅綠燈一樣。鎖的粒度和鎖的類型都是由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ù)量，行密度，查詢條件等。但具體判斷條件是微軟沒(méi)有公布的秘密。開發(fā)人員不用擔(dān)心SQL Server是如何決定使用哪個(gè)鎖的。因?yàn)镾QL Server已經(jīng)做了最好的選擇。

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

資源

說(shuō)明

RID

用于鎖定堆中的單個(gè)行的行標(biāo)識(shí)符。

KEY

索引中用于保護(hù)可序列化事務(wù)中的鍵范圍的行鎖。

PAGE

數(shù)據(jù)庫(kù)中的 8 KB 頁(yè)，例如數(shù)據(jù)頁(yè)或索引頁(yè)。

EXTENT

一組連續(xù)的八頁(yè)，例如數(shù)據(jù)頁(yè)或索引頁(yè)。

HoBT

堆或 B 樹。 用于保護(hù)沒(méi)有聚集索引的表中的 B 樹（索引）或堆數(shù)據(jù)頁(yè)的鎖。

TABLE

包括所有數(shù)據(jù)和索引的整個(gè)表。

FILE

數(shù)據(jù)庫(kù)文件。

APPLICATION

應(yīng)用程序?qū)Ｓ玫馁Y源。

METADATA

元數(shù)據(jù)鎖。

ALLOCATION_UNIT

分配單元。

DATABASE

整個(gè)數(shù)據(jù)庫(kù)。

??? 表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ì)占用大約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ì)不在上述查詢中行做更新(id是50001，不在圖10中查詢的范圍之內(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è)查詢發(fā)現(xiàn)請(qǐng)求資源上的鎖和自己申請(qǐng)的鎖兼容，那么查詢就可以執(zhí)行下去，但如果不兼容，查詢會(huì)被阻塞。直到所請(qǐng)求的資源上的鎖被釋放。從大類來(lái)看，SQL Server中的鎖可以分為如下幾類:

???? 共享鎖(S鎖）:用于讀取資源所加的鎖。擁有共享鎖的資源不能被修改。共享鎖默認(rèn)情況下是讀取了資源馬上被釋放。比如我讀100條數(shù)據(jù)，可以想像成讀完了第一條，馬上釋放第一條，然后再給第二條數(shù)據(jù)上鎖，再釋放第二條，再給第三條上鎖。以此類推直到第100條。這也是為什么我在圖9和圖10中的查詢需要將隔離等級(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è)查詢需要鎖定頁(yè)或是表時(shí)，再一行行去看這個(gè)頁(yè)和表中所包含的數(shù)據(jù)是否被鎖定就有點(diǎn)太痛苦了。因此SQL Server鎖定一個(gè)粒度比較低的資源時(shí)，會(huì)在其父資源上加上意向鎖，告訴其他查詢這個(gè)資源的某一部分已經(jīng)上鎖。比如，當(dāng)我們更新一個(gè)表中的某一行時(shí)，其所在的頁(yè)和表都會(huì)獲得意向排他鎖，如圖13所示。

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

??? 其它類型的構(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í)行。

總結(jié)

??? 本文簡(jiǎn)單介紹了SQL Server中鎖的概念,原理，以及鎖的粒度，模式，兼容性和死鎖。透徹的理解鎖的概念是數(shù)據(jù)庫(kù)性能調(diào)優(yōu)以及解決死鎖的基礎(chǔ)。

總結(jié)

以上是生活随笔為你收集整理的SQL Server中的锁的简单学习的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。