SQL SERVER乐观锁定和悲观锁定使用实例

在实际的多用户并发访问的生产环境里边，我们经常要尽可能的保持数据的一致性。而其中最典型的例子就是我们从表里边读取数据，检查验证后对数据进行修改，然后写回到数据库中。在读取和写入的过程中，如果在多用户并发的环境里边，其他用户已经把你要修改的数据进行了修改是非常有可能发生的情况，这样就造成了数据的不一致性。解决这样的办法，SQL SERVER提出了乐观锁定和悲观锁定的概念，下边我以一个实例来说明如何使用乐观锁定和悲观锁定来解决这样的问题。

　　/* 建立测试表:Card,代表一个真实的卡库,供用户注册.用户要从里边选出一个未使用的卡，也就是F_Flag=0的卡，给用户注册：更新F_Name,F_Time,F_Flag字段. 如果出现两个用户同时更新一张卡的情况，是不能容忍的，也就是我们所说的数据不一致行。注意：如下代码若复制粘贴到查询分析器中，每句的前导空格可能是双字节的，会出现语法错误，要删除。（）*/

初始化测试表：update card set f_name=null,f_flag=0,f_time=null

　　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 Transaction

-- 选择一张未使用的卡

select top 1 @CardNo=F_CardNo

from Card where F_Flag=0

-- 延迟秒，模拟并发访问.

waitfor delay '000:00:20'

注解：

declare @CardNo varchar(20)

select top 1 @CardNo=F_CardNo from Card where F_Flag=0

--上句是将从card表中选定的符合条件的第一行的 F_CardNo字段值赋给 @CardNo。

--下句可以验证：

select @CardNo

-- 把刚才选择出来的卡进行注册.

update Card

set F_Name=user,

F_Time=getdate(),

F_Flag=1

where F_CardNo=@CardNo

commit Transaction

改造过的：

declare @CardNo varchar(20)

Begin Transaction

-- 选择一张未使用的卡

select top 1 @CardNo=F_CardNo

from Card where F_Flag=0

-- 延迟秒，模拟并发访问.

waitfor delay '000:00:30' --这30秒是让你有时间在这时间里再执行一次本段代码，模拟并发事务。

-- 把刚才选择出来的卡进行注册.

update Card

set F_Name=user,F_Time=getdate(),F_Flag=1 where F_CardNo=@CardNo

commit Transaction

问题:如果我们在同一窗口执行同一段代码，但是去掉了waitfor delay子句.两边执行完毕后 我们发现尽管执行了两次注册，但是只注册了一张卡，也就是两个人注册了同一张卡.

悲观锁定解决方案-- 我们只要对上边的代码做微小的改变就可以实现悲观的锁定.

declare @CardNo varchar(20)

Begin Transaction

-- 选择一张未使用的卡

select top 1 @CardNo=F_CardNo

from Card with (UPDLOCK) where F_Flag=0

-- 延迟秒，模拟并发访问.

--waitfor delay '000:00:50'

--waitfor time '2:12:00'

-- 把刚才选择出来的卡进行注册.

update Card

set F_Name=user,

F_Time=getdate(),

F_Flag=1

where F_CardNo=@CardNo

commit Transaction

注意其中的区别了吗?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

-- 延迟秒，模拟并发访问.

--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 --@@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的代码,注册成功后,返回原来的窗口,我们就会发现到时间后它显示的提示是此卡以被另外一个用户注册的提示.很明显,这样我们也可以避免两个用户同时注册一张卡的现象的出现.同时,使用这种方法的另外一个好处是没有使用更新锁,这样增加的系统的并发处理能力.

　　上边我详细介绍了乐观锁定和悲观锁定的使用方法,在实际生产环境里边,如果并发量不大,我们完全可以使用悲观锁定的方法,因为这种方法使用起来非常方便和简单.但是如果系统的并发非常大的话,悲观锁定会带来非常大的性能问题,所以我们就要选择乐观锁定的方法.

1．关于乐观锁（optimistic lock）和悲观锁（pessimistic lock）

从数据库系统角度，可把锁分为共享锁、独占锁、更新锁，从程序员角度，可把锁分为乐观锁（optimistic lock）和悲观锁（pessimistic lock）

乐观锁是指在处理数据时，不需要在应用程序的代码中做任何事情，就可以直接在记录上加锁，即完全依靠数据库来管理锁的工作。一般情况下，当执行事务处理时，SQL会自动对事务处理范围内更新到的表做锁定。

悲观锁不采用数据库系统的自动管理，需要程序员直接管理数据或对象上的加锁处理，并负责获取共享和放弃正在使用的数据上的任何锁。

2．关于 timestamp  

  timestamp   这种数据类型表现自动生成的二进制数，确保这些数在数据库中是唯一的。timestamp   一般用作给表行加版本戳的机制。存储大小为   8   字节。  

       一个表只能有一个   timestamp   列。每次插入或更新包含   timestamp   列的行时，timestamp   列中的值均会更新。这一属性使   timestamp   列不适合作为键使用，尤其是不能作为主键使用。对行的任何更新都会更改   timestamp   值，从而更改键值。如果该列属于主键，那么旧的键值将无效，进而引用该旧值的外键也将不再有效。如果该表在动态游标中引用，则所有更新均会更改游标中行的位置。如果该列属于索引键，则对数据行的所有更新还将导致索引更新。  

    不可为空的   timestamp   列在语义上等价于   binary(8)   列。可为空的   timestamp   列在语义上等价于   varbinary(8)   列。  

3．请问"select 1 from table where id=condition"这句的返回结果及它的意思

判断一个条件在这表中是否存在，常常用来表示是否存在某条记录

判断是否有符合条件的数据。Select 1　要比Select * 速度快。结果集倒是没有什么实际的意义。如：

    if exists(select 1 from table where id=condition)

感悟：

本实例突出两个事件（动作）：拿号——>登记注册，并发冲突就是处理 拿号到登记注册之间2秒时间的冲突，第一人拿号尚未来及登记，第二人也拿号（同号），如果第二个人拿号发生在第一个人登记之后，就没有任何冲突了

【declare st cursor for select * from student for update of 身份证号

open st1

fetch next from st】