SQL2005中的事務與鎖定（五）
------------------------------------------------------------------------
-- Author : HappyFlyStone
-- Date : 2009-10-05 14:00:00
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
-- 轉載請注明出處，更多請關注：http://blog.csdn.net/happyflystone
-- 關鍵字：鎖定 行版本控制 Lock Hint HOLDLOCK
------------------------------------------------------------------------
在生産交易過程中多個使用者同時通路資料是不可以避免的，通過不同的隔離等級對資源與資料進行各種類型的鎖定保護并在适當時候釋放保證交易的正确運作，使得交易完整并保證資料的一緻性。不管是鎖定還是行版本控制器都決定着商業邏輯的流暢、事務的完整、資料的一緻。是以我們要根據實際情況進行部署，在并發性性能與資源管理成本之間找到平衡點，怎樣才能找到這個平衡點呢，那我們就得對SQLSERVER如何管理資源與鎖有一個了解，SQLSERVER不但管理鎖定，還要管理鎖定模式之間的相容性或更新鎖定及解決死鎖問題。通過SQL SERVER強大的、細緻的鎖定機制，使得并發性能得到最大程度的發揮，但是使用盡可能少的系統資源也是我們最希望的。
SQLSERVER本身有兩種鎖定體系：一種是對共享資料的鎖定，這種鎖定就是我們大部時間讨論的鎖定；一種是對内部資料結構及處理索引，這是一種稱為闩鎖的輕量級鎖，比第一種鎖定少耗資源，在sys.dm_tran_locks中是看不到這種鎖的資訊。我們在資料分頁上放置實體記錄或壓縮、折分、轉移分頁資料時，這種鎖就會發生了。我們在前面一直在說資料的邏輯一緻性，那這種邏輯上的一緻性就是通過鎖定來控制的，而我們新提到的闩是保證實體的一緻性(這種闩是系統内部使用是以我們不重點讨論了)。
并發通路資料時，SQL Server 2005使用下列機制確定事務完整并維護資料的一緻性：
l 鎖定
每個事務對所依賴的資源（如行、頁或表）請求不同類型的鎖。鎖可以阻止其他事務以某種可能會導緻事務請求鎖出錯的方式修改資源。當事務不再依賴鎖定的資源時，它将釋放鎖。
l 行版本控制
當啟用了基于行版本控制的隔離級别時，資料庫引擎 将維護修改的每一行的版本。應用程式可以指定事務使用行版本檢視事務或查詢開始時存在的資料，而不是使用鎖保護所有讀取。通過使用行版本控制，讀取操作阻止其他事務的可能性将大大降低。
鎖定和行版本控制可以防止使用者讀取未送出的資料，還可以防止多個使用者嘗試同時更改同一資料。如果不進行鎖定或行版本控制，對資料執行的查詢可能會傳回資料庫中尚未送出的資料，進而産生意外的結果。
最後說一下鎖的粒度與并發性能是沖突的，但是對管理鎖定的成本卻是有利的，粒度越大并發性能下降，粒度越小管理鎖定成本越大。用圖示例一下：
六、鎖定
1、鎖粒度和可鎖定資源
SQL Server2005 具有多粒度鎖定，允許一個事務鎖定不同類型的資源。為了盡量減少鎖定的開銷，資料庫引擎自動将資源鎖定在适合任務的級别。鎖定在較小的粒度（例如行）可以提高并發度，但開銷較高，因為如果鎖定了許多行，則需要持有更多的鎖。鎖定在較大的粒度（例如表）會降低了并發度，因為鎖定整個表限制了其他事務對表中任意部分的通路，但其開銷較低，因為需要維護的鎖較少。
SQL SERVER可以鎖定表、分頁、行級、索引鍵或範圍。在這我提醒大家一下，對于聚集索引的表，因為資料行就是索引的葉級，是以鎖定是鍵鎖完成而不是行鎖。
資料庫引擎通常必須擷取多粒度級别上的鎖才能完整地保護資源。這組多粒度級别上的鎖稱為鎖層次結構。例如，為了完整地保護對索引的讀取，資料庫引擎執行個體可能必須擷取行上的共享鎖以及頁和表上的意向共享鎖。
下表列出了資料庫引擎可以鎖定的資源：
查詢一：
SELECT *
FROM MASTER..SPT_VALUES WHERE TYPE = 'LR'
備注：
RID RID 鎖定堆中行的行辨別符
KEY KEY 序列化事務中的鍵範圍行鎖
PAG PAGE 資料或索引頁面，8K為機關
EXT EXTENT 資料或索引頁面，連續的8*page
HBT HOBT 堆或B樹，保護索引或堆表頁堆的鎖
TAB TABLE 整個表，包括資料及索引
FIL FILE 資料庫檔案
APP APPLICATION 應用程式資源
MD METADATA 中繼資料
AU ALLOCATION_UNIT 配置設定單元
DB DATABASE 資料庫
注：SPT_VALUES這個大家不陌生吧，好多人用它生成一個連續的ID号的啦，當時也有人問這個表的用途，現在發現它的作用了吧。下面我們還會使用到。
2、鎖定模式
我們在前提面前到的共享鎖定、更新鎖定、排它鎖定，這是為了配合前面的事務而提及的，那麼SQL SERVER2005一共有多少鎖定模式呢？我們通過一個簡單的查詢來清單：
查詢：
SELECT *
FROM MASTER..SPT_VALUES WHERE [TYPE] = 'L'
我們可以看到一共有22種鎖定模式 ，我簡單的對上述[NAME]進行簡單的枚舉：
l S --- 共享鎖定(Shared)
l U --- 更新鎖定(Update)
l X --- 排它鎖定(Exclusive)
l I --- 意向鎖定(Intent)
l Sch --- 架構鎖定(Schema)
l BU --- 大量更新(Bulk Update)
l RANGE --- 鍵範圍(Key-Range)
l 其它是在上述鎖定的變種組合，比如IS --- 意向共享鎖定
其實對這些鎖定模式沒什麼介紹，大家可以參考聯機幫助：通路和更改資料庫資料 -> 鎖定和行版本控制 -> 資料庫引擎中的鎖定。其實這些鎖定模式在前一篇基本都有出現，大家可以在看下面的定義再回頭看看前一篇的相關内容。下面我就簡單的說說：
共享鎖（S 鎖）
當我們查詢(select)資料時SQL SERVER2005會嘗試在資料上申請共享鎖定，但是前提是在目前的資料上不存在與共享鎖定互斥的鎖定。資源上存在共享鎖時，任何其他事務都不能修改資料但是可以讀取資料。讀取操作一完成，就立即釋放資源上的共享鎖，除非将事務隔離級别設定為可重複讀或更進階别，或者在事務持續時間内用鎖定提示(HOLDLOCK)保留共享鎖。
更新鎖（U 鎖）
更新新是一種介于共享鎖與排它鎖之間的鎖定，是一種中繼鎖定，像一個中間閘門，把從共享鎖定轉為排它鎖的請求進行排隊，有效的防止常見的死鎖。在可重複讀或可序列化事務中，一個事務讀取資料 [擷取資源（頁或行）的共享鎖（S 鎖）]，然後修改資料 [此操作要求鎖轉換為排他鎖（X 鎖）]。如果兩個事務獲得了資源上的共享模式鎖，然後試圖同時更新資料，則一個事務嘗試将鎖轉換為排他鎖（X 鎖）。共享模式到排他鎖的轉換必須等待一段時間，因為一個事務的排他鎖與其他事務的共享模式鎖不相容；發生鎖等待。第二個事務試圖擷取排他鎖（X 鎖）以進行更新。由于兩個事務都要轉換為排他鎖（X 鎖），并且每個事務都等待另一個事務釋放共享模式鎖，是以發生死鎖。而有了更新鎖則可避免這種潛在的死鎖問題，在查找到要更新的資料後SQL SERVER首先給資料設定更新鎖定，因為共享鎖定與更新鎖定不互斥，在其它事務設定共享鎖定時依然可以設定更新鎖定，繼而因更新鎖定斥的，如果其它要修改資料的事務必須等待。如果事務修改資源，則更新鎖轉換為排他鎖（X 鎖）。
排他鎖（X 鎖）
排他鎖可以防止并發事務對資源進行通路。使用排他鎖（X 鎖）時，任何其他事務都無法修改資料；僅在使用 NOLOCK 提示或未送出讀隔離級别時才會進行讀取操作。
資料修改語句（如 INSERT、UPDATE 和 DELETE）合并了修改和讀取操作。語句在執行所需的修改操作之前首先執行讀取操作以擷取資料。是以，資料修改語句通常請求共享鎖和排他鎖。例如，UPDATE 語句可能根據與一個表的聯接修改另一個表中的行。在此情況下，除了請求更新行上的排他鎖之外，UPDATE 語句還将請求在聯接表中讀取的行上的共享鎖。
排他鎖定随事務結束而釋放。
意向鎖（I鎖）
資料庫引擎使用意向鎖來保護共享鎖（S 鎖）或排他鎖（X 鎖）放置在鎖層次結構的底層資源上。意向鎖之是以命名為意向鎖，是因為在較低級别鎖前可擷取它們，是以會通知意向将鎖放置在較低級别上。
意向鎖有兩種用途：
l 防止其他事務以會使較低級别的鎖無效的方式修改較進階别資源。
l 提高資料庫引擎 在較高的粒度級别檢測鎖沖突的效率。
例如，在該表的頁或行上請求共享鎖（S 鎖）之前，在表級請求共享意向鎖。在表級設定意向鎖可防止另一個事務随後在包含那一頁的表上擷取排他鎖（X 鎖）。意向鎖可以提高性能，因為資料庫引擎僅在表級檢查意向鎖來确定事務是否可以安全地擷取該表上的鎖。而不需要檢查表中的每行或每頁上的鎖以确定事務是否可以鎖定整個表。
意向鎖包括意向共享 (IS)、意向排他 (IX)、意向排他共享 (SIX)、意向更新 (IU)、共享意向更新 (SIU ，S和 IU 鎖的組合)、更新意向排他 (UIX，U 鎖和 IX 鎖的組合)。
在這兒的SIX，SIU，UIX我們可以了解成一種轉換鎖定，并不是由SQLSERVER直接申請的，是由一種模式向另一種模式轉換時中間狀态。比如說SIX表示一種正持有共享鎖定的程序正在企圖申請意向排它鎖定，或是這樣了解一個持有共享鎖定的資源中有部分分頁或行被另一個程序的排它鎖定鎖定了。其它同理可以了解。
為了更好的說明一點， 大家先看一個圖：
這是我在TA表上加Where條件的一個更新動作，然後通過我以前寫的一個工具：sp_us_lockinfo檢視鎖的資訊，其實我的update隻是影響一個行記錄，但是我們發現有三個鎖存在，隻要目前事務不結束，其它事物對這個表申請不管是頁面的鎖定還是表級的鎖定一定會與現在的表或頁意向鎖沖突，進而發生阻塞，而且我們在前面的隔離等級的執行個體中也有例子，你會發現它的請求狀态是WAIT 而不是GRANT。
架構鎖（架構修改鎖 Sch-M 鎖、架構穩定性鎖Sch-S 鎖）
執行表的資料定義語言 (DDL) 操作（例如添加列或删除表）時使用架構修改鎖。在架構修改鎖起作用的期間，會防止對表的并發通路。這意味着在釋放架構修改鎖（Sch-M 鎖）之前，該鎖之外的所有操作都将被阻止。
當編譯查詢時，使用架構穩定性鎖。架構穩定性鎖不阻塞任何事務鎖，包括排他鎖（X 鎖）。是以在編譯查詢時，其他事務 [包括在表上有排他鎖（X 鎖）的事務] 都能繼續運作。但不能在表上執行 DDL 操作。
大容量更新鎖 （ BU 鎖）
當将資料大容量複制到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 設定了 table lock on bulk 表選項時，将使用大容量更新鎖。大容量更新鎖允許多個線程将資料并發地大容量加載到同一表，同時防止其他不進行大容量加載資料的程序通路該表。
鍵鎖、鍵範圍鎖（Key-range鎖）
在SQL SERVER2005有兩種類型鍵鎖：鍵鎖及鍵範圍鎖。采用哪種類型的鍵鎖取決于隔離級别。對于已送出讀、可重複讀、快照隔離時SQLSERVER鎖定實際的索引鍵(如果是堆表除了實際非聚集索引上的鍵鎖同時有實際行上的行鎖)，如果是可串行化隔離時就可以看到鍵範圍鎖。在早期的版本中我們實驗可以看到SQLSERVER是通過分頁鎖定或表鎖來實作的，也許鍵範圍鎖不是最完美的，但是我們應該看到它比分頁或表鎖定所鎖定的範圍要小得多，在保證不出現幻影的前提下鍵範圍鎖比以前版本采用鎖定提供了更高的并發性能。
鍵範圍鎖放置在索引上，指定開始鍵值和結束鍵值。此鎖将阻止任何要插入、更新或删除任何帶有該範圍内的鍵值的行的嘗試，因為這些操作會首先擷取索引上的鎖。鍵範圍鎖包括按範圍-行格式指定的範圍元件和行元件，是一種組合鎖模式(Range範圍-索引項的鎖模式)。比如：RangeI-N ，RangeI 表示插入範圍，N(NULL) 表示空資源，它表示在索引中插入新鍵之前測試範圍。
在SELECT * FROM MASTER..SPT_VALUES WHERE [TYPE] = 'L'查詢結果的最後9條個就是鍵範圍鎖。這種鎖定因持續時間比較短一般在sys.dm_tran_locks中很難見到。比如RangeI_N這個鎖定，是在鍵範圍内插入記錄時獲得的，在鍵範圍内找到位置立即更新為X鎖定，這個過程很短，我們在sys.dm_tran_locks中很難找到它的蹤影，不過我們是可以模拟出來的，下面我們來模拟一下：
查詢一：
DROP TABLE TB
GO
CREATE TABLE TB (ID INT primary key, COL VARCHAR(16))
GO
INSERT INTO TB SELECT 1,'A'
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM TB WHERE id BETWEEN 1 AND 5 --OLD資料
--COMMIT TRAN --Don't commit
SELECT @@SPID
查詢二：
INSERT TB SELECT 2,'E'
查詢三：
exec sp_us_lockinfo
在使用可序列化事務隔離級别時，對于 Transact-SQL 語句讀取的記錄集，鍵範圍鎖在索引獲上取鎖定阻止一切嘗試在包含索引鍵值落入範圍内增删改的資料行，可以隐式保護該記錄集中包含的行範圍。鍵範圍鎖可防止幻讀。通過保護行之間鍵的範圍，它還防止對事務通路的記錄集進行幻像插入或删除。
例如我們在上面有例子的可串行化隔離級别下，選擇索引鍵值在’1-5’的資料時，SQL SERVER 對落在1-5之間鍵值設定鍵範圍鎖定，避免包含在這個範圍内的鍵值的插入及這個範圍内鍵值的删除及更新。
最後強調一下鍵範圍鍵産生的條件：
1、 務隔離級别必須設定為 SERIALIZABLE。
2、詢處理器必須使用索引來實作範圍篩選謂詞。例如，SELEC中的 WHERE 子句。
3、鎖相容性矩陣
鎖相容性控制多個事務能否同時擷取同一資源上的鎖。如果資源已被另一事務鎖定，則僅當請求鎖的模式與現有鎖的模式相相容時，才會授予新的鎖請求。如果請求鎖的模式與現有鎖的模式不相容，則請求新鎖的事務将等待釋放現有鎖或等待鎖逾時間隔過期。
請大家繼續關注我的blog: http://blog.csdn.net/happyflystone