设置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

第一种情况：

READ   COMMITTED

这句的作用是：

指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻像数据。该选项是   SQL   Server   的默认值。

第二种情况：

 READ   UNCOMMITTED

这句的作用是：

执行脏读或0级隔离锁定，这表示不发出共享锁，也不接受排它锁。当设置该选项时，可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。

第三种情况：

REPEATABLE   READ

这句的作用是：

锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。

第四种情况：

SERIALIZABLE

这句的作用是：

 在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是四个隔离级别中限制最大的级别。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有  SELECT   语句中的所有表上设置   HOLDLOCK   相同。

其中三种是悲观模式下使用，一种（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' becausesnapshot 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.

原文来自：http://www.cnblogs.com/princessd8251/p/4188947.html

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