在實際的多用戶并發訪問的生產環境里邊，我們經常要盡可能的保持數據的一致性。而其中最典型的例子就是我們從表里邊讀取數據，檢查驗證后對數據進行修改，然后寫回到數據庫中。在讀取和寫入的過程中，如果在多用戶并發的環境里邊，其他用戶已經把你要修改的數據進行了修改是非常有可能發生的情況，這樣就造成了數據的不一致性。解決這樣的辦法，SQL SERVER提出了樂觀鎖定和悲觀鎖定的概念，下邊我以一個實例來說明如何使用樂觀鎖定和悲觀鎖定來解決這樣的問題。
/* 建立測試表: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)Goinsert Card(F_CardNo,F_Flag) select '1111-1111',0insert Card(F_CardNo,F_Flag) select '1111-1112',0insert Card(F_CardNo,F_Flag) select '1111-1113',0insert Card(F_CardNo,F_Flag) select '1111-1114',0insert Card(F_CardNo,F_Flag) select '1111-1115',0insert Card(F_CardNo,F_Flag) select '1111-1116',0insert Card(F_CardNo,F_Flag) select '1111-1117',0insert Card(F_CardNo,F_Flag) select '1111-1118',0insert Card(F_CardNo,F_Flag) select '1111-1119',0insert Card(F_CardNo,F_Flag) select '1111-1110',0Go |
-- 下邊是我們經常使用的更新方案如下:
declare @CardNo varchar(20)Begin Tran -- 選擇一張未使用的卡 select top 1 @CardNo=F_CardNo from Card 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 |
問題:如果我們在同一窗口執行同一段代碼，但是去掉了waitfor delay子句.兩邊執行完畢后 我們發現盡管執行了兩次注冊，但是只注冊了一張卡，也就是兩個人注冊了同一張卡.
悲觀鎖定解決方案
-- 我們只要對上邊的代碼做微小的改變就可以實現悲觀的鎖定.
declare @CardNo varchar(20)Begin Tran -- 選擇一張未使用的卡 select top 1 @CardNo=F_CardNo from Card with (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 where F_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的代碼,注冊成功后,返回原來的窗口,我們就會發現到時間后它顯示的提示是此卡以被另外一個用戶注冊的提示.很明顯,這樣我們也可以避免兩個用戶同時注冊一張卡的現象的出現.同時,使用這種方法的另外一個好處是沒有使用更新鎖,這樣增加的系統的并發處理能力.
上邊我詳細介紹了樂觀鎖定和悲觀鎖定的使用方法,在實際生產環境里邊,如果并發量不大,我們完全可以使用悲觀鎖定的方法,因為這種方法使用起來非常方便和簡單.但是如果系統的并發非常大的話,悲觀鎖定會帶來非常大的性能問題,所以我們就要選擇樂觀鎖定的方法.
如果大家發現文章里邊有什么錯誤的地方,請及時提醒我,也歡迎有興趣的一起研究討論.
(t116)
