使用SQL Server 6年多了，在下自認(rèn)為對(duì)SQL Server還是比較熟悉的，而且我喜歡將SQL Server內(nèi)部的一些 東西搞清楚。
當(dāng)我在教一門SQL Server編程課程時(shí)，我注意到微軟MSDN中提到了鎖兼容性，在 MSDN列舉了一個(gè)兼容性關(guān)系的表格。
看過(guò)這張關(guān)系表格，我就想知道是否存在用于更新的意向鎖(Intent Update lock)？于是我開始閱讀相關(guān)的資料。 這篇文章也是我研究的結(jié)果。這篇文章的適用讀者是那些對(duì)隔離級(jí)別(isolation level)，意向鎖，死鎖和鎖粒度有所了解的。 如果你對(duì)這些領(lǐng)域還不了解，那么我建議你在讀這篇文章前，應(yīng)該先去了解和閱讀相關(guān)資料。
希望這篇文章能夠加深你對(duì)SQL Server鎖的理解，也許有些技巧還能夠在SQL Server編程中帶來(lái)幫助。
必須指出，即使不知道鎖是如何工作的，你也能長(zhǎng)時(shí)間愉快地使用SQL Server，并且能創(chuàng)建高質(zhì)量的代碼和數(shù)據(jù)庫(kù)設(shè)計(jì)。 不過(guò)如果你象我那樣喜歡探究事情的內(nèi)部機(jī)理，或者你的工作需要你掌握一些性能方面的知識(shí)，我很樂(lè)意能教你一些有用的東西。
更新鎖(Update Locks)
死鎖的典型情況是SPID X鎖住了資源A，并在等待對(duì)資源B進(jìn)行加鎖，而SPID Y鎖住了資源B，在等待對(duì)資源A加鎖，如此就 形成了死鎖。如果不理解，查詢 MSDN 或者相關(guān)的資料。
現(xiàn)在來(lái)假想更多情形下的死鎖。假設(shè)：SPID X在資源A上加了共享鎖，SPID Y也在資源A上加了共享鎖，因?yàn)槭枪蚕礞i， 所以這樣沒(méi)有問(wèn)題。現(xiàn)在X想把共享鎖升級(jí)為排它鎖(exclusive lock)以用于更新資源。X就必須等Y釋放共享鎖才能辦到， 當(dāng)X在等待時(shí)，Y也想做同樣的事情。這樣，X在等Y釋放，Y同時(shí)在等待X釋放，死鎖產(chǎn)生了。這種死鎖被稱為轉(zhuǎn)換死鎖(conversion deadlock)。
這種情況會(huì)很常見，為避免這種死鎖，就引入了更新鎖機(jī)制。更新鎖允許連接讀取資源，同時(shí)宣告它因?yàn)橐庉嫈?shù)據(jù)而要開始鎖住資源了。SQL Server并無(wú)法提前知道一個(gè)事務(wù)要把共享鎖轉(zhuǎn)換成排它鎖了，當(dāng)然有一個(gè)情況特殊，即只在一個(gè)SQL語(yǔ)句中 完成讀取然后更新的操作，比如說(shuō)UPDATE XXX (SELECT YYY ....)這種類型。對(duì)于一般的SELECT語(yǔ)句，我們必須顯示地 使用UPDLOCK提示。
下面是代碼示例：
USE Northwind
GO
SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ
GO
BEGIN TRAN
SELECT *
FROM Orders (UPDLOCK)
WHERE OrderID = 10633
注意到我打開了事務(wù)，但并沒(méi)有關(guān)閉事務(wù)。這樣鎖就始終存在。如果另外一個(gè)連接視圖在相同的記錄上獲取更新鎖，就只有等待第一個(gè)事務(wù)結(jié)束后才行。這樣就可以演示，在相同資源上， 兩個(gè)更新鎖不相容的效果。
運(yùn)行SP_LOCK，會(huì)顯示和上面的操作相關(guān)的記錄行，字段以及鎖的情況：
如我們預(yù)想那樣，主鍵OrderID被更新鎖鎖住了。圖中Resource列里面那個(gè)(89003da47679)的值，表示的是 主鍵10633的哈希值。SQL Server使用哈希表的方式來(lái)存儲(chǔ)鎖信息。
包含那行的記錄行，如我們所期望的那樣，被更新意向鎖鎖住了。在resource那列的數(shù)值(1:242)表示該數(shù)據(jù)頁(yè)面是 數(shù)據(jù)庫(kù)的第1個(gè)文件，頁(yè)面編號(hào)是#242。而意外的是，SQL Server添加了一個(gè)IX的表鎖。由于SQL Server不會(huì)在 表鎖上使用U/IU類型鎖，所以在表鎖級(jí)別上，只能看到X/IX類型鎖。
當(dāng)更新操作中帶有where語(yǔ)法，SQL Server會(huì)掃描整個(gè)表，并且/或者掃描索引，以決定那些記錄會(huì)被改變。 在從表/索引讀取信息之前，SQL Server首先把對(duì)象鎖住。既然SQL Server知道你提交的是更新事務(wù)，那么它 就會(huì)選擇更新鎖，而不是共享鎖。這樣做就是為了避免前面所提到的死鎖情況－－轉(zhuǎn)換死鎖(conversion deadlock)。
當(dāng)SQL Server確定那些記錄行需要改變后，在這些記錄上，它會(huì)把更新鎖進(jìn)一步升級(jí)為排它鎖，如果是堆表(heap table)，那么鎖加 在RID(行標(biāo)識(shí)符)上，如果是聚集索引表，鎖加在主鍵上。這就意味著更新鎖會(huì)立刻升級(jí)為排它鎖，因此當(dāng)你執(zhí)行UPDATE 操作時(shí)，幾乎不可能看到這個(gè)更新過(guò)程。
不過(guò)，也有例外。如果SQL Server使用一個(gè)索引來(lái)定位記錄行，它就會(huì)鎖住索引頁(yè)，在索引上加的就是更新鎖。 如果不改變?nèi)魏伟谠撍饕械臄?shù)據(jù)列，更新鎖不會(huì)升級(jí)為排它鎖。下面是一個(gè)例子：
BEGIN TRAN
UPDATE Region
SET RegionDescription = 'South'
WHERE RegionID = 4
Region是一個(gè)堆表，在RegionId上只有非聚集唯一索引主鍵。因此完成上面查詢時(shí)，SQL Server在RegionId上掃描索引， 鎖住索引頁(yè)和索引鍵。當(dāng)發(fā)現(xiàn)要改變得記錄行后，因?yàn)楦虏樵儾⒉桓淖僐egionId的值，因此不會(huì)升級(jí)到排它鎖。 運(yùn)行SP_LOCK后可以得到以下信息：
我們看到，在RID上有一個(gè)IX鎖。該鎖位于RegionId索引上。還可以看到在表上有一個(gè)IX鎖，RID上有一個(gè)X鎖。 KEY鎖在RegionId索引上，證據(jù)可以從Indid列上可以得到。在索引上還有一個(gè)更新鎖，這是更新鎖激活的一個(gè)瞬間之一。
當(dāng)查詢結(jié)束后，仍然存在兩個(gè)頁(yè)面鎖 –- 一個(gè)在索引頁(yè) (1:306)上, 另一個(gè)在堆(heap) (1:300)上。這是因?yàn)?堆的Indid(Index id)為0。
鎖粒度(Lock Granularity)
SQL Server有幾種鎖類型，每種類型都可以選擇不同的粒度。
如果運(yùn)行SP_LOCK，或者查看企業(yè)管理器中"當(dāng)前激活"信息，就可以看到至少四，五中不同的鎖類型。下面簡(jiǎn)單回顧一下這些類型：
在研究SQL Server 2000的鎖行為中，我認(rèn)為SQL Server在大多數(shù)情況下，和速度相比，更看重并發(fā)性能。 較高的并發(fā)性能，意味著很多用戶能同時(shí)對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作。所以鎖盡可能的小，不必要地鎖住別人也需要的數(shù)據(jù)的可能性就越?。涣硪环矫?，如果使用較大的鎖，將獲取更高的速度。（譯者注：這句話的理解應(yīng)該以平衡性能的前提下考查。)
當(dāng)SQL Server 2000發(fā)現(xiàn)操作將鎖住越來(lái)越多的記錄行時(shí)，就會(huì)提高鎖的級(jí)別。 例如SQL Server 2000會(huì)升級(jí)到表鎖，丟掉單獨(dú)的pages/keys/RIDs級(jí)別鎖。注意：提高鎖的級(jí)別肯定是升級(jí)到表鎖，而不會(huì)將RID/KEY鎖升級(jí)到頁(yè)面鎖。
那么SQL Server2000什么時(shí)候升級(jí)鎖呢？它無(wú)法知道你將鎖住的表的比例，因此它唯一在意的就是產(chǎn)生的鎖的數(shù)量。 當(dāng)鎖使用了較高比例的內(nèi)存時(shí)，SQL Server2000就開始升級(jí)所有連接事務(wù)上的鎖了。當(dāng)鎖槽使用將盡時(shí)，也會(huì)開始 升級(jí)工作。你可以用SP_CONFIGURE來(lái)配置SQL Server可用的鎖槽數(shù)，例如降低該數(shù)值，從而來(lái)觀察鎖的升級(jí)情況。
SQL Server會(huì)盡可能使用較小的鎖來(lái)保證較高的并發(fā)性能。但是有時(shí)候SQL Server并不知道數(shù)據(jù)將會(huì)發(fā)生怎么樣的改變， 從而它會(huì)按照它的規(guī)則來(lái)改變鎖的級(jí)別，而這種改變并非你想要的。例如一個(gè)很大的查找表(lookup table)，僅僅是讀取 數(shù)據(jù)。那么你可以直接用一個(gè)表鎖來(lái)替代很多KEY鎖。使用的方法是使用鎖提示或者SP_INDEXOPTION。
鎖提示很普通，在 聯(lián)機(jī)幫助(BOL) 有大量關(guān)于此的文檔，因此在本文就不重復(fù)介紹了。系統(tǒng)存儲(chǔ)過(guò)程SP_INDEXOPTION是強(qiáng)迫SQL Server使用特定大小的鎖 的好辦法。
使用SP_INDEXOPTION，你可以關(guān)閉行或者頁(yè)面級(jí)別的鎖。也就是說(shuō)，你可以不需要鎖提示－－所有表或者索引上的鎖都是你指定的大小。即使BOL宣稱，該存儲(chǔ)過(guò)程用于索引上的鎖粒度，其實(shí)它也能用戶堆上。一個(gè)好的實(shí)現(xiàn) 方法是使用表名來(lái)替代@IndexNamePattern變量，這種方法很少人知道。
關(guān)于這方面的研究并沒(méi)有結(jié)束。如果你使用了兩個(gè)更高隔離級(jí)別中的一個(gè)，且在檢索規(guī)則中沒(méi)有任何可用的索引，那么 SQL Server即使不鎖住整個(gè)表，也會(huì)盡可能多的記錄來(lái)滿足你的查詢。下面是一個(gè)例子：
USE Northwind
GO
SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE
GO
BEGIN TRAN
UPDATE dbo.Orders
SET Freight = Freight * 1.25
WHERE Freight BETWEEN 100 AND 200
在另一個(gè)窗口運(yùn)行SP_LOCK。在我這里運(yùn)行的時(shí)候，我看到該連接上有853個(gè)鎖。數(shù)據(jù)庫(kù)Northwind中的Orders表上 有830行，每行上都有一個(gè)鎖。回滾該UPDATE事務(wù)，然后進(jìn)行改寫，在UPDATE前先創(chuàng)建索引，如下所示：
USE Northwind
GO
CREATE NONCLUSTERED INDEX
FreightTest ON
Orders(Feight)
GO
SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE
GO
BEGIN TRAN
UPDATE dbo.Orders
SET Freight = Freight * 1.25
WHERE Freight BETWEEN 100 AND 200
現(xiàn)在，運(yùn)行SP_LOCK只顯示25個(gè)鎖。這在性能調(diào)試時(shí)經(jīng)常被忽視。即使你是用缺省的READ COMMITED隔離級(jí)別， 和創(chuàng)建索引相比，也是巨大的差別－－136個(gè)鎖和24個(gè)鎖。
鎖的跟蹤標(biāo)記(Locking Trace Flags)
有一些跟蹤標(biāo)記可以幫助我們調(diào)試鎖，發(fā)現(xiàn)死鎖問(wèn)題。
跟蹤標(biāo)記用于打開或者關(guān)閉SQL Server的行為方式。DBCC TRACEON命令來(lái)設(shè)置跟蹤標(biāo)記，如果希望SQL Server啟動(dòng) 時(shí)就打開跟蹤標(biāo)記，只要在啟動(dòng)參數(shù)加'-T'就可以了。
Node:1
KEY: 6:885578193:2 (010086470766) CleanCnt : 1 Mode : U Flags : 0x0
Grant List 0:
Owner: 0x42c0b2e0 Mode: U Flg: 0x0 Ref: 2 Life: 02000000 SPID: 53 ECID: 0
SPID: 53 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update Region
set RegionDescription = 'aa'
where RegionID = 1
Requested By
ResType: LockOwner Stype : 'OR' Mode: U SPID: 51 ECID: 0 Ec:(0x42E25568)
Value : 0x42c0b220 Cost: (0/0)
Node: 2
RID: 6:1:300:0 CleanCnt: 1 Mode: U Flags: 0x2
Grant List: 0
Owner: 0x42c0b320 Mode: S Flg: 0x0 Ref: 1 Life: 02000000 SPID: 51 ECID: 0
SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update Region
set RegionDescription = 'aa'
where RegionID = 1
Requested By
ResType: LockOwner Stype : 'OR' Mode: X SPID: 53 ECID: 0 Ec:(0x434A1568)
Value : 0x42c0b240 Cost: (0/0)
Victim Resource Owner:
ResType: LockOwner Stype: 'OR' Mode: X SPID: 53 ECID: 0 Ec(0x434A1568)
Value: 0x42c0b240 Cost: (0/0)
KEY: 表示死鎖中涉及到的索引信息。當(dāng)然你也可以用類似的參數(shù)來(lái)指定任何其它的鎖信息，例如page，RID，table等等。
ECID從master.dbo.sysprocesses得到，用于區(qū)分不同線程產(chǎn)生的鎖。Mode是死鎖的請(qǐng)求模式，例如S, X 或者 U。
字符串"6:885578193:2"表示：數(shù)據(jù)庫(kù)id為6，對(duì)象id為885578193，索引id為2。后面圓括號(hào)內(nèi)的數(shù)值是標(biāo)識(shí)鎖的哈希值，該值存儲(chǔ)在master.dbo.syslockinfo表的rsc_text列內(nèi)。遺憾的是，這個(gè)數(shù)值是單向哈希，也就是說(shuō)僅靠它是無(wú)法找出被 鎖住的記錄行。Spid是鎖的系統(tǒng)進(jìn)程ID。
Node 1 & 2顯示進(jìn)入了死鎖狀態(tài)。兩個(gè)鎖都處于等待隊(duì)列中，“Requested By:”說(shuō)明了這一點(diǎn)。
列鎖(Column Locks)
正如你所知道的，SQL Server 2000中最下的鎖是行鎖。SQL Server 并不直接提供列鎖。下面我們通過(guò)索引鎖來(lái)模擬 實(shí)現(xiàn)列級(jí)別的鎖。
列鎖通常被認(rèn)為在某些情況下會(huì)很慢，SQL Server也不例外。但是既然行鎖并不自動(dòng)鎖表的索引，因此你總是可以 在索引頁(yè)上使用那些被鎖住的數(shù)據(jù)。我們?cè)儆脭?shù)據(jù)庫(kù)Northwind的Region表來(lái)舉例。
表Region是堆表，有兩個(gè)字段：RegionDescription和RegionId。RegionId字段上有一個(gè)唯一性非聚集索引。
我們用一個(gè)簡(jiǎn)單的UPDATE操作，來(lái)更新RegionDescription字段的內(nèi)容。
USE Northwind
GO BEGIN TRAN
UPDATE Region
SET RegionDescription = 'South'
WHERE RegionDescription = 'Southern'
該查詢，SQL Server不會(huì)用到索引，因?yàn)樵赗egionDescription字段上并沒(méi)有索引。 因此SQL Server會(huì)掃描整表以找到需要更新的記錄行。一旦找到，那些記錄上的更新鎖就會(huì)升級(jí)到排它鎖。要確定這點(diǎn)，可以在另一個(gè)窗口運(yùn)行SP_LOCK即可。因此那些對(duì)應(yīng)數(shù)據(jù)上應(yīng)該有RID鎖。在運(yùn)行SP_LOCK的那個(gè)窗口中 輸入一個(gè)SELECT查詢：
SELECT * FROM Region
此時(shí)，我們不會(huì)進(jìn)入等待狀態(tài)。如果你象我，就會(huì)喜歡去看一下執(zhí)行計(jì)劃，因?yàn)閳?zhí)行計(jì)劃會(huì)告訴我們?yōu)槭裁创藭r(shí)我們不會(huì)進(jìn)入等待狀態(tài)。
正如上面看到的，SQL Server要完成上面的SELECT，需要選擇一個(gè)索引掃描以獲取數(shù)據(jù)。既然SLECT *可以用索引來(lái)完成， 因此它就沒(méi)有必要去讀取堆上的數(shù)據(jù)了。我們稱這種查詢?yōu)楦采w查詢(covering query)。
需要注意上面過(guò)程中的兩個(gè)準(zhǔn)則。第一個(gè)準(zhǔn)則是查詢中涉及到的數(shù)據(jù)必須是索引能照顧到的。記住如果表有一個(gè)聚集索引，所有的非聚集索引會(huì)有一個(gè)index字段，字段內(nèi)就是那個(gè)聚集索引字段的值
第二個(gè)準(zhǔn)則是早先的那個(gè)UPDATE操作不能改變?nèi)魏嗡饕淖侄蔚闹?。如果被改變?即索引值也被改變了)，它就會(huì) 升級(jí)到排它鎖，因此上面的技巧也失效了。
擴(kuò)展鎖能力的表格(Extended Lock Capability Table)
該表可以在聯(lián)機(jī)幫助和MSDN中找到，它標(biāo)識(shí)了那些鎖之間是相互兼容的。我這里列出一個(gè)更復(fù)雜的表格，希望 對(duì)大家有用：
結(jié)束語(yǔ)
我的確找到了難以掌握的更新意向鎖，對(duì)此進(jìn)行了大量的研究。鎖和鎖行為在聯(lián)機(jī)幫助中的資料很少，因此也增加了對(duì)此的研究。我在大量研究后寫下此文，希望能和你們分享相關(guān)的知識(shí)。
聯(lián)系客服
