使用SQL Server 6年多了，在下自認為對SQL Server還是比較熟悉的，而且我喜歡將SQL Server內(nèi)部的一些 東西搞清楚。

當(dāng)我在教一門SQL Server編程課程時，我注意到微軟MSDN中提到了鎖兼容性，在 MSDN列舉了一個兼容性關(guān)系的表格。

看過這張關(guān)系表格，我就想知道是否存在用于更新的意向鎖(Intent Update lock)？于是我開始閱讀相關(guān)的資料。 這篇文章也是我研究的結(jié)果。這篇文章的適用讀者是那些對隔離級別(isolation level)，意向鎖，死鎖和鎖粒度有所了解的。 如果你對這些領(lǐng)域還不了解，那么我建議你在讀這篇文章前，應(yīng)該先去了解和閱讀相關(guān)資料。

希望這篇文章能夠加深你對SQL Server鎖的理解，也許有些技巧還能夠在SQL Server編程中帶來幫助。

必須指出，即使不知道鎖是如何工作的，你也能長時間愉快地使用SQL Server，并且能創(chuàng)建高質(zhì)量的代碼和數(shù)據(jù)庫設(shè)計。 不過如果你象我那樣喜歡探究事情的內(nèi)部機理，或者你的工作需要你掌握一些性能方面的知識，我很樂意能教你一些有用的東西。

更新鎖(Update Locks)

死鎖的典型情況是SPID X鎖住了資源A，并在等待對資源B進行加鎖，而SPID Y鎖住了資源B，在等待對資源A加鎖，如此就 形成了死鎖。如果不理解，查詢 MSDN 或者相關(guān)的資料。

現(xiàn)在來假想更多情形下的死鎖。假設(shè)：SPID X在資源A上加了共享鎖，SPID Y也在資源A上加了共享鎖，因為是共享鎖， 所以這樣沒有問題?，F(xiàn)在X想把共享鎖升級為排它鎖(exclusive lock)以用于更新資源。X就必須等Y釋放共享鎖才能辦到， 當(dāng)X在等待時，Y也想做同樣的事情。這樣，X在等Y釋放，Y同時在等待X釋放，死鎖產(chǎn)生了。這種死鎖被稱為轉(zhuǎn)換死鎖(conversion deadlock)。

這種情況會很常見，為避免這種死鎖，就引入了更新鎖機制。更新鎖允許連接讀取資源，同時宣告它因為要編輯數(shù)據(jù)而要開始鎖住資源了。SQL Server并無法提前知道一個事務(wù)要把共享鎖轉(zhuǎn)換成排它鎖了，當(dāng)然有一個情況特殊，即只在一個SQL語句中 完成讀取然后更新的操作，比如說UPDATE XXX (SELECT YYY ....)這種類型。對于一般的SELECT語句，我們必須顯示地 使用UPDLOCK提示。

下面是代碼示例：

USE Northwind
GO
SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ
GO
BEGIN TRAN
SELECT *
FROM Orders (UPDLOCK)
WHERE OrderID = 10633

注意到我打開了事務(wù)，但并沒有關(guān)閉事務(wù)。這樣鎖就始終存在。如果另外一個連接視圖在相同的記錄上獲取更新鎖，就只有等待第一個事務(wù)結(jié)束后才行。這樣就可以演示，在相同資源上， 兩個更新鎖不相容的效果。

運行SP_LOCK，會顯示和上面的操作相關(guān)的記錄行，字段以及鎖的情況：

如我們預(yù)想那樣，主鍵OrderID被更新鎖鎖住了。圖中Resource列里面那個(89003da47679)的值，表示的是 主鍵10633的哈希值。SQL Server使用哈希表的方式來存儲鎖信息。

包含那行的記錄行，如我們所期望的那樣，被更新意向鎖鎖住了。在resource那列的數(shù)值(1:242)表示該數(shù)據(jù)頁面是 數(shù)據(jù)庫的第1個文件，頁面編號是#242。而意外的是，SQL Server添加了一個IX的表鎖。由于SQL Server不會在 表鎖上使用U/IU類型鎖，所以在表鎖級別上，只能看到X/IX類型鎖。

當(dāng)更新操作中帶有where語法，SQL Server會掃描整個表，并且/或者掃描索引，以決定那些記錄會被改變。 在從表/索引讀取信息之前，SQL Server首先把對象鎖住。既然SQL Server知道你提交的是更新事務(wù)，那么它 就會選擇更新鎖，而不是共享鎖。這樣做就是為了避免前面所提到的死鎖情況－－轉(zhuǎn)換死鎖(conversion deadlock)。

當(dāng)SQL Server確定那些記錄行需要改變后，在這些記錄上，它會把更新鎖進一步升級為排它鎖，如果是堆表(heap table)，那么鎖加 在RID(行標(biāo)識符)上，如果是聚集索引表，鎖加在主鍵上。這就意味著更新鎖會立刻升級為排它鎖，因此當(dāng)你執(zhí)行UPDATE 操作時，幾乎不可能看到這個更新過程。

不過，也有例外。如果SQL Server使用一個索引來定位記錄行，它就會鎖住索引頁，在索引上加的就是更新鎖。 如果不改變?nèi)魏伟谠撍饕械臄?shù)據(jù)列，更新鎖不會升級為排它鎖。下面是一個例子：

BEGIN TRAN
UPDATE Region
SET RegionDescription = 'South'
WHERE RegionID = 4

Region是一個堆表，在RegionId上只有非聚集唯一索引主鍵。因此完成上面查詢時，SQL Server在RegionId上掃描索引， 鎖住索引頁和索引鍵。當(dāng)發(fā)現(xiàn)要改變得記錄行后，因為更新查詢并不改變RegionId的值，因此不會升級到排它鎖。 運行SP_LOCK后可以得到以下信息：

我們看到，在RID上有一個IX鎖。該鎖位于RegionId索引上。還可以看到在表上有一個IX鎖，RID上有一個X鎖。 KEY鎖在RegionId索引上，證據(jù)可以從Indid列上可以得到。在索引上還有一個更新鎖，這是更新鎖激活的一個瞬間之一。

當(dāng)查詢結(jié)束后，仍然存在兩個頁面鎖 –- 一個在索引頁 (1:306)上, 另一個在堆(heap) (1:300)上。這是因為 堆的Indid(Index id)為0。

鎖粒度(Lock Granularity)

SQL Server有幾種鎖類型，每種類型都可以選擇不同的粒度。

如果運行SP_LOCK，或者查看企業(yè)管理器中"當(dāng)前激活"信息，就可以看到至少四，五中不同的鎖類型。下面簡單回顧一下這些類型：

• Database (DB): 這是一種會話(session)鎖。例如，它不涉及任何事務(wù)，僅僅是一個用戶和數(shù)據(jù)庫之間的連接。 這樣就可以防止有用戶連接到數(shù)據(jù)庫時，該數(shù)據(jù)庫被卸載了。值得注意的是，雖然SQL Server的master和tempdb是 不能卸載的，但是在這兩個數(shù)據(jù)庫上是沒有DB鎖的。
• Table (TAB): 這是SQL Server中最粗略的邏輯鎖。在表級別上經(jīng)常加的是意向鎖 (覺得意向鎖不安全嗎？ 這里 有更詳細的信息。)
• Extent (EXT): 這種鎖一般發(fā)生在SQL Server創(chuàng)建新表，或者已有表容量增加時，而并非用于鎖住記錄行。因此 當(dāng)文件容量變化時，經(jīng)常會看到這種鎖的存在。
• Page (PAG): 當(dāng)SQL Server要同時鎖住很多記錄行，而可用的鎖槽(slot)較少時，頁面鎖將會被采用。頁面級別 上的意向鎖更常見。目前為止的SQL Server版本（包括SQL Server 6.5在內(nèi))，這種類型的鎖是最佳性能的。
• Key (KEY): 和RID鎖一樣，可能是SQL Server中最佳級別的鎖。KEY鎖用于索引上，而RID鎖用在堆表上。 (譯者注：行鎖包括KEY鎖和RID鎖，從鎖的級別上 考慮對并發(fā)是最佳的，但是從性能考慮，行鎖會大量占用資源，相關(guān)資料可見前面的blog)。

在研究SQL Server 2000的鎖行為中，我認為SQL Server在大多數(shù)情況下，和速度相比，更看重并發(fā)性能。 較高的并發(fā)性能，意味著很多用戶能同時對數(shù)據(jù)庫進行操作。所以鎖盡可能的小，不必要地鎖住別人也需要的數(shù)據(jù)的可能性就越小；另一方面，如果使用較大的鎖，將獲取更高的速度。（譯者注：這句話的理解應(yīng)該以平衡性能的前提下考查。)

當(dāng)SQL Server 2000發(fā)現(xiàn)操作將鎖住越來越多的記錄行時，就會提高鎖的級別。 例如SQL Server 2000會升級到表鎖，丟掉單獨的pages/keys/RIDs級別鎖。注意：提高鎖的級別肯定是升級到表鎖，而不會將RID/KEY鎖升級到頁面鎖。

那么SQL Server2000什么時候升級鎖呢？它無法知道你將鎖住的表的比例，因此它唯一在意的就是產(chǎn)生的鎖的數(shù)量。 當(dāng)鎖使用了較高比例的內(nèi)存時，SQL Server2000就開始升級所有連接事務(wù)上的鎖了。當(dāng)鎖槽使用將盡時，也會開始 升級工作。你可以用SP_CONFIGURE來配置SQL Server可用的鎖槽數(shù)，例如降低該數(shù)值，從而來觀察鎖的升級情況。

SQL Server會盡可能使用較小的鎖來保證較高的并發(fā)性能。但是有時候SQL Server并不知道數(shù)據(jù)將會發(fā)生怎么樣的改變， 從而它會按照它的規(guī)則來改變鎖的級別，而這種改變并非你想要的。例如一個很大的查找表(lookup table)，僅僅是讀取 數(shù)據(jù)。那么你可以直接用一個表鎖來替代很多KEY鎖。使用的方法是使用鎖提示或者SP_INDEXOPTION。

鎖提示很普通，在 聯(lián)機幫助(BOL) 有大量關(guān)于此的文檔，因此在本文就不重復(fù)介紹了。系統(tǒng)存儲過程SP_INDEXOPTION是強迫SQL Server使用特定大小的鎖 的好辦法。

使用SP_INDEXOPTION，你可以關(guān)閉行或者頁面級別的鎖。也就是說，你可以不需要鎖提示－－所有表或者索引上的鎖都是你指定的大小。即使BOL宣稱，該存儲過程用于索引上的鎖粒度，其實它也能用戶堆上。一個好的實現(xiàn) 方法是使用表名來替代@IndexNamePattern變量，這種方法很少人知道。

關(guān)于這方面的研究并沒有結(jié)束。如果你使用了兩個更高隔離級別中的一個，且在檢索規(guī)則中沒有任何可用的索引，那么 SQL Server即使不鎖住整個表，也會盡可能多的記錄來滿足你的查詢。下面是一個例子：

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

在另一個窗口運行SP_LOCK。在我這里運行的時候，我看到該連接上有853個鎖。數(shù)據(jù)庫Northwind中的Orders表上 有830行，每行上都有一個鎖?；貪L該UPDATE事務(wù)，然后進行改寫，在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)在，運行SP_LOCK只顯示25個鎖。這在性能調(diào)試時經(jīng)常被忽視。即使你是用缺省的READ COMMITED隔離級別， 和創(chuàng)建索引相比，也是巨大的差別－－136個鎖和24個鎖。

鎖的跟蹤標(biāo)記(Locking Trace Flags)

有一些跟蹤標(biāo)記可以幫助我們調(diào)試鎖，發(fā)現(xiàn)死鎖問題。

跟蹤標(biāo)記用于打開或者關(guān)閉SQL Server的行為方式。DBCC TRACEON命令來設(shè)置跟蹤標(biāo)記，如果希望SQL Server啟動 時就打開跟蹤標(biāo)記，只要在啟動參數(shù)加'-T'就可以了。

• 1200: 顯示所有連接的所有的鎖。這個選項將會有巨量的輸出信息，因此我建議只在可控制的環(huán)境下使用，例如在 同一時刻只有一個連接在工作。
• 1204: 輸入和死鎖相關(guān)的信息。下面是這種信息的一個示例：

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ù)來指定任何其它的鎖信息，例如page，RID，table等等。

ECID從master.dbo.sysprocesses得到，用于區(qū)分不同線程產(chǎn)生的鎖。Mode是死鎖的請求模式，例如S, X 或者 U。

字符串"6:885578193:2"表示：數(shù)據(jù)庫id為6，對象id為885578193，索引id為2。后面圓括號內(nèi)的數(shù)值是標(biāo)識鎖的哈希值，該值存儲在master.dbo.syslockinfo表的rsc_text列內(nèi)。遺憾的是，這個數(shù)值是單向哈希，也就是說僅靠它是無法找出被 鎖住的記錄行。Spid是鎖的系統(tǒng)進程ID。

Node 1 & 2顯示進入了死鎖狀態(tài)。兩個鎖都處于等待隊列中，“Requested By:”說明了這一點。

• 1205: 打印鎖管理工作的相關(guān)信息。每次死鎖搜索工作初始化后，跟蹤標(biāo)記就通知鎖管理打印出搜索的信息。該 選項工作的前提示跟蹤標(biāo)記1024必須給出。
• 1211: 禁止所有鎖的升級。這個標(biāo)記通知鎖管理不要升級任何鎖，即使鎖資源被用完也一樣。

列鎖(Column Locks)

正如你所知道的，SQL Server 2000中最下的鎖是行鎖。SQL Server 并不直接提供列鎖。下面我們通過索引鎖來模擬 實現(xiàn)列級別的鎖。

列鎖通常被認為在某些情況下會很慢，SQL Server也不例外。但是既然行鎖并不自動鎖表的索引，因此你總是可以 在索引頁上使用那些被鎖住的數(shù)據(jù)。我們再用數(shù)據(jù)庫Northwind的Region表來舉例。

表Region是堆表，有兩個字段：RegionDescription和RegionId。RegionId字段上有一個唯一性非聚集索引。

我們用一個簡單的UPDATE操作，來更新RegionDescription字段的內(nèi)容。

USE Northwind
GO BEGIN TRAN
UPDATE Region
SET RegionDescription = 'South'
WHERE RegionDescription = 'Southern'

該查詢，SQL Server不會用到索引，因為在RegionDescription字段上并沒有索引。 因此SQL Server會掃描整表以找到需要更新的記錄行。一旦找到，那些記錄上的更新鎖就會升級到排它鎖。要確定這點，可以在另一個窗口運行SP_LOCK即可。因此那些對應(yīng)數(shù)據(jù)上應(yīng)該有RID鎖。在運行SP_LOCK的那個窗口中 輸入一個SELECT查詢：

SELECT * FROM Region

此時，我們不會進入等待狀態(tài)。如果你象我，就會喜歡去看一下執(zhí)行計劃，因為執(zhí)行計劃會告訴我們?yōu)槭裁创藭r我們不會進入等待狀態(tài)。

正如上面看到的，SQL Server要完成上面的SELECT，需要選擇一個索引掃描以獲取數(shù)據(jù)。既然SLECT *可以用索引來完成， 因此它就沒有必要去讀取堆上的數(shù)據(jù)了。我們稱這種查詢?yōu)楦采w查詢(covering query)。

需要注意上面過程中的兩個準(zhǔn)則。第一個準(zhǔn)則是查詢中涉及到的數(shù)據(jù)必須是索引能照顧到的。記住如果表有一個聚集索引，所有的非聚集索引會有一個index字段，字段內(nèi)就是那個聚集索引字段的值

第二個準(zhǔn)則是早先的那個UPDATE操作不能改變?nèi)魏嗡饕淖侄蔚闹?。如果被改變?即索引值也被改變了)，它就會 升級到排它鎖，因此上面的技巧也失效了。

擴展鎖能力的表格(Extended Lock Capability Table)

該表可以在聯(lián)機幫助和MSDN中找到，它標(biāo)識了那些鎖之間是相互兼容的。我這里列出一個更復(fù)雜的表格，希望 對大家有用：

結(jié)束語

我的確找到了難以掌握的更新意向鎖，對此進行了大量的研究。鎖和鎖行為在聯(lián)機幫助中的資料很少，因此也增加了對此的研究。我在大量研究后寫下此文，希望能和你們分享相關(guān)的知識。