至顶网›软件频道 ›SQL Server乐观锁定和悲观锁定实例

SQL Server乐观锁定和悲观锁定实例

扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条

本文使用一个实例来说明如何使用乐观锁定和悲观锁定来解决多用户并发的环境里，其他用户已经把你要修改的数据进行了修改而造成数据的不一致的问题。

作者：老陈来源：Csdn博客 2007年9月12日

【引自老陈的博客】在实际的多用户并发访问的生产环境里边，我们经常要尽可能的保持数据的一致性。而其中最典型的例子就是我们从表里边读取数据，检查验证后对数据进行修改，然后写回到数据库中。在读取和写入的过程中，如果在多用户并发的环境里边，其他用户已经把你要修改的数据进行了修改是非常有可能发生的情况，这样就造成了数据的不一致性。解决这样的办法，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