SQLSERVER樂觀鎖定和悲觀鎖定使用實例

在實際的多用戶并發訪問的生產環境里邊，我們經常要盡可能的保持數據的一致性。而其中最典型的例子就是我們從表里邊讀取數據，檢查驗證后對數據進行修改，然后寫回到數據庫中。在讀取和寫入的過程中，如果在多用戶并發的環境里邊，其他用戶已經把你要修改的數

/ 建立測試表:Card，代表一個真實的卡庫，供用戶注冊.用戶要從里邊選出一個未使用的卡，也就是F_Flag=0的卡，給用戶注冊：更新F_Name，F_Time，F_Flag字段. 如果出現兩個用戶同時更新一張卡的情況，是不能容忍的，也就是我們所說的數據不一致行。/create table Card(F_CardNO varchar(20)，F_Name varchar(20)，F_Flag bit，F_Time datetime)
Go
insert Card(F_CardNo，F_Flag) select ‘1111-1111‘，0
insert Card(F_CardNo，F_Flag) select ‘1111-1112‘，0
insert Card(F_CardNo，F_Flag) select ‘1111-1113‘，0
insert Card(F_CardNo，F_Flag) select ‘1111-1114‘，0
insert Card(F_CardNo，F_Flag) select ‘1111-1115‘，0
insert Card(F_CardNo，F_Flag) select ‘1111-1116‘，0
insert Card(F_CardNo，F_Flag) select ‘1111-1117‘，0
insert Card(F_CardNo，F_Flag) select ‘1111-1118‘，0
insert Card(F_CardNo，F_Flag) select ‘1111-1119‘，0
insert Card(F_CardNo，F_Flag) select ‘1111-1110‘，0
Go-- 下邊是我們經常使用的更新方案如下:declare @CardNo varchar(20)
Begin Tran -- 選擇一張未使用的卡
select top 1 @CardNo=F_CardNo
from Cardwhere F_Flag=0

-- 延遲50秒，模擬并發訪問.
waitfor delay ‘000:00:50‘ -- 把剛才選擇出來的卡進行注冊. update Card
set F_Name=user，
F_Time=getdate()，
F_Flag=1
where F_CardNo=@CardNocommit問題:如果我們在同一窗口執行同一段代碼，但是去掉了waitfor delay子句.兩邊執行完畢后我們發現盡管執行了兩次注冊，但是只注冊了一張卡，也就是兩個人注冊了同一張卡.悲觀鎖定解決方案-- 我們只要對上邊的代碼做微小的改變就可以實現悲觀的鎖定.declare @CardNo varchar(20)
Begin Tran -- 選擇一張未使用的卡
select top 1 @CardNo=F_CardNo
from Cardwith (UPDLOCK) where F_Flag=0

-- 延遲50秒，模擬并發訪問.
waitfor delay ‘000:00:50‘ -- 把剛才選擇出來的卡進行注冊. update Card
set F_Name=user，
F_Time=getdate()，
F_Flag=1
where F_CardNo=@CardNocommit注意其中的區別了嗎?with(updlock)，是的，我們在查詢的時候使用了with (UPDLOCK)選項，在查詢記錄的時候我們就對記錄加上了更新鎖，表示我們即將對次記錄進行更新.注意更新鎖和共享鎖是不沖突的，也就是其他用戶還可以查詢此表的內容，但是和更新鎖和排它鎖是沖突的.所以其他的更新用戶就會阻塞.如果我們在另外一個窗口執行此代碼，同樣不加waifor delay子句.兩邊執行完畢后，我們發現成功的注冊了兩張卡.可能我們已經發現了悲觀鎖定的缺點:當一個用戶進行更新的事務的時候，其他更新用戶必須排隊等待，即使那個用戶更新的不是同一條記錄.樂觀鎖定解決方案-- 首先我們在Card表里邊加上一列F_TimeStamp 列，該列是varbinary(8)類型.但是在更新的時候這個值會自動增長.alter table Card add F_TimeStamp timestamp not null

-- 悲觀鎖定
declare @CardNo varchar(20)
declare @timestamp varbinary(8)
declare @rowcount intBegin Tran -- 取得卡號和原始的時間戳值
select top 1 @CardNo=F_CardNo，
@timestamp=F_TimeStamp
from Card
where F_Flag=0

-- 延遲50秒，模擬并發訪問.
waitfor delay ‘000:00:50‘ -- 注冊卡，但是要比較時間戳是否發生了變化.如果沒有發生變化.更新成功.如果發生變化，更新失敗. update Card
set F_Name=user，
F_Time=getdate()，
F_Flag=1
whereF_CardNo=@CardNo and F_TimeStamp=@timestamp
set @rowcount=@@rowcount
if @rowcount=1
begin
print ‘更新成功!‘
commit
end
else if @rowcount=0
begin
if exists(select 1 from Card where F_CardNo=@CardNo)
begin
print ‘此卡已經被另外一個用戶注冊！‘
rollback tran
end
else
begin
print ‘并不存在此卡!‘
rollback tran
end
end在另外一個窗口里邊執行沒有waitfor的代碼，注冊成功后，返回原來的窗口，我們就會發現到時間后它顯示的提示是此卡以被另外一個用戶注冊的提示.很明顯，這樣我們也可以避免兩個用戶同時注冊一張卡的現象的出現.同時，使用這種方法的另外一個好處是沒有使用更新鎖，這樣增加的系統的并發處理能力.上邊我詳細介紹了樂觀鎖定和悲觀鎖定的使用方法，在實際生產環境里邊，如果并發量不大，我們完全可以使用悲觀鎖定的方法，因為這種方法使用起來非常方便和簡單.但是如果系統的并發非常大的話，悲觀鎖定會帶來非常大的性能問題，所以我們就要選擇樂觀鎖定的方法.如果大家發現文章里邊有什么錯誤的地方，請及時提醒我，也歡迎有興趣的一起研究討論.

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com