我的家園

一. 為什么要引入鎖

?

當(dāng)多個用戶同時對數(shù)據(jù)庫的并發(fā)操作時會帶來以下數(shù)據(jù)不一致的問題:

?

◆丟失更新

A,B兩個用戶讀同一數(shù)據(jù)并進行修改,其中一個用戶的修改結(jié)果破壞了另一個修改的結(jié)果,比如訂票系統(tǒng)

?

◆臟讀

A用戶修改了數(shù)據(jù),隨后B用戶又讀出該數(shù)據(jù),但A用戶因為某些原因取消了對數(shù)據(jù)的修改,數(shù)據(jù)恢復(fù)原值,此時B得到的數(shù)據(jù)就與數(shù)據(jù)庫內(nèi)的數(shù)據(jù)產(chǎn)生了不一致

?

◆不可重復(fù)讀

A用戶讀取數(shù)據(jù),隨后B用戶讀出該數(shù)據(jù)并修改,此時A用戶再讀取數(shù)據(jù)時發(fā)現(xiàn)前后兩次的值不一致

?

并發(fā)控制的主要方法是封鎖,鎖就是在一段時間內(nèi)禁止用戶做某些操作以避免產(chǎn)生數(shù)據(jù)不一致

?

二 鎖的分類

?

◆鎖的類別有兩種分法：

?

1. 從數(shù)據(jù)庫系統(tǒng)的角度來看：分為獨占鎖（即排它鎖），共享鎖和更新鎖

?

MS-SQL Server 使用以下資源鎖模式。

?

鎖模式 描述

共享 (S) 用于不更改或不更新數(shù)據(jù)的操作（只讀操作），如 SELECT 語句。

更新 (U) 用于可更新的資源中。防止當(dāng)多個會話在讀取、鎖定以及隨后可能進行的資源更新時發(fā)生常見形式的死鎖。

排它 (X) 用于數(shù)據(jù)修改操作，例如 INSERT、UPDATE 或 DELETE。確保不會同時同一資源進行多重更新。

意向鎖 用于建立鎖的層次結(jié)構(gòu)。意向鎖的類型為：意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。

架構(gòu)鎖 在執(zhí)行依賴于表架構(gòu)的操作時使用。架構(gòu)鎖的類型為：架構(gòu)修改 (Sch-M) 和架構(gòu)穩(wěn)定性 (Sch-S)。

大容量更新 (BU) 向表中大容量復(fù)制數(shù)據(jù)并指定了 TABLOCK 提示時使用。

?

◆共享鎖

共享 (S) 鎖允許并發(fā)事務(wù)讀取 (SELECT) 一個資源。資源上存在共享 (S) 鎖時，任何其它事務(wù)都不能修改數(shù)據(jù)。一旦已經(jīng)讀取數(shù)據(jù)，便立即釋放資源上的共享 (S) 鎖，除非將事務(wù)隔離級別設(shè)置為可重復(fù)讀或更高級別，或者在事務(wù)生存周期內(nèi)用鎖定提示保留共享 (S) 鎖。

?

◆更新鎖

更新 (U) 鎖可以防止通常形式的死鎖。一般更新模式由一個事務(wù)組成，此事務(wù)讀取記錄，獲取資源（頁或行）的共享 (S) 鎖，然后修改行，此操作要求鎖轉(zhuǎn)換為排它 (X) 鎖。如果兩個事務(wù)獲得了資源上的共享模式鎖，然后試圖同時更新數(shù)據(jù)，則一個事務(wù)嘗試將鎖轉(zhuǎn)換為排它 (X) 鎖。共享模式到排它鎖的轉(zhuǎn)換必須等待一段時間，因為一個事務(wù)的排它鎖與其它事務(wù)的共享模式鎖不兼容；發(fā)生鎖等待。第二個事務(wù)試圖獲取排它 (X) 鎖以進行更新。由于兩個事務(wù)都要轉(zhuǎn)換為排它 (X) 鎖，并且每個事務(wù)都等待另一個事務(wù)釋放共享模式鎖，因此發(fā)生死鎖。

?

若要避免這種潛在的死鎖問題，請使用更新 (U) 鎖。一次只有一個事務(wù)可以獲得資源的更新 (U) 鎖。如果事務(wù)修改資源，則更新 (U) 鎖轉(zhuǎn)換為排它 (X) 鎖。否則，鎖轉(zhuǎn)換為共享鎖。

?

◆排它鎖

排它 (X) 鎖可以防止并發(fā)事務(wù)對資源進行訪問。其它事務(wù)不能讀取或修改排它 (X) 鎖鎖定的數(shù)據(jù)。

意向鎖

意向鎖表示 SQL Server 需要在層次結(jié)構(gòu)中的某些底層資源上獲取共享 (S) 鎖或排它 (X) 鎖。例如，放置在表級的共享意向鎖表示事務(wù)打算在表中的頁或行上放置共享 (S) 鎖。在表級設(shè)置意向鎖可防止另一個事務(wù)隨后在包含那一頁的表上獲取排它 (X) 鎖。意向鎖可以提高性能，因為 SQL Server 僅在表級檢查意向鎖來確定事務(wù)是否可以安全地獲取該表上的鎖。而無須檢查表中的每行或每頁上的鎖以確定事務(wù)是否可以鎖定整個表。

?

意向鎖包括意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。

?

鎖模式 描述

意向共享 (IS) 通過在各資源上放置 S 鎖，表明事務(wù)的意向是讀取層次結(jié)構(gòu)中的部分（而不是全部）底層資源。

意向排它 (IX) 通過在各資源上放置 X 鎖，表明事務(wù)的意向是修改層次結(jié)構(gòu)中的部分（而不是全部）底層資源。IX 是 IS 的超集。

與意向排它共享 (SIX) 通過在各資源上放置 IX 鎖，表明事務(wù)的意向是讀取層次結(jié)構(gòu)中的全部底層資源并修改部分（而不是全部）底層資源。允許頂層資源上的并發(fā) IS 鎖。例如，表的 SIX 鎖在表上放置一個 SIX 鎖（允許并發(fā) IS 鎖），在當(dāng)前所修改頁上放置 IX 鎖（在已修改行上放置 X 鎖）。雖然每個資源在一段時間內(nèi)只能有一個 SIX 鎖，以防止其它事務(wù)對資源進行更新，但是其它事務(wù)可以通過獲取表級的 IS 鎖來讀取層次結(jié)構(gòu)中的底層資源。

?

◆獨占鎖：

只允許進行鎖定操作的程序使用，其他任何對他的操作均不會被接受。執(zhí)行數(shù)據(jù)更新命令時，SQL Server會自動使用獨占鎖。當(dāng)對象上有其他鎖存在時，無法對其加獨占鎖。

共享鎖：共享鎖鎖定的資源可以被其他用戶讀取，但其他用戶無法修改它，在執(zhí)行Select時，SQL Server會對對象加共享鎖。

◆更新鎖：

當(dāng)SQL Server準(zhǔn)備更新數(shù)據(jù)時，它首先對數(shù)據(jù)對象作更新鎖鎖定，這樣數(shù)據(jù)將不能被修改，但可以讀取。等到SQL Server確定要進行更新數(shù)據(jù)操作時，他會自動將更新鎖換為獨占鎖，當(dāng)對象上有其他鎖存在時，無法對其加更新鎖。

?

2. 從程序員的角度看：分為樂觀鎖和悲觀鎖。

樂觀鎖：完全依靠數(shù)據(jù)庫來管理鎖的工作。

悲觀鎖：程序員自己管理數(shù)據(jù)或?qū)ο笊系逆i處理。

?

MS-SQLSERVER 使用鎖在多個同時在數(shù)據(jù)庫內(nèi)執(zhí)行修改的用戶間實現(xiàn)悲觀并發(fā)控制

?

三 鎖的粒度

?

?

鎖粒度是被封鎖目標(biāo)的大小,封鎖粒度小則并發(fā)性高,但開銷大,封鎖粒度大則并發(fā)性低但開銷小

?

SQL Server支持的鎖粒度可以分為為行、頁、鍵、鍵范圍、索引、表或數(shù)據(jù)庫獲取鎖

?

資源 描述

RID 行標(biāo)識符。用于單獨鎖定表中的一行。

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

頁 8 千字節(jié) (KB) 的數(shù)據(jù)頁或索引頁。

擴展盤區(qū) 相鄰的八個數(shù)據(jù)頁或索引頁構(gòu)成的一組。

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

DB 數(shù)據(jù)庫。

?

四 鎖定時間的長短

?

鎖保持的時間長度為保護所請求級別上的資源所需的時間長度。

?

用于保護讀取操作的共享鎖的保持時間取決于事務(wù)隔離級別。采用 READ COMMITTED 的默認(rèn)事務(wù)隔離級別時，只在讀取頁的期間內(nèi)控制共享鎖。在掃描中，直到在掃描內(nèi)的下一頁上獲取鎖時才釋放鎖。如果指定 HOLDLOCK 提示或者將事務(wù)隔離級別設(shè)置為 REPEATABLE READ 或 SERIALIZABLE，則直到事務(wù)結(jié)束才釋放鎖。

?

根據(jù)為游標(biāo)設(shè)置的并發(fā)選項，游標(biāo)可以獲取共享模式的滾動鎖以保護提取。當(dāng)需要滾動鎖時，直到下一次提取或關(guān)閉游標(biāo)（以先發(fā)生者為準(zhǔn)）時才釋放滾動鎖。但是，如果指定 HOLDLOCK，則直到事務(wù)結(jié)束才釋放滾動鎖。

?

用于保護更新的排它鎖將直到事務(wù)結(jié)束才釋放。

如果一個連接試圖獲取一個鎖，而該鎖與另一個連接所控制的鎖沖突，則試圖獲取鎖的連接將一直阻塞到：

?

將沖突鎖釋放而且連接獲取了所請求的鎖。

?

連接的超時間隔已到期。默認(rèn)情況下沒有超時間隔，但是一些應(yīng)用程序設(shè)置超時間隔以防止無限期等待

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT * FROM table ROWLOCK WHERE id = 1

SELECT * FROM table WITH (HOLDLOCK)

sybase: 
update 表 set col1=col1 where 1=0 ; 
MSSQL: 
select col1 from 表 (tablockx) where 1=0 ; 
oracle: 
LOCK TABLE 表 IN EXCLUSIVE MODE ； 
加鎖后其它人不可操作，直到加鎖用戶解鎖，用commit或rollback解鎖

設(shè)table1(A,B,C) 
A B C 
a1 b1 c1 
a2 b2 c2 
a3 b3 c3

begin tran 
update table1 
set A='aa' 
where B='b2' 
waitfor delay '00:00:30' --等待30秒 
commit tran 
在第二個連接中執(zhí)行以下語句 
begin tran 
select * from table1 
where B='b2' 
commit tran

begin tran 
select * from table1 holdlock -holdlock人為加鎖 
where B='b2' 
waitfor delay '00:00:30' --等待30秒 
commit tran

begin tran 
select * from table1 holdlock -holdlock人為加鎖 
where B='b2' 
waitfor delay '00:00:30' --等待30秒 
commit tran

begin tran 
select A,C from table1 
where B='b2' 
update table1 
set A='aa' 
where B='b2' 
commit tran

增設(shè)table2(D,E) 
D E 
d1 e1 
d2 e2 
在第一個連接中執(zhí)行以下語句 
begin tran 
update table1 
set A='aa' 
where B='b2' 
waitfor delay '00:00:30' 
update table2 
set D='d5' 
where E='e1' 
commit tran

begin tran 
update table2 
set D='d5' 
where E='e1' 
waitfor delay '00:00:10' 
update table1 
set A='aa' 
where B='b2' 
commit tran