设置SQLServer的行版本控制隔离级别

1.--查询数据库状态
select name,user_access,user_access_desc,snapshot_isolation_state,snapshot_isolation_state_desc,is_read_committed_snapshot_on from sys.databases

2. 查看当前数据库的隔离级别

　　DBCC Useroptions -- isolation level 这项的值就代表当前的隔离级别

2。 更改数据库与乐观锁有关的参数 （必须关闭除了当前连接之外的所有的数据库连接 ）

ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
ALTER DATABASE [dbname] SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE [dbname] SET MULTI_USER;

2005支持5种隔离级别来控制读操作的行为，分别是：

  • Uncommitted Read
  • Read Committed
  • Repeatable Read
  • Snapshot
  • Serializable

其中三种是悲观模式下使用，一种（Snapshot）是乐观模式下使用，还有一种（Read Committed）俩种模式下都可用.

修改隔离级别的语句如下，只能修改一个链接的隔离级别，不能修改全局的默认隔离级别

SET TRANSACTION ISOLATION LEVEL    { READ UNCOMMITTED     | READ COMMITTED     | REPEATABLE READ     | SNAPSHOT     | SERIALIZABLE     } [ ; ]

通过如下命令设置乐观模式的改隔离级别：

1. ALTER DATABASE [dbname] SET ALLOW_SNAPSHOT_ISOLATION ON

2. set transaction isolation level snapshot

假如我们不执行step1，只执行step2，然后开启事务进行查询，会报如下错误：

Msg 3952, Level 16, State 1, Line 3
Snapshot isolation transaction failed accessing database 'AdventureWorks' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.

 

ALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT ON;

set transaction isolation level Read Committed

--由于SQL Server默认的isolation level 就是Read Committed,所以这句可以不需要,这就意味着当设置了数据库参数READ_COMMITTED_SNAPSHOT后，

隔离级别就自动变成了read committed snapshot.

两种行版本控制隔离级别的差别：

READ_COMMITTED_SNAPSHOT  数据库选项为ON时， READ_COMMITTED事务通过使用行版本控制提供语句级读取一致性.同时在执行DML语句的时候，会把snapshot数据自动存储在tempdb里面，哪怕当前session的隔离级别不是READ_COMMITTED.因为每个session的隔离级别是可以随时变的，所以只要数据库的这个选项设置为on的时候，就必须存储行版本数据，以提供行版本数据控制。


ALLOW_SNAPSHOT_ISOLATION 数据库选项为ON时，SNAPSHOT事务通过使用行版本控制提供事务级读取一致性。在执行DML的时候，会保持更多的行版本数据，以供需要行版本数据的snapshot隔离级别使用。所以这个可能会影响tempdb的使用。

 

行版本数据是在被更新的时候(DML),把前snapshot数据放到tempdb里面，可以在视图sys.dm_tran_version_store查到对于的记录.

select * from sys.dm_tran_version_store

 

 下面是一些实验例子

--认清SQL_Server的基于行版本控制的两种隔离级别
--快照隔离级别(snapshot)和已提交读快照隔离级别(read committed snapshot)

--特点：在这两种隔离级别下，读取数据时不再请求共享锁，而且永远不会与修改进程的数据发生冲突，如果请求的行被锁定(例如正在被更新)，
--SQL_Server会从行版本存储区返回最早的关于该行的记录

--说明：首先这两种隔离级别都是基于快照的实现模式，所以使用前必须修改数据库选项"允许快照隔离"为ON，否则

--        以下两种隔离级别将都被禁用：
        ALTER DATABASE DBNAME SET ALLOW_SNAPSHOT_ISOLATION ON
--        修改这个选项时可能会需要将数据库置为单用户模式
        ALTER DATABASE DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--        修改完允许快照隔离后再将数据库重置为多用户模式
        ALTER DATABASE DBNAME set MULTI_USER
--    一、快照隔离级别是一种全新的隔离级别，在打开“允许快照隔离”选项后，不管是否使用快照隔离级别--        在更新数据时，SQL SERVER总是会在tempdb库中保存更改前的最后的行数据链接列表,从这里可以想到
--        将会影响SQL SERVER在更新数据时的事务性能。当然，该隔离级别的主要作用是提高并发，所以读取多，修改少的时候可大胆使用.
        SET TRANSACTION ISOLATION LEVEL SNAPSHOT 
--    二、已提交读快照隔离级别，其实就是SQL Server默认隔离级别Read Committed的衍生品，或者说是
--        另一种版本的已提交读，打开此数据库选项的命令是：
        ALTER DATABASE DBNAME SET read_committed_snapshot ON
--        在这里大家要明白，它只是一个数据库选项开关，是在 READ COMMITTED 隔离模式下时才会起作用，而且
--        将改变整个数据库的全局行为。因为SQL SERVER默认就是在READ COMMITTED隔离模式下，所以在稍后的
--        示例中我们不会用到SET TRANSACTION ISOLATION LEVEL READ COMMITTED语句，但是我们心里要明白。

--适用情况：主要是读取数据的环境，在这种环境下偶尔需要修改操作并且很少发生更新冲突。
--区别：我们会在稍后的演示中进行说明，那样更容易理解一些。
--示例一：快照
--创建环境：
    IF DB_ID('DB_TEST') IS NOT NULL DROP DATABASE DB_TEST;
    GO
    CREATE DATABASE DB_TEST
    USE DB_TEST;
    GO
    IF OBJECT_ID('T_TEST','U') IS NOT NULL DROP TABLE T_TEST
    GO
    CREATE TABLE T_TEST(ID INT IDENTITY(1,1),COL VARCHAR(50))
    GO
    INSERT INTO T_TEST SELECT 'AAAAAAAAA' UNION ALL SELECT 'BBBBBBBBBB'
    GO
    SELECT * FROM T_TEST
    /*
        ID    COL
        1    AAAAAAAAA
        2    BBBBBBBBBB
    */
--    在连接1中执行如下语句（确保ALLOW_SNAPSHOT_ISOLATION已置为ON）
    USE DB_TEST;
    GO
    ALTER DATABASE DB_TEST SET ALLOW_SNAPSHOT_ISOLATION ON;
    GO
    BEGIN TRAN
        SELECT COL FROM T_TEST WHERE ID=2;
        UPDATE T_TEST SET COL='CCCCCCC' WHERE ID=2;
        SELECT COL FROM T_TEST WHERE ID=2;
--    通过输出的结果我们可以看到，在未完成的事务中ID=2的COL值从'B'变为'C',而且你应该注意到我这里没有使用快照隔离级别，还是用的SQL SERVER默认隔离级别，--    但是因为我们打开了ALLOW_SNAPSHOT_ISOLATION选项，这个时候，我们的事务应该在更改ID=2的COL值之前就把之前的行状态存储到了tempdb中,
--    那么我们怎么才能证明这个猜测呢，动态视图sys.dm_tran_version_store可以帮助我们，执行：
    SELECT * FROM sys.dm_tran_version_store
--    你一定可以看到在版本存储区中已经有了一行数据了，接下来我们再打开一个连接2，执行如下SQL:
    USE DB_TEST
    GO
    --SET TRANSACTION ISOLATION LEVEL SNAPSHOT;    --这里我们先注释掉设置隔离级别为快照模式
    BEGIN TRAN
        SELECT COL FROM T_TEST WHERE ID=2;
--    可以看到查询一直在等待，是因为我们在连接1中一直保持着该行的排它锁X。但是现在我们把该事务commit或rollback
--    掉，然后把快照隔离模式的注释打开，重新执行上面的语句，我们就可以看到
    /*
        BBBBBBBBBB
    */
--    我们可以想象到SQL SERVER在这种隔离级别下的查找思路，它会先去原表查找该行数据，待发现该行被锁后，则去
--    tempdb数据库存储的行版本列表中取出最近的一次数据，这样就避免了等待，但是前提是要求数据查询不用那么精确
--    的情况下，当然，你是否在这里忽略了一个问题，即：SQL SERVER仅会在修改该行数据前才会去存储最新的行版本，
--    而在修改的事务结束后，SQL SERVER并不会去更新之前的快照到最新的行版本，但是即使这样我们也不用担心，因为
--    这个时候原表的该行数据已经不是锁定状态，其他之后的查询依然会得到最新的数据。唯一注意的一点，我们还是用
--    代码说明：在连接1中用COMMIT TRAN 提交事务，然后继续执行连接2中的查询：
    SELECT COL FROM T_TEST WHERE ID=2;
--    我们发现数据还是之前的数据BBBBBBB，为什么？因为事务隔离级别！在事务中的任何地方读取该行数据时，它获取的
--    总是在事务开始时获取的数据，这里要牢记，因为他是稍后我们要说的已提交读快照隔离级别的第一个不同点。


--    接下来我们说说快照隔离级别的另一个特点：冲突检测，代码说明，简洁易懂：
--    在连接1中执行如下语句：
    USE DB_TEST;
    GO
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;--注意这里我们要设置隔离级别为快照模式
    BEGIN TRAN
        SELECT COL FROM T_TEST WHERE ID=2;
--    这里我们可以得到一个数据，然后再打开一个连接2，执行如下SQL:
    USE DB_TEST;
    GO
    UPDATE T_TEST SET COL='DDDDDDD' WHERE ID=2;
--    回到连接1，继续执行SQL:
    UPDATE T_TEST SET COL='EEEEEEE' WHERE ID=2;
--    这时SQL SERVER 就会检测到你在连接1中事务开始时读取的数据已经与现在的数据发生了改变，所以就会报出更新
--    冲突的错误：
    /*
    消息 3960，级别 16，状态 4，第 1 行
    快照隔离事务由于更新冲突而中止。您无法在数据库'DB_Test'中使用快照隔离来直接或间接访问表 'dbo.T_TEST'，
以便更新、删除或插入已由其他事务修改或删除的行。请重试该事务或更改 update/delete 语句的隔离级别。
    */
--    这里，其实就是快照隔离级别和已提交读快照隔离级别的第二大区别了，READ COMMITTED SNAPSHOT不会检测更新冲突


--示例二：已提交读快照
--    在连接1中执行如下语句：
    ALTER DATABASE DB_TEST SET READ_COMMITTED_SNAPSHOT ON;--首先我们打开该数据库选项
    USE DB_TEST;
    GO;
    BEGIN TRAN
        UPDATE T_TEST SET COL='FFFFFFF' WHERE ID=2;
        SELECT COL FROM T_TEST WHERE ID=2;
--    在该事务里，你将得到你刚刚更新过的值FFFFFFFF
--    在连接2中执行如下语句：
    USE DB_TEST;
    GO
    BEGIN TRAN
        SELECT COL FROM T_TEST WHERE ID=2;
--    这里你将得到连接1中的事务在修改数据之前的值，而非FFFFFF，这是肯定的。
--    这时我们提交连接1中的事务：
    COMMIT TRAN;
--    在连接2中再进行查询时，我们惊奇的发现与在快照中不同的是，我们竟然在未完成的事务2中得到了连接1中的事务
--    更改后的值！这也是为什么不会进行更新冲突检测的原因，不如我们测试一下：
--    将之前连接1中的事务提交或回滚，然后执行如下SQL:
    USE DB_TEST;
    GO
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;--这里我们显示指定隔离级别是因为刚才指定的快照隔离
-- 级别会在没有关闭的会话中一直有效。
    BEGIN TRAN
        SELECT COL FROM T_TEST WHERE ID=2;
--之后，我们再把连接2中的事务提交或回滚掉，执行如下SQL：
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    UPDATE T_TEST SET COL='aaaaa' WHERE ID=2;
    SELECT COL FROM T_TEST WHERE ID=2;
--    好了，这个时候我们再在连接1中更新这条在事务1中读取后但是在外部被更新过的数据：
    UPDATE T_TEST SET COL='测试已提交读更新冲突检测' WHERE ID=2;
--    我们发现更新可以正常进行，最后我们关闭所有连接，并更改数据库选项：
    ALTER DATABASE DB_TEST SET ALLOW_SNAPSHOT_ISOLATION OFF;
    ALTER DATABASE DB_TEST SET READ_COMMITTED_SNAPSHOT OFF;

--总结：快照隔离模式是乐观并发模型，可以避免脏读、丢失更新、不可重复读、幻读、而且有更新冲突检测的特点。
--已提交快照读隔离模式和已提交读模式是相同的，都只能避免脏读，都无更新冲突检测，但是不同的是，已提交读快照隔离级别是乐观并发模型，并且读取数据不会发生等待
--另附所有隔离级别的允许或防止的问题等。
==============================================================================================
隔离级别        脏读        丢失更新        不可重复读    幻读        并发模型        更新冲突检测
----------------------------------------------------------------------------------------------
未提交读        是            是            是        是        悲观                否
----------------------------------------------------------------------------------------------
已提交读     　　否            是            是        是       悲观                 否
----------------------------------------------------------------------------------------------
可重复读        否            否            否        是        悲观                否
----------------------------------------------------------------------------------------------
可串行读        否            否            否        否        悲观                 否
----------------------------------------------------------------------------------------------
快照           否            否            否        否        乐观                 是
----------------------------------------------------------------------------------------------
已提交读快照    否            是            是        是        乐观                 否
==============================================================================================

DDL Statements That Are Not Allowed Within Snapshot Isolation

The following statements are not allowed within a transaction that is running under snapshot isolation because of their disruptive potential on the snapshot copies of the data:

  • CREATE INDEX
  • CREATE XML INDEX
  • ALTER INDEX
  • ALTER TABLE
  • DBCC DBREINDEX
  • ALTER PARTITION FUNCTION
  • ALTER PARTITION SCHEME
  • DROP INDEX
  • Common language runtime (CLR) DDL

An attempt to run one of these statements will cause a severity level 16 message to be returned, such as:

Msg 3964, Level 16, State 1, Line 1
Transaction failed because this DDL statement is not allowed
inside asnapshot isolation transaction. Since metadata is not
versioned,a metadata change can lead to inconsistency 
if mixed within snapshot isolation.

refer：
SQL Server 2005 Row Versioning-Based Transaction Isolation   http://msdn.microsoft.com/en-us/library/ms345124%28SQL.90%29.aspx
Using Row Versioning-based Isolation Levels  http://msdn.microsoft.com/en-us/library/ms189050.aspx
posted @ 2014-12-27 22:49  princessd8251  阅读(5305)  评论(0)  编辑  收藏  举报