sql 乐观锁 timestamp

 

 

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

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

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

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

/* 建立测试表:Train_ticket,代表一个真实的火车票库,供用户注册.用户要从里边购买一个未使用的火车票，也就是S_Flag=0的票，给用户 注册：更新T_Name,T_Time,S_Flag字段. 如果出现两个用户同时更新一张票的情况，是不能容忍的，也就是我们所说的数据不一致行。*/

create table Train_ticket(T_NO varchar(20),T_Name varchar(20),S_Flag bit,T_Time datetime)

悲观锁定解决方案

Begin Tran
select top 1 @TrainNo=T_NO
         from Train_ticket   with (UPDLOCK)   where S_Flag=0

      update Train_ticket
         set T_Name=user,
             T_Time=getdate(),
             S_Flag=1
         where T_NO=@TrainNo
commit

注 意其中的区别了吗?with(updlock),是的,我们在查询的时候使用了with (UPDLOCK)选项,在查询记录的时候我们就对记录加上了更新锁,表示我们即将对次记录进行更新.注意更新锁和共享锁是不冲突的,也就是其他用户还可 以查询此表的内容,但是和更新锁和排它锁是冲突的.所以其他的更新用户就会阻塞.如果我们在另外一个窗口执行此代码,同样不加waifor delay子句.两边执行完毕后,我们发现成功的注册了两张火车票.可能我们已经发现了悲观锁定的缺点:当一个用户进行更新的事务的时候,其他更新用户必 须排队等待,即使那个用户更新的不是同一条记录.

乐观锁定解决方案

--   首先我们在Train_ticket表里边加上一列T_TimeStamp 列,该列是varbinary(8)类型.但是在更新的时候这个值会自动增长.

alter table Train_ticket add   T_TimeStamp timestamp not null

--   取得号和原始的时间戳值
         select top 1 @TrainNo=T_No,
                     @timestamp=T_TimeStamp
        from Train_ticket
         where S_Flag=0
        
         --   延迟50秒，模拟并发访问.
         waitfor delay '000:00:50'

         --   购买票,但是要比较时间戳是否发生了变化.如果没有发生变化.更新成功.如果发生变化,更新失败.
         update Train_ticket
         set T_Name=user,
             T_Time=getdate(),
             S_Flag=1
         where T_No = @TrainNo and F_TimeStamp = @timestamp

        set @rowcount=@@rowcount
         if @rowcount=1
         begin
                 print 'Successful!'
                 commit
         end
         else if @rowcount=0
         begin
                 if exists(select 1 from Train_ticket where T_No = @TrainNo)

                begin
                         print 'The ticket was already buyed.'
                         rollback tran
                 end
                 else
                 begin
                         print 'This ticket doesn't exist!'
                         rollback tran
                 end
         end

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

SQL Server开始是用行级锁的，但是经常会扩大为页面锁和表锁，最终造成死锁。

即使用户没有修改数据，SQL Server在SELECT的时候也会遇到锁。幸运的是，我们可以通过SQL Server 的两个关键字来手工处理：NOLOCK和ROWLOCK。

它们的使用方法如下：

SELECT COUNT(UserID)
FROM Users WITH (NOLOCK)
WHERE Username LIKE 'foobar'

和

UPDATE Users WITH (ROWLOCK)
SET Username = 'fred' WHERE Username = 'foobar'

NOLOCK的使用

NOLOCK可以忽略锁，直接从数据库读取数据。这意味着可以避开锁，从而提高性能和扩展性。但同时也意味着代码出错的可能性存在。你可能会读取到运行事务正在处理的无须验证的未递交数据。 这种风险可以量化。

如果是金融方面的代码或者一些非常规的总计(你想绝对保证安全性)，你应该小心行事并且不使用这种技术。 但是我认为使用该技术会比你90%应用系统性能要好，当用户(或者是交互代码)发现一个未递交的修改时，使用技术会保证不会像未使用该技术那样引起大麻烦。实际上，你可能发现你的大多数数据很少或者甚至不进行 修改的，这样我们就不会因为这些数据被锁住而浪费大量的时间。

例如，如果你想统计在2000年6月份到8月份之间加入Streamload.com的所有用户，就没有理由去锁住任何记录： 2000年9月1号一到来，这个用户数就是确定的。又例如要列举在Streamload.com的文件列表：这种结果即使 不是100%的正确，也不是大问题。因为你要么不拥有该文件，当然也无所谓你是否能找到它，或者你确实拥有该文件，这种情况下你当然知道你是否修改了该文件，以及该文件是否已经上传完毕了。

但是，如果这些数据的修改，对数据库来说是基础性的修改，或者这些数据对于用户来说，必须是百分之百保证 是修改正确的(例如帐单或者余额数据)，那么你不要使用该技术。

ROWLOCK的使用

ROWLOCK告诉SQL Server只使用行级锁。ROWLOCK语法可以使用在SELECT,UPDATE和DELETE语句中，不过 我习惯仅仅在UPDATE和DELETE语句中使用。如果在UPDATE语句中有指定的主键，那么就总是会引发行级锁的。但是当SQL Server对几个这种UPDATE进行批处理时，某些数据正好在同一个页面(page)，这种情况在当前情况下 是很有可能发生的，这就象在一个目录中，创建文件需要较长的时间，而同时你又在更新这些文件。当页面锁引发后，事情就开始变得糟糕了。而如果在UPDATE或者DELETE时，没有指定主键，数据库当然认为很多数据会收到影响，那样 就会直接引发页面锁，事情同样变得糟糕。

通过指定使用行级锁，这种情况可以得到避免。但是需要小心的是，如果你错误地使用在过多行上，数据库并不会聪明到自动将行级锁升级到页面锁，服务器也会因为行级锁的开销而消耗大量的内存和CPU，直至无法响应。尤其主要留意的是 企业管理器中"管理/当前活动"(Management/Current Activity)这一项。该项会花较长的时间来载入锁的信息。这些信息 时十分有用的，当你使用行级锁后，你如果在"锁/处理"(Locks/Processes)下看到几百个锁，一点都不奇怪，而恰恰应该庆幸锁超时和死锁的问题减少了。

注意事项

我认为SQL Server倾向于使用NOLOCK关键字，而ROWLOCK关键字由用户根据情况自行决定。你可以仅仅在 SELECT语句中使用NOLOCK，这些SELECT语句场合包括INNER查询，以及在INSERT语句中的SELECT使用，在连接查询下也可以使用，例如：

SELECT COUNT(Users.UserID)
FROM Users WITH (NOLOCK)
JOIN UsersInUserGroups WITH (NOLOCK) ON
Users.UserID = UsersInUserGroups.UserID

NOLOCK 和 ROWLOCK的使用效果

很难去量化在使用NOLOCK和ROWLOCK后，Streamload.com或者你的网站性能到底改善了多少。 不过在使用NOLOCK和ROWLOCK前，Streamload.com的速度很慢，而且经常无法使用，以及很不稳定。使用后，就变得快速、容易访问以及稳定了。两者简直就是天壤之别。这些改变当然无法在 关于锁的文档中很难找到。那些文档会建议你重写你的应用，当表数据被使用，锁产生了(没错，就是这样)，然后你应该使用小事务并且以批处理的形式执行(不错，实际经验就是如此)，使用低级别的隔离措施 (也没错，NOLOCK就是一个极端的例子)，还建议你有限的连接，从而让处理器进行合作(好复杂的描述，而且总觉得怪怪的不像个好点子)。我不知道是否用数据库咨询师会提到本文中的技术(或类似的技术)， 但是我只想说的是，Streamload.com的运行状况的确因为该技术得到了改善。如果你遇到了锁争用的问题，也可以试试NOLOCK和ROWLOCK。

申明

是否使用NOLOCK和ROWLOCK，需要自行判断，并谨慎运用。我用该技术的方法是通过查看我的存储过程和即时查询语句，在我自己的理解上来觉得哪里用和如何用。我需要判断如果用NOLOCK 而引起一些返回的不准确，或者ROWLOCK是否会造成太多的锁，这些情况出现时，对于访问者或者使用者来说，是否是可以接受的。在大多数情况下，我认为是没有问题的，但是也许你的代码不适用， 你需要小心对待。你需要创建一些独立的过程，是否加锁，如何加锁，以作为对比。当UPDATE或者 DELETE查询影响到很多数据行时，你在使用PAGELOCK,TABLOCK时也会遇到别的问题。
附：－－－－－－－－－－－－－－－　UPDLOCK　　读取表时使用更新锁，而不使用共享锁，并将锁一直保留到语句或事务的结束。UPDLOCK 的优点是允许您读取数据（不阻塞其它事务）并在以后更新数据，同时确保自从上次读取数据后数据没有被更改。　　这是SqlServer2000中对更新锁的说明.　　当我们用UPDLOCK来读取记录时可以对取到的记录加上更新锁，从而加上锁的记录在其它的线程中是不能更改的只能等本线程的事务结束后才能更改，我如下示例：BEGIN TRANSACTION --开始一个事务
SELECT Qty
FROM myTable WITH (UPDLOCK)
WHERE Id in (1,2,3)
UPDATE myTable SET Qty = Qty - A.Qty
FROM myTable AS A
INNER JOIN @_Table AS B ON A.ID = B.ID
COMMIT TRANSACTION --提交事务　　这样在更新时其它的线程或事务在这些语句执行完成前是不能更改ID是１，２，３的记录的．其它的都可以修改和读，１，２，３的只能读，要是修改的话只能等这些语句完成后才能操作.从而保证的数据的修改正确.



我帖上来我收藏,呵呵

赞

SQL77 2009-08-17

http://hi.baidu.com/kbkiss/blog/item/0d11baf357b85658352acc3e.html

楼主参考下

 回复 赞