说说SQL Server 2005中的一致性读

最新推荐文章于 2024-07-19 11:09:26 发布

czx33859066

最新推荐文章于 2024-07-19 11:09:26 发布

阅读量4.6k

点赞数

分类专栏： SQL

SQL 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

实验环境和代码 SQL Server 2000中的查询问题

查询的阻塞问题数据不一致的问题

SQL Server 2005的解决之道

事务级别的行版本管理

SNAPSHOT与查询阻塞 SNAPSHOT与读一致性

数据库级别的行版本管理

总结延伸阅读

实验环境和代码

本来实验的环境为

 
        SELECT 
         @@VERSION 
       
        --------------------------------------------------------------------------- 
       
        Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) 
       
        Feb  9 2007 22:47:07 
       
        Copyright (c) 1988-2005 Microsoft Corporation 
       
        Enterprise Edition  
        on 
         Windows NT 5.2 (Build 3790: Service Pack 2)

测试数据库的代码为：

 
        USE test 
       
        GO 
       
        DROP 
         TABLE 
         tmp 
       
        CREATE 
         TABLE 
         tmp (id  
        INT 
        , id2  
        INT 
        , n  
        int 
        , c  
        CHAR 
        (4000)) 
       
        DECLARE 
         @i  
        INT 
       
        SET 
         @i=0 
       
        WHILE @i<1000000 
       
        BEGIN 
       
        INSERT 
         INTO 
         tmp(id,id2,n, c) 
       
        SELECT 
         @i, @i, 1000,  
        'cccc' 
       
        SET 
         @i=@i+1 
       
        END 
       
        -- 建立索引，为的是在验证和修改数据时快点 
       
        CREATE 
         INDEX 
         ix_id2  
        ON 
         tmp(id2)

SQL Server 2000中的查询问题

查询的阻塞问题

从SQL Server 2000过来的人都知道，在SQL Server 2000中有一个令人郁闷的问题，那就是锁对查询的阻塞，就是当你一个表上做了些DML操作之后，但是没有提交，这时候另外一个进程要查询这些修改的数据的时候，你的查询进程会被阻塞，直到执行DML操作进程提交完了以后，你的查询才能得以继续进行下去。在一个表修改很少的时候等会儿也就等会儿了，但是当一个表被频繁的修改的时候，这些锁带来的问题会让你的系统变得巨慢，甚至是死掉（由于SQL Server是利用字典表来管理锁的，锁多了之后系统挂起是很常见的）。

还好，微软对这个问题并不是没有解的，为了让查询不会DML操作所阻塞，他们提供了两个提示来解决这个问题，那就是NOLOCK(等同于READUNCOMMITTED)和READPAST这两个提示。NOLOCK就是著名的脏读提示了，在查询后面使用了WITH (NOLOCK)提示之后，查询将不再为未提交的DML操作所阻塞，但是却会同时把那些没有提交的数据给查询出来，这其实是非常的不好的；而READPAST带来的效果也没有更好，相比NOLOCK提示来说，READPAST提示会跳过那些被锁住的行，之查询那些没有被锁的数据行，也就是说当你一个表锁在表上的时候你再使用READPAST去查询的话将得到一个空的结果，同样的这种解决方法也是非常不好的，但是在2000的时代，也就只能这么的将就了。

数据不一致的问题

查询被DML阻塞只是2000时代查询所存在的比较令人头疼的问题之一，另一个就是读数据一致性的问题了。

我们先举个例子来说明下什么是读数据的一致性。假设我们有一个银行账户信息表tmp，里面保存的是用户存款的信息，现在我们要对账户中的金额进行下统计，于是有了下面的操作：

 
        假设我们操作的表里面有1百万条记录，每条记录的金额都是1000 
       
        T1 P1进程发出求和的查询命令 
       
        T2 P1进程统计到了id从1到10000的记录 
       
        T2 同时另外一个进程P2发出一个命令，将id是900000的记录金额修改成了2000 
       
        T3 P1进程统计完毕，这时候统计的结果会是多少呢？

下面就用实际的实验结果来看看（数据采用前面提供的代码生成）：

我们现在一个进程里面发出一个统计的查询命令

 
   
        PRINT  
        'T1 is: ' 
        + 
        CONVERT 
        ( 
        VARCHAR 
        ,GETDATE(),120) 
       
 
        SELECT 
         SUM 
        (n)  
        FROM 
         tmp 
       
 
        PRINT  
        'T3 is: ' 
        + 
        CONVERT 
        ( 
        VARCHAR 
        ,GETDATE(),120) 
       
 
 

过会儿，再打开另外一个进程将id是900000的数据从1000修改为2000

 
   
        PRINT  
        'T2 is: ' 
        + 
        CONVERT 
        ( 
        VARCHAR 
        ,GETDATE(),120) 
       
 
        SELECT 
         *  
        FROM 
         tmp  
        WHERE 
         id2=900000 
       
 
        UPDATE 
         tmp  
        SET 
         n=2000  
        WHERE 
         id2=900000 
       
 
        SELECT 
         *  
        FROM 
         tmp  
        WHERE 
         id2=900000 
       
 
 

最后我们看到统计出来的结果如下

查询进程输出结果为：

 
        T1  
        is 
        : 2010-02-28 13:41:56 
       
        ----------- 
       
        1000001000 
       
        (1 row(s) affected) 
       
        T3  
        is 
        : 2010-02-28 13:42:39

更新数据进程输出的结果为：

 
        T2  
        is 
        : 2010-02-28 13:42:09 
       
        id          id2         n           c 
       
        ----------- ----------- ----------- --------------- 
       
        900000      900000      1000        cccc 
       
        (1 row(s) affected) 
       
        (1 row(s) affected) 
       
        id          id2         n           c 
       
        ----------- ----------- ----------- --------------- 
       
        900000      900000      2000        cccc 
       
        (1 row(s) affected)

很意外的，理论上我们在T1时刻发出统计命令时我们是希望得到的时候T1时刻表tmp里面的金额总和（1000000*1000），而实际上我们得到的结果却是T3时刻的结果（(1000000-1)*1000+2000），这与我们的要求相距甚远，这样的情况在实际的金融系统中是绝对不允许发生的。当然，2000时代解决方法也不是没有的，那就是在统计之前先将表锁上，不让有新的DML操作，统计完了再打开，但是这样造成的结果就是严重的牺牲了数据库的可用性，在这里读数据一致性和可用性是难以兼得的鱼和熊掌了。

SQL Server 2005的解决之道

幸好，微软没有停留在以前，从SQL Server 2005开始，他也能向数据库的霸主Oracle看齐，学着Oracle的undo机制，自己也创造了一套类似的基于行版本的事务管理机制，让前面提到的问题有一个好的解决之道。

简单来说SQL Server 2005的行版本管理机制就是将表中数据行的过去的每次修改都记录到tempdb中，这样当我们要查询一条未提交的数据记录时，可以直接到tempdb中的去找相应查询时刻的历史记录，从而避免被阻塞，也保证数据的一致性。

一如SQL Server对各种隔离级别的实现，SQL Server 2005中行版本管理也一样可以由事务级别的行版本管理和数据库级别的。

事务级别的行版本管理

SQL Server 2005中提供了一种新的隔离级别，叫做SNAPSHOT隔离级别，文档中对SNAPSHOT隔离级别是这么说的：

SNAPSHOT

指定事务中任何语句读取的数据都将是在事务开始时便存在的数据的事务上一致的版本。

事务只能识别在其开始之前提交的数据修改。在当前事务中执行的语句将看不到在当前事务开始以后由其他事务所做的数据修改。其效果就好像事务中的语句获得了已提交数据的快照，因为该数据在事务开始时就存在。

除非正在恢复数据库，否则 SNAPSHOT 事务不会在读取数据时请求锁。读取数据的 SNAPSHOT 事务不会阻止其他事务写入数据。写入数据的事务也不会阻止 SNAPSHOT 事务读取数据。

在数据库恢复的回滚阶段，如果尝试读取由其他正在回滚的事务锁定的数据，则 SNAPSHOT 事务将请求一个锁。在事务完成回滚之前，SNAPSHOT 事务会一直被阻塞。当事务取得授权之后，便会立即释放锁。

必须将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON，才能开始一个使用 SNAPSHOT 隔离级别的事务。如果使用 SNAPSHOT 隔离级别的事务访问多个数据库中的数据，则必须在每个数据库中将 ALLOW_SNAPSHOT_ISOLATION 都设置为 ON。

不能将通过其他隔离级别开始的事务设置为 SNAPSHOT 隔离级别，否则将导致事务中止。如果一个事务在 SNAPSHOT 隔离级别开始，则可以将它更改为另一个隔离级别，然后再返回 SNAPSHOT。事务在第一次访问数据时启动。

在 SNAPSHOT 隔离级别下运行的事务可以查看由该事务所做的更改。例如，如果事务对表执行 UPDATE，然后对同一个表发出 SELECT 语句，则修改后的数据将包含在结果集中。

下面就通过实验来验证SNAPSHOT隔离模式对于我们前面提到的两个问题是否能解决，实验之前，我们按照文档的要求将数据库的ALLOW_SNAPSHOT_ISOLATION设置为ON，使用下面的代码

 
        ALTER 
         DATABASE 
         TEST  
        SET 
         ALLOW_SNAPSHOT_ISOLATION  
        ON

SNAPSHOT与查询阻塞

先打开一个进程，执行下面的命令修改数据，但是不要提交

 
        BEGIN 
         TRAN 
       
        SELECT 
         *  
        FROM 
         tmp  
        WHERE 
         id2=100000 
       
        UPDATE 
         tmp  
        SET 
         n=2000  
        WHERE 
         id2=100000 
       
        -- ROLLBACK 
       
        -- COMMIT

然后打开另外一个进程查询我们刚刚修改了但未提交的数据

 
        -- 设置SNAPSHOT隔离模式 
       
        SET 
         TRANSACTION 
         ISOLATION 
         LEVEL 
         SNAPSHOT 
       
        -- 下面的查询不会被阻塞 
       
        SELECT 
         *  
        FROM 
         tmp  
        WHERE 
         id2=100000 
       
        -- 换回READ COMMITTED隔离模式 
       
        SET 
         TRANSACTION 
         ISOLATION 
         LEVEL 
         READ 
         COMMITTED 
       
        -- 这个时候查询又会被阻塞了 
       
        SELECT 
         *  
        FROM 
         tmp  
        WHERE 
         id2=100000

这回我们看到，问题顺利的解决了，再SNAPSHOT隔离模式下，我们的查询并没有被DML修改操作所阻塞。

SNAPSHOT与读一致性

同样的我们还是执行上面的统计总和的那些操作，不同的事，我们会在执行操作之前将隔离级别设置为SNAPSHOT。

先在一个进程里面发出一个统计的查询命令，和前面的测试一样的

 
   
        -- 设置SNAPSHOT隔离模式 
       
 
        SET 
         TRANSACTION 
         ISOLATION 
         LEVEL 
         SNAPSHOT 
       
 
        -- 开始数据查询操作 
       
 
        PRINT  
        'T1 is: ' 
        + 
        CONVERT 
        ( 
        VARCHAR 
        ,GETDATE(),120) 
       
 
        SELECT 
         SUM 
        (n)  
        FROM 
         tmp 
       
 
        PRINT  
        'T3 is: ' 
        + 
        CONVERT 
        ( 
        VARCHAR 
        ,GETDATE(),120) 
       
 
 

过会儿，再打开另外一个进程将id是900000的数据从1000修改为2000

 
   
        PRINT  
        'T2 is: ' 
        + 
        CONVERT 
        ( 
        VARCHAR 
        ,GETDATE(),120) 
       
 
        SELECT 
         *  
        FROM 
         tmp  
        WHERE 
         id2=900000 
       
 
        UPDATE 
         tmp  
        SET 
         n=2000  
        WHERE 
         id2=900000 
       
 
        SELECT 
         *  
        FROM 
         tmp  
        WHERE 
         id2=900000 
       
 
 

最后我们看到统计出来的结果如下

查询进程输出结果为：

 
        T1  
        is 
        : 2010-03-01 13:58:12 
       
        ----------- 
       
        1000000000 
       
        (1 row(s) affected) 
       
        T3  
        is 
        : 2010-03-01 13:58:54

更新数据进程输出的结果为：

 
        T2  
        is 
        : 2010-03-01 13:58:44 
       
        id          id2         n           c 
       
        ----------- ----------- ----------- -------- 
       
        900000      900000      1000        cccc 
       
        (1 row(s) affected) 
       
        (1 row(s) affected) 
       
        id          id2         n           c 
       
        ----------- ----------- ----------- -------- 
       
        900000      900000      2000        cccc 
       
        (1 row(s) affected)

这回我们看到，统计的结果是我们在T1时刻发出查询时候的结果了，不再是T3时刻数据库中数据的统计结果。我们顺利的通过设置SNAPSHOT达到了我们目的，不需要引入额外的表锁。

数据库级别的行版本管理

SQL Server 2005同时还提供一个名为READ_COMMITTED_SNAPSHOT的数据库选项，引用文档的说明如下：

READ_COMMITTED_SNAPSHOT { ON | OFF }

指定已提交读隔离级别的事务使用行版本控制而不是锁定。当事务在已提交读隔离级别运行时，所有的语句都将数据快照视为位于语句的开头。

OFF

指定 READ_COMMITTED 隔离级别的事务使用锁定。

也就是我们只需要将READ_COMMITTED_SNAPSHOT设置为ON的话，我们就可以可以在整个数据库级别使用行版本控制功能，不需要再给每个处理的进程都设置一下隔离级别了。

同样的，我们还是要用实验来验证一下，同样在实验之前要将我们的参数设置到位：

 
        -- 先关掉刚刚打开的这个参数 
       
        ALTER 
         DATABASE 
         TEST  
        SET 
         ALLOW_SNAPSHOT_ISOLATION  
        OFF 
       
        -- 打开READ_COMMITTED_SNAPSHOT参数 
       
        ALTER 
         DATABASE 
         TEST  
        SET 
         READ_COMMITTED_SNAPSHOT  
        ON