龙岗区住房和建设局网站,图案设计制作,宁德建设银行网站,域名网站建设方案书模板--认清SQL_Server_2005的基于行版本控制的两种隔离级别--By:zc_0101 Date:2010-03-31--快照隔离级别(snapshot)和已提交读快照隔离级别(read committed snapshot)--特点#xff1a;在这两种隔离级别下#xff0c;读取数据时不再请求共享锁#xff0c;而且永远不会与修改进程… --认清SQL_Server_2005的基于行版本控制的两种隔离级别 --By:zc_0101 Date:2010-03-31--快照隔离级别(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 ID2; UPDATE T_TEST SET COLCCCCCCC WHERE ID2; SELECT COL FROM T_TEST WHERE ID2;-- 通过输出的结果我们可以看到在未完成的事务中ID2的COL值从B变为C,而且你应该注意到我这里没有使用-- 快照隔离级别还是用的SQL SERVER默认隔离级别但是因为我们打开了ALLOW_SNAPSHOT_ISOLATION选项这-- 个时候我们的事务应该在更改ID2的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 ID2;-- 可以看到查询一直在等待是因为我们在连接1中一直保持着该行的排它锁X。但是现在我们把该事务commit或rollback-- 掉然后把快照隔离模式的注释打开重新执行上面的语句我们就可以看到 /* BBBBBBBBBB */-- 我们可以想象到SQL SERVER在这种隔离级别下的查找思路它会先去原表查找该行数据待发现该行被锁后则去-- tempdb数据库存储的行版本列表中取出最近的一次数据这样就避免了等待但是前提是要求数据查询不用那么精确-- 的情况下当然你是否在这里忽略了一个问题即SQL SERVER仅会在修改该行数据前才会去存储最新的行版本-- 而在修改的事务结束后SQL SERVER并不会去更新之前的快照到最新的行版本但是即使这样我们也不用担心因为-- 这个时候原表的该行数据已经不是锁定状态其他之后的查询依然会得到最新的数据。唯一注意的一点我们还是用-- 代码说明在连接1中用COMMIT TRAN 提交事务然后继续执行连接2中的查询 SELECT COL FROM T_TEST WHERE ID2;-- 我们发现数据还是之前的数据BBBBBBB为什么因为事务隔离级别在事务中的任何地方读取该行数据时它获取的-- 总是在事务开始时获取的数据这里要牢记因为他是稍后我们要说的已提交读快照隔离级别的第一个不同点。-- 接下来我们说说快照隔离级别的另一个特点冲突检测代码说明简洁易懂-- 在连接1中执行如下语句 USE DB_TEST; GO SET TRANSACTION ISOLATION LEVEL SNAPSHOT;--注意这里我们要设置隔离级别为快照模式 BEGIN TRAN SELECT COL FROM T_TEST WHERE ID2;-- 这里我们可以得到一个数据然后再打开一个连接2执行如下SQL: USE DB_TEST; GO UPDATE T_TEST SET COLDDDDDDD WHERE ID2;-- 回到连接1继续执行SQL: UPDATE T_TEST SET COLEEEEEEE WHERE ID2;-- 这时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 COLFFFFFFF WHERE ID2; SELECT COL FROM T_TEST WHERE ID2;-- 在该事务里你将得到你刚刚更新过的值FFFFFFFF-- 在连接2中执行如下语句 USE DB_TEST; GO BEGIN TRAN SELECT COL FROM T_TEST WHERE ID2;-- 这里你将得到连接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 ID2;--之后我们再把连接2中的事务提交或回滚掉执行如下SQL SET TRANSACTION ISOLATION LEVEL READ COMMITTED; UPDATE T_TEST SET COLaaaaa WHERE ID2; SELECT COL FROM T_TEST WHERE ID2;-- 好了这个时候我们再在连接1中更新这条在事务1中读取后但是在外部被更新过的数据 UPDATE T_TEST SET COL测试已提交读更新冲突检测 WHERE ID2;-- 我们发现更新可以正常进行最后我们关闭所有连接并更改数据库选项 ALTER DATABASE DB_TEST SET ALLOW_SNAPSHOT_ISOLATION OFF; ALTER DATABASE DB_TEST SET READ_COMMITTED_SNAPSHOT OFF;--总结快照隔离模式是乐观并发模型可以避免脏读、丢失更新、不可重复读、幻读、而且有更新冲突检测的特点。-- 已提交快照读隔离模式和已提交读模式是相同的都是只能避免脏读都无更新冲突检测但是不同的是已-- 提交读快照隔离级别是乐观并发模型并且读取数据不会发生等待。--另附所有隔离级别的允许或防止的问题等。隔离级别 脏读 丢失更新 不可重复读 幻读 并发模型 更新冲突检测----------------------------------------------------------------------------------------------未提交读 是 是 是 是 悲观 否----------------------------------------------------------------------------------------------已提交读 否 是 是 是 悲观 否----------------------------------------------------------------------------------------------可重复读 否 否 否 是 悲观 否----------------------------------------------------------------------------------------------可串行读 否 否 否 否 悲观 否----------------------------------------------------------------------------------------------快照 否 否 否 否 乐观 是----------------------------------------------------------------------------------------------已提交读快照 否 是 是 是 乐观 否 转载于:https://www.cnblogs.com/qanholas/archive/2012/01/04/2312324.html
