SQL Server2005 事务隔离级别
转载至http://yrq205.blog.163.com/blog/static/47672140201162504351429/
SQL2005中基于行版本控制的两种隔离级别
2011-07-25 12:43:51| 分类： 数据库知识 | 标签：行版本控制隔离级别 |字号大中小 订阅
--快照隔离级别(snapshot)和已提交读快照隔离级别(read committed snapshot)
--特点：在这两种隔离级别下，读取数据时不再请求共享锁，而且永远不会与修改进程的数据发生冲突，如果请求的
-- 行被锁定(例如正在被更新)，SQL_Server会从行版本存储区返回最早的关于该行的记录(SQL_server会在
-- 更新时将之前的行数据在tempdb库中形成一个链接列表,当然目前我没有搞清楚之前的数据到底存到了那里)
-- 这两个快照提供了乐观的并发模型
--说明：首先这两种隔离级别都是基于快照的实现模式，所以使用前必须修改数据库选项"允许快照隔离"为ON，否则
-- 以下两种隔离级别将都被禁用：
ALTER DATABASE DBNAME SET ALLOW_SNAPSHOT_ISOLATION ON
-- 修改这个选项时可能会需要将数据库置为单用户模式
ALTER DATABASE DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- 修改完允许快照隔离后再将数据库重置为多用户模式
ALTER DATABASE DBNAME set MULTI_USER
-- 一、快照隔离级别是一种全新的隔离级别，在打开“允许快照隔离”选项后，不管是否使用快照隔离级别
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
-- 在更新数据时，SQL SERVER总是会在tempdb库中保存更改前的最后的行数据链接列表,从这里可以想到
-- 将会影响SQL SERVER在更新数据时的事务性能
-- 当然，该隔离级别的主要作用是提高并发，所以在有读取数据的地方时，请使用
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
-- 二、已提交读快照隔离级别，说得明白点，其实就是SQL Server默认隔离级别已提交读的衍生品，或者说是
-- 另一种版本的已提交读，或者是官方的说法：是已提交读的新实现，官方的说法总是让我们不能仅凭字面
-- 意思就可以理解到本质。打开此数据库选项的命令是：
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;--首先我们打开该数据库选项(注意该选项需要
-- 上面提到的ALLOW_SNAPSHOT_ISOLATION选项的支持)
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;
--总结：快照隔离模式是乐观并发模型，可以避免脏读、丢失更新、不可重复读、幻读、而且有更新冲突检测的特点。
-- 已提交快照读隔离模式和已提交读模式是相同的，都是只能避免脏读，都无更新冲突检测，但是不同的是，已
-- 提交读快照隔离级别是乐观并发模型，并且读取数据不会发生等待。
--另附所有隔离级别的允许或防止的问题等。
==============================================================================================
隔离级别 脏读 丢失更新 不可重复读 幻读 并发模型 更新冲突检测
-------------------------------------------------------------------------------------------------------------
未提交读 是 是 是 是 悲观 否
-------------------------------------------------------------------------------------------------------------
已提交读 否 是 是 是 悲观 否
-------------------------------------------------------------------------------------------------------------
可重复读 否 否 否 是 悲观 否
-------------------------------------------------------------------------------------------------------------
可串行读 否 否 否 否 悲观 否
-------------------------------------------------------------------------------------------------------------
快照 否 否 否 否 乐观 是
-------------------------------------------------------------------------------------------------------------
已提交读快照 否 是 是 是 乐观 否
==============================================================================================
补充：Snapshot Isolation （快照隔离级别）和Read Committed Snapshot（已提交读快照隔离级别）
这两种隔离级别都是通过行版本控制在tempdb中生成数据的复本来解决数据的写和读的时候发生锁及阻塞的问题的。不过snapshot isolation需要在数据库中执行
ALTER DATABASE Aesop
SET ALLOW_SNAPSHOT_ISOLATION ON
之后，然后在执行事务之前，设置连接的隔离级别
SET TRANSACTION ISOLATION LEVEL Snapshot;
BEGIN TRAN
SELECT Title
FROM FABLE
WHERE FableID = 2
这时候当发生第二个事务对fableid=2的行进行更新的时候，它可以进行更新，但是在更新事务提交之后，查询事务依然是无法查到更新事务所做的修改，它还是只能查询到原始的数据，这种情况类似于repeatable read隔离级别，但是在repeatable read下，更新事务是无法更新的，直到查询事务提交之后才可以。
Read Committed Snapshot Isolation只是针对sqlserver默认的read committed隔离级别的。使用它需要执行如下sql:
ALTER DATABASE Aesop
SET READ_COMMITTED_SNAPSHOT ON
如果数据库只是普通的read committed级别下，当执行一个更新事务但没有提交时，再执行一个对更新数据进行查询的事务，查询事务将无法查询，被阻塞，但是在 READ_COMMITTED_SNAPSHOT被打开的情况下，在上面那种情况中，查询事务将不会被阻塞，它能够查询到未更新前的数据。
说句最俗的，Snapshot Isolation是针对SET TRANSACTION ISOLATION LEVEL Snapshot;的，在使用的时候需要在事务前设置隔离级别，而Read Committed Snapshot Isolation完全不需要使用set transaction isolation，因为它是针对数据默认的read committed隔离级别的。
