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

精选转载

kinwar 2014-05-22 14:37:45 博主文章分类：SQLServer

文章标签 网络数据库用户影响最好 文章分类 数据库

锁。我们知道，最常用的处理多用户并发访问的方法是加锁，当一个用户锁住数据库中的某个对象时，其他用户就不能再访问该对象。加锁对并发访问的影响体现在锁的粒度上，比如：

放在一个表上的锁限制对整个表的并发访问；

放在数据页上的锁限制了对整个数据页的访问；

放在行上的锁只限制对该行的并发访问。

可见行锁粒度最小，并发访问最好，

页锁粒度最大，表锁介于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)

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

乐观锁定解决方案

-- 下边是我们经常使用的更新方案如下，执行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

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