SQL Server樂觀鎖定和悲觀鎖定執行個體

 

【引自老陳的部落格】

在實際的多使用者並發訪問的生產環境裡邊，我們經常要儘可能的保持資料的一致性。而其中最典型的例子就是我們從表裡邊讀取資料，檢查驗證後對資料進行修改，然後寫回到資料庫中。在讀取和寫入的過程中，如果在多使用者並發的環境裡邊，其他使用者已經把你要修改的資料進行了修改是非常有可能發生的情況，這樣就造成了資料的不一致性。解決這樣的辦法，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) 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 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 commit

問題：如果我們在同一視窗執行同一段代碼，但是去掉了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=@CardNo commit

注意其中的區別了嗎?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 int Begin 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的代碼，註冊成功後，返回原來的視窗，我們就會發現到時間後它顯示的提示是此卡以被另外一個使用者註冊的提示。很明顯，這樣我們也可以避免兩個使用者同時註冊一張卡的現象的出現。同時，使用這種方法的另外一個好處是沒有使用更新鎖定，這樣增加的系統的並發處理能力。

上邊我詳細介紹了樂觀鎖定和悲觀鎖定的使用方法，在實際生產環境裡邊，如果並發量不大，我們完全可以使用悲觀鎖定的方法，因為這種方法使用起來非常方便和簡單。但是如果系統的並發非常大的話，悲觀鎖定會帶來非常大的效能問題，所以我們就要選擇樂觀鎖定的方法。

如果大家發現文章裡邊有什麼錯誤的地方，請及時提醒我，也歡迎有興趣的一起研究討論。