一、為什么要引入鎖

多個(gè)用戶同時(shí)對(duì)數(shù)據(jù)庫(kù)進(jìn)行并發(fā)操作時(shí)，會(huì)引起以下數(shù)據(jù)不一致的問題：

丟失更新：AB兩個(gè)用戶對(duì)同一數(shù)據(jù)進(jìn)行修改，其中一個(gè)用戶的修改結(jié)果破壞了另一個(gè)用戶的修改結(jié)果，比如訂票系統(tǒng)。

臟讀：A用戶修改了數(shù)據(jù)，隨后B用戶讀取并使用了該數(shù)據(jù)，之后因?yàn)槟承┰駻又撤銷了對(duì)數(shù)據(jù)的更改，數(shù)據(jù)恢復(fù)原值，此時(shí)B用戶得到的數(shù)據(jù)就可能與數(shù)據(jù)庫(kù)不一致。

不可重復(fù)讀：同一個(gè)事務(wù)內(nèi)，同一用戶兩次讀到的數(shù)據(jù)不一致。

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

二、鎖的分類

鎖的類別有兩種分法：

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

排他鎖（X鎖）：如果事務(wù)T對(duì)數(shù)據(jù)A加上排他鎖后，則其他事務(wù)不能再對(duì)A加任任何類型的封鎖。只允許進(jìn)行鎖定操作的程序使用，其它任何對(duì)它的操作均不會(huì)被接受。執(zhí)行數(shù)據(jù)更新命令時(shí)，SQL Server會(huì)自動(dòng)使用獨(dú)占鎖。

共享鎖（S鎖）：如果事務(wù)T對(duì)數(shù)據(jù)A加上共享鎖后，則其他事務(wù)只能對(duì)A再加共享鎖，不能加排他鎖。獲準(zhǔn)共享鎖的事務(wù)只能讀數(shù)據(jù)，不能修改數(shù)據(jù)。共享鎖下其它用戶可以并發(fā)讀取，查詢數(shù)據(jù)。但不能修改，增加，刪除數(shù)據(jù)。資源共享。

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

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

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

2、從程序員的角度看：分為樂觀鎖和悲觀鎖。
樂觀鎖：完全依靠數(shù)據(jù)庫(kù)來(lái)管理鎖的工作；
悲觀鎖：程序員自己管理數(shù)據(jù)或?qū)ο笊系逆i處理；

請(qǐng)參考：http://blog.csdn.net/lmb55/article/details/78266667

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

三、鎖的粒度

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

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

RID：行標(biāo)識(shí)符，用于單獨(dú)鎖定表中的一行。
鍵：索引中的行鎖，用于保護(hù)可串行事務(wù)中的鍵范圍。
頁(yè)：8千字節(jié)（KB）的數(shù)據(jù)頁(yè)或索引頁(yè)。
擴(kuò)展盤區(qū)：相鄰的八個(gè)數(shù)據(jù)頁(yè)或索引頁(yè)構(gòu)成的一組。
表：包括所有數(shù)據(jù)和索引在內(nèi)的整個(gè)表。
DB: 數(shù)據(jù)庫(kù)。

四、鎖定時(shí)間的長(zhǎng)短

鎖保持的時(shí)間長(zhǎng)度為保護(hù)鎖請(qǐng)求級(jí)別上的資源所需的時(shí)間長(zhǎng)度。

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

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

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

如果一個(gè)連接試圖獲取一個(gè)鎖，而該鎖與另一個(gè)連接所控制的鎖沖突，則試圖獲取鎖的連接將一直阻塞到?jīng)_突鎖被釋放而且連接獲取了所請(qǐng)求的鎖。

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

五、SQL Server中鎖的自定義

1、處理死鎖和設(shè)置死鎖優(yōu)先級(jí)

死鎖就是多個(gè)用戶申請(qǐng)不同的鎖，由于每個(gè)申請(qǐng)者均擁有一部分資源而又等待其他用戶擁有的資源而引起無(wú)休止的等待。

可以使用SET DEADLOCL_PRIORITY控制在發(fā)生死鎖情況時(shí)會(huì)話的反應(yīng)方式。如果兩個(gè)進(jìn)程都鎖定數(shù)據(jù)，并且直到其他進(jìn)程釋放自己的鎖時(shí)，每個(gè)進(jìn)程才能釋放自己的鎖，即發(fā)生死鎖情況。

2、處理超時(shí)和設(shè)置鎖超時(shí)持續(xù)時(shí)間

LOCK_TIMEOUT返回當(dāng)前會(huì)話的當(dāng)前鎖超時(shí)設(shè)置，單位為毫秒。

SEI LOCK_TIMEOUT設(shè)置允許應(yīng)用程序設(shè)置語(yǔ)句等待阻塞資源的最長(zhǎng)時(shí)間。當(dāng)語(yǔ)句等待的時(shí)間大于LOCK_TIMEOUT設(shè)置時(shí)，系統(tǒng)將自動(dòng)取消阻塞的語(yǔ)句，并給應(yīng)用程序返回“已超過了鎖請(qǐng)求超時(shí)時(shí)段”的錯(cuò)誤信息。

示例：將鎖超時(shí)期限設(shè)置為1800毫秒。
SET LOCK_TIMEOUT 1800

3、設(shè)置事務(wù)隔離級(jí)別

4、對(duì)SELECT、INSERT、UPDATE個(gè)DELETE語(yǔ)句使用表級(jí)鎖定提示。

5、配置索引的鎖定粒度

可以使用sp_indexoption系統(tǒng)存儲(chǔ)過程來(lái)設(shè)置用于索引的鎖定粒度。

六、查看鎖的信息

1、執(zhí)行EXEC SP_LOCK報(bào)告有關(guān)鎖的信息
2、查詢分析器中按Ctrl+2可以看到鎖的信息

七、使用注意事項(xiàng)

如何避免死鎖：

1、使用事務(wù)時(shí)，盡量縮短事務(wù)的邏輯處理過程，及早提交或回滾事務(wù)；

2、設(shè)置死鎖超時(shí)參數(shù)為合理范圍，如：3—10分鐘，超過時(shí)間，自動(dòng)放棄本次操作，避免進(jìn)程懸掛；

3、優(yōu)化程序，檢查并避免死鎖現(xiàn)象出現(xiàn)；

4、在正式版本之前，對(duì)所有的腳本和SP都要仔細(xì)測(cè)試；

5、所有的SP都要有錯(cuò)誤處理（通過@error）；

6、一般不要修改SQL SERVER事務(wù)的默認(rèn)級(jí)別，不推薦強(qiáng)行加鎖。

八、幾個(gè)有關(guān)鎖的問題

解決問題：如何對(duì)行、表和數(shù)據(jù)庫(kù)加鎖。

1、如何鎖定一個(gè)表的某一行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM table ROWLOCK WHERE id = 1

2、鎖定數(shù)據(jù)庫(kù)的一個(gè)表

SELECT * FROM table WITH(HOLDLOCK)

3、加鎖語(yǔ)句：

MySQL:

select col1 from 表 (tablockx) where 1=0 ;

oracle:

LOCK TABLE 表 IN EXCLUSIVE MODE ；

加鎖后其它人不可操作，直到加鎖用戶解鎖，用commit或rollback解鎖

幾個(gè)例子幫助大家加深印象:

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

1）排它鎖
新建兩個(gè)連接
在第一個(gè)連接中執(zhí)行以下語(yǔ)句

begin tran
update table1
set A=’aa’
where B=’b2’
waitfor delay ‘00:00:30’ –等待30秒
commit tran

在第二個(gè)連接中執(zhí)行以下語(yǔ)句

begin tran
select * from table1
where B=’b2’
commit tran

若同時(shí)執(zhí)行上述兩個(gè)語(yǔ)句，則select查詢必須等待update執(zhí)行完畢才能執(zhí)行即要等待30秒

2）共享鎖
在第一個(gè)連接中執(zhí)行以下語(yǔ)句

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

在第二個(gè)連接中執(zhí)行以下語(yǔ)句

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

若同時(shí)執(zhí)行上述兩個(gè)語(yǔ)句，則第二個(gè)連接中的select查詢可以執(zhí)行
而update必須等待第一個(gè)事務(wù)釋放共享鎖轉(zhuǎn)為排它鎖后才能執(zhí)行 即要等待30秒

3）死鎖
增設(shè)table2(D,E)
D E
d1 e1
d2 e2

在第一個(gè)連接中執(zhí)行以下語(yǔ)句

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

在第二個(gè)連接中執(zhí)行以下語(yǔ)句

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

同時(shí)執(zhí)行，系統(tǒng)會(huì)檢測(cè)出死鎖，并中止進(jìn)程

注意: 鎖定數(shù)據(jù)庫(kù)的一個(gè)表的區(qū)別

SELECT * FROM table WITH (HOLDLOCK) –其他事務(wù)可以讀取表，但不能更新刪除

SELECT * FROM table WITH (TABLOCKX) –其他事務(wù)不能讀取表,更新和刪除

總結(jié)

以上是生活随笔為你收集整理的数据库锁机制的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

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