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的程式碼,註冊成功後,返回原來的視窗,我們就會發現到時間後它顯示的提示是此卡以被另外一個使用者註冊的提示.很明顯,這樣我們也可以避免兩個使用者同時註冊一張卡的現象的出現.同時,使用這種方法的另外一個好處是沒有使用更新鎖,這樣增加的系統的併發處理能力.
上邊我詳細介紹了樂觀鎖定和悲觀鎖定的使用方法,在實際生產環境裡邊,如果併發量不大,我們完全可以使用悲觀鎖定的方法,因為這種方法使用起來非常方便和簡單.但是如果系統的併發非常大的話,悲觀鎖定會帶來非常大的效能問題,所以我們就要選擇樂觀鎖定的方法.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/16436858/viewspace-591583/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQLServer樂觀鎖定和悲觀鎖定例項SQLServer
- 悲觀鎖和樂觀鎖
- laravel樂觀鎖和悲觀鎖Laravel
- [轉帖]SQL Server 鎖機制 悲觀鎖 樂觀鎖 實測解析SQLServer
- MySQL樂觀鎖和悲觀鎖介紹MySql
- mysql悲觀鎖以樂觀鎖MySql
- 樂觀鎖與悲觀鎖及應用舉例
- MySQL鎖(樂觀鎖、悲觀鎖、多粒度鎖)MySql
- Redis的事務、樂觀鎖和悲觀鎖Redis
- java-樂觀鎖與悲觀鎖Java
- MybatisPlus - [03] 樂觀鎖&悲觀鎖MyBatis
- 資料庫中的悲觀鎖和樂觀鎖資料庫
- JPA和Hibernate的樂觀鎖與悲觀鎖
- Java中的鎖之樂觀鎖與悲觀鎖Java
- 悲觀鎖定的應用
- Java 中的悲觀鎖和樂觀鎖的實現Java
- 面試必備之悲觀鎖與樂觀鎖面試
- MySQL 悲觀鎖與樂觀鎖的詳解MySql
- 面試必備之樂觀鎖與悲觀鎖面試
- Java彌散系列 - 樂觀鎖與悲觀鎖Java
- 經典問題之樂觀鎖和悲觀鎖及使用場景
- 利用MySQL中的樂觀鎖和悲觀鎖實現分散式鎖MySql分散式
- 小議“悲觀鎖和樂觀鎖”的原理、場景、示例
- 樂觀鎖和悲觀鎖策略的區別與實現
- 【鎖機制】共享鎖、排它鎖、悲觀鎖、樂觀鎖、死鎖等等
- 樂觀鎖和悲觀鎖在kubernetes中的應用
- Java併發程式設計(05):悲觀鎖和樂觀鎖機制Java程式設計
- Spring Boot2+JPA之悲觀鎖和樂觀鎖實戰Spring Boot
- 悲觀鎖與樂觀鎖的實現(詳情圖解)圖解
- 【每日鮮蘑】從資料庫看樂觀鎖、悲觀鎖資料庫
- 面試必備的資料庫悲觀鎖與樂觀鎖面試資料庫
- 關於樂觀鎖與悲觀鎖的實際應用
- SSM (十五) 樂觀鎖與悲觀鎖的實際應用SSM
- 解鎖你的資料庫:JPA和Hibernate的樂觀鎖與悲觀鎖資料庫
- Java鎖最全詳解:樂觀鎖/悲觀鎖+公平鎖/非公平鎖+獨享鎖/共享鎖Java
- 面試必備知識點:悲觀鎖和樂觀鎖的那些事兒面試
- 使用ORA_ROWSCN實現樂觀鎖定
- 一文讀懂資料庫中的樂觀鎖和悲觀鎖和MVVC資料庫