SQL Server 悲观锁与乐观锁(摘自网络)

olop

锁。我们知道，最常用的处理多用户并发访问的方法是加锁，当一个用户锁住数据库中的某个对象时，其他用户就不能再访问该对象。加锁对并发访问的影响体现在锁的粒度上，比如：
放在一个表上的锁限制对整个表的并发访问；
放在数据页上的锁限制了对整个数据页的访问；
放在行上的锁只限制对该行的并发访问。
可见行锁粒度最小，并发访问最好，
页锁粒度最大，表锁介于2者之间。
锁有两种：悲观锁和乐观锁。
悲观锁假定其他用户企图访问或者改变你正在访问、更改的对象的概率是很高的，因此在悲观锁的环境中，在你开始改变此对象之前就将该对象锁住，并且直到你提交了所作的更改之后才释放锁。悲观的缺陷是不论是页锁还是行锁，加锁的时间可能会很长，这样可能会长时间的限制其他用户的访问，也就是说悲观锁的并发访问性不好。

与悲观锁相反，乐观锁则认为其他用户企图改变你正在更改的对象的概率是很小的，因此乐观锁直到你准备提交所作的更改时才将对象锁住，当你读取以及改变该对象时并不加锁。可见乐观锁加锁的时间要比悲观锁短，乐观锁可以用较大的锁粒度获得较好的并发访问性能。但是如果第二个用户恰好在第一个用户提交更改之前读取了该对象，那么当他完成了自己的更改进行提交时，数据库就会发现该对象已经变化了，这样，第二个用户不得不重新读取该对象并作出更改。这说明在乐观锁环境中，会增加并发用户读取对象的次数。

　　从数据库厂商的角度看，使用乐观的页锁是比较好的，尤其在影响很多行的批量操作中可以放比较少的锁，从而降低对资源的需求提高数据库的性能。再考虑聚集索引。在数据库中记录是按照聚集索引的物理顺序存放的。如果使用页锁，当两个用户同时访问更改位于同一数据页上的相邻两行时，其中一个用户必须等待另一个用户释放锁，这会明显地降低系统的性能。interbase和大多数关系数据库一样，采用的是乐观锁，而且读锁是共享的，写锁是排他的。可以在一个读锁上再放置读锁，但不能再放置写锁；你不能在写锁上再放置任何锁。锁是目前解决多用户并发访问的有效手段。


/* 建立测试表: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


乐观锁定解决方案

--  下边是我们经常使用的更新方案如下，执行Session1 再执行 Session2：

Session 1

declare @CardNo varchar(20)
Begin Tran

       --  选择一张未使用的卡
        select top 1 @CardNo=F_CardNo
        from Card    where F_Flag=0
        
        --  延迟10秒，模拟并发访问.
        waitfor delay '000:00:10'

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

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

commit

Session 2

declare @CardNo varchar(20)
Begin Tran

       --  选择一张未使用的卡
        select top 1 @CardNo=F_CardNo
        from Card    where F_Flag=0
        
        --  延迟10秒，模拟并发访问.
       -- waitfor delay '000:00:10'   

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

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

commit

两边执行完毕后， 我们发现尽管执行了两次注册，但是只注册了一张卡，也就是两个人注册了同一张卡。

悲观锁定解决方案

--  我们只要对上边的代码做微小的改变就可以实现悲观的锁定 ，先执行Session1 再执行 Session2：

Session 1

declare @CardNo varchar(20)
Begin Tran

       --  选择一张未使用的卡
        select top 1 @CardNo=F_CardNo
        from Card   with (UPDLOCK)  where F_Flag=0
        
        --  延迟10秒，模拟并发访问.
        waitfor delay '000:00:10'

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

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

commit

Session 2

declare @CardNo varchar(20)
Begin Tran

       --  选择一张未使用的卡
        select top 1 @CardNo=F_CardNo
        from Card   with (UPDLOCK)  where F_Flag=0
        
        --  延迟10秒，模拟并发访问.
        --waitfor delay '000:00:10'

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

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

commit

两边执行完毕后， 我们发现执行了两次注册。