视图后面加with(nolock)后

原文出处：http://www.cnblogs.com/ASPNET2008/archive/2012/02/09/2343538.html

      一些小的企业项目，往往存在很多局限性，这里讲的局限性是指这些项目在面对某些需求时，所选用的解决方案不多。就拿我目前处理这个来讲吧，说的通俗点就是为业务部门生成相关报表。我目前理解的报表生成方式，总结如下：
      
      第一：在业务库上执行SQL语句或者存储过程，实时生成数据。这其中又分为三种：
      
       1：在主库上操作，及业务系统操作的数据库与生成报表的SQL逻辑同处一个数据库。
       2: 在主库的只读库即从库上操作，一般可以通过订阅复制来设置主从库，产生报表的逻辑只操作从库。
       3: 主库定时生成一个备份库，报表从生成的备份库中操作。
       
      第二：由数据仓库生成相应报表。
          这部分在这里就不多说了。
          
      我目前的项目就属于第一种的第一小分类，即直接在主库上以执行SQL方式产生数据，其中的原因也不用多说，低成本。但由于生成报表的需要的时间一般都较长，容易对业务表进行长时间的锁定，影响业务系统的正常使用，反过来，如果用户正在操作业务库，此时如果同时执行报表生成，就会形成锁等待。为此决定对报表相关查询表进行无锁操作，即在表后面加 with(nolock)，但我们为客户端是以视图形式提供，为此产生一个问题：在视图后面加with(nolock),是否意味着视图内的所有表都是nolock?
      
      我在网上没找到相关内容，于是还是眼见为实，决定做个小实验。重点需要搞清楚如下几个问题：
      
      问题一：如何人为造成一个锁？即让某个表被某个线程长时间锁定。

      我们可以BEGIN TRAN mytran，然后操作一个表，比如update，但不commit tran,此时被操作的表就会被当前线程长时间锁定。我们可以用sp_who2来查询锁定情况，如下图所示，第一个框代表状态，第二个框是CPUTime。还可以利用sp_lock查看锁的类型。

      
      问题二：如何制造脏读的情况，我们都说加了nolock，容易形成脏读，但脏读到底怎么样呢？
      
      1:创建一个测试用的表：
      

-- 创建表
CREATE  TABLE mytest
    (
      id  INT  IDENTITY
              NOT  NULL ,
      data  UNIQUEIDENTIFIER  DEFAULT (  NEWID() )
                             NOT  NULL
    )

 

    这里需要创建一个聚焦索引，如果不创建此聚焦索引，后续的结果会发生变化。
    

-- 创建索引
CREATE  UNIQUE  CLUSTERED  INDEX cidx  ON mytest( data )

 

     2：为表添加一些测试数据。
   

-- 添加数据
DECLARE  @i  INT ;
SET  @i  =  1 ;
WHILE  @i  <=  50000 
     BEGIN
         INSERT  mytest
                 DEFAULT  VALUES  
         SET  @i  =  @i  +  1
     END

    

     3:创建测试视图：
    

CREATE  VIEW view_mytest
AS
   SELECT  *  FROM mytest
go

 

    4：创建一个嵌套视图，嵌套视图的目的就是想看看在最上层视图上加nolock，是否会传递到最底层的基表。
   

CREATE  VIEW view_mytest2
AS
   SELECT a. *  FROM view_mytest a
   LEFT  JOIN dbo.Report b
   ON a.id =b.id
go

 

     5：在MSMS中打开两个窗口：
      窗口一：执行如下脚本，提交一个事务，更新测试表，但不提交，形成长时间锁定。
      

BEGIN  TRAN mytran
UPDATE  mytest
SET     data  =  NEWID()

 

      窗口二:对测试表进行行数统计，当发现数据出现错误时，打印出提示信息。      

DECLARE @totalrows INT ,

    @currentnow INT ,

    @errorcount TINYINT

SET @errorcount = 0

SELECT  @totalrows = COUNT(1)

FROM    mytest 

WHILE 1 = 1 

    BEGIN

        SELECT  @currentnow = COUNT(1)

        FROM    view_mytest2 WITH ( NOLOCK )  

        IF @totalrows <> @currentnow 

            BEGIN

                PRINT '查询总数= ' + CAST(@currentnow AS VARCHAR(10)) + ' 差异数量= '

                    + CAST(@currentnow - @totalrows AS VARCHAR(10))

                SET @errorcount = @errorcount + 1

                IF @errorcount >= 8 

                    BREAK

            END

    END

      6：查看窗口二，如果nolock,理论上应该会出现错误提示信息，出现此错误提示信息就是因为在无锁情况下有可能出现多读或者是少读的现象。

    

    

      上图表示，在视图上添加nolock跟在基表后加nolock效果一样，只不过在视图上加就相当于视图内所有表都无锁，这样不太灵活，尽管有脏读现象，但我们的业务逻辑一般都是多表相匹配后的情况，如果一个事务中会操作多个表，生成报表时，某些表形成了脏读，也不会影响最终数据，如果所有表都操作完毕，还未提交事务，此时虽然查询出的数据可能是错误数据，但属于可接受范围之内。

  

      文中若不错误地方望批评指正。 

   

      参考资料：http://www.dotblogs.com.tw/ricochen/archive/2011/04/15/22758.aspx