sql server 使用基于行版本控制的隔离级别初探.docx

sql server 使用基于行版本控制的隔离级别初探.docx

《sql server 使用基于行版本控制的隔离级别初探.docx》由会员分享，可在线阅读，更多相关《sql server 使用基于行版本控制的隔离级别初探.docx（12页珍藏版）》请在冰豆网上搜索。

sqlserver使用基于行版本控制的隔离级别初探

SQLServer2005使用基于行版本控制的隔离级别初探

      最近回归本行，开始数据库的研究。

虽然不是9R2，但是好歹SQLServer的傻瓜式配置也不是浪得虚名，也算混的勉强过关把。

不过仔细研究了下2005的行版本控制这块做的确实不错，下面把我研究的共享，欢迎批判。

行版本控制隔离：

简介

         行版本控制的隔离是SQLServer2005一个新的隔离框架。

使用行版本控制的隔离可以在大量并发的情况下，显著减少所得产生，并且与NoLock相比，它又可以显著降低肮脏读，幻影，丢失更新等现象的发生（READ_COMMITTED_SNAPSHOT）

       当在基于行版本控制的隔离下运行的事务读取数据时，读取操作不会获取正被读取的数据上的共享锁（S锁），因此不会阻塞正在修改数据的事务。

另外，锁定资源的开销随着所获取的锁的数量的减少降至最低。

使用行版本控制的已提交读隔离和快照隔离可以提供副本数据的语句级或事务级读取一致性。

行版本控制隔离：

优势

使用行版本控制的隔离级别具有以下优点：

·        读取操作检索一致的数据库快照。

·        SELECT语句在读取操作过程中不锁定数据（读取器不阻塞编写器，编写器也不阻塞读取器）。

·        SELECT语句可以在其他事务更新行时访问最后提交的行值，而不阻塞应用程序。

·        死锁的数量减少。

·        事务所需的锁的数量减少，这减少了管理锁所需的系统开销。

·        锁升级的次数减少。

行版本控制隔离：

原理

SQLServer2005的行版本控制原理上很简单，就是在库表中每一行的记录上都悄悄的增加了一个类时间戳列（行版本列）。

当使用行版本控制的隔离时，SQLServer2005DatabaseEngine 向使用行版本控制操作数据的每个事务分配一个事务序列号（XSN）。

事务在执行BEGINTRANSACTION语句时启动。

但是，事务序列号在执行BEGINTRANSACTION语句后的第一次读/写操作时开始增加。

事务序列号在每次分配时都增加1。

当事务执行时，SQLServer根据行版本列，来提供的行的相应版本。

       而SQLServer将维护所有在数据库中执行的数据修改的逻辑副本（版本）。

特定的事务每次修改行时，数据库引擎 实例都存储以前提交的tempdb中行的图像版本。

每个版本都标记有进行此更改的事务的事务序列号。

已修改行的版本使用链接列表链接在一起。

最新的行值始终存储在当前的数据库中并链接至版本存储区tempdb中存储的版本。

（修改大型对象（LOB）时，只有已更改的片段才会复制到tempdb中的版本存储区, 对于短期运行的事务，已修改行的版本将可能保存在缓冲池中，而不会写入tempdb数据库的磁盘文件中。

如果只是临时需要副本行，它将只是简单地从缓冲池中删除而不会引发I/O开销。

）

      MSDN上一再强调要关注提升tempdb的地位，不然因为tempdb的性能导致整个数据库性能下降，可就是无妄之灾了。

行版本控制隔离：

种类

            行版本控制分为两种已提交读快照隔离级别（READ_COMMITTED_SNAPSHOT）和快照隔离级别（ALLOW_SNAPSHOT_ISOLATION），他们属于行版本控制的隔离的范畴，但是实际上他们却有方方面面的不同。

下面是MSDN对他们的比较。

属性

已提交读快照隔离级别

快照隔离级别

必须设置为ON以便启用所需支持的数据库选项。

READ_COMMITTED_SNAPSHOT

ALLOW_SNAPSHOT_ISOLATION

会话如何请求特定类型的行版本控制。

使用默认的已提交读隔离级别，或运行SETTRANSACTIONISOLATIONLEVEL语句来指定READCOMMITTED隔离级别。

这可以在事务启动后完成。

需要执行SETTRANSACTIONISOLATIONLEVEL来在事务启动前指定SNAPSHOT隔离级别。

由语句读取的数据的版本。

在每条语句启动前提交的所有数据。

在每个事务启动前提交的所有数据。

如何处理更新。

从行版本恢复到实际的数据，以选择要更新的行并使用选择的数据行上的更新锁。

获取要修改的实际数据行上的排他锁。

没有更新冲突检测。

使用行版本选择要更新的行。

尝试获取要修改的实际数据行上的排他锁，如果数据已被其他事务修改，则出现更新冲突，同时快照事务也将终止。

有更新冲突检测。

无。

集成支持。

无法禁用。

读取数据时的行为

SNAPSHOT隔离就像真实的快照，它会无视涉及行的变化。

在SNAPSHOT隔离下运行的事务将读取数据，然后由另一事务修改此数据。

SNAPSHOT事务不阻塞由其他事务执行的更新操作，它忽略数据的修改继续从版本化的行读取数据。

但是，当快照事务尝试修改已由其他事务修改的数据时，SNAPSHOT事务将生成错误并终止.

READ_COMMITTED_SNAPSHOT类似与以前的READ_COMMITTED隔离级别，

也就是说与快照事务相同的是，即使其他事务修改了数据，已提交读事务也将读取版本化的行。

然而，与快照事务不同的是，已提交读将执行下列操作：

·        在其他事务提交数据更改后，读取修改的数据。

·        能够更新由其他事务修改的数据，而快照事务不能。

修改数据时的行为

在使用行版本控制的已提交读事务中，使用阻塞性扫描（其中读取数据值时将在数据行上采用更新锁（U锁）完成选择要更新的行。

这与不使用行版本控制的已提交读事务相同。

如果数据行不符合更新标准，在该行上将释放更新锁并且将锁定下一行并对其进行扫描。

在快照隔离下运行的事务对数据修改采用乐观方法：

直到数据被修改时才获取数据上的锁。

不需要获取锁就可以选择要更新的行。

当数据行符合更新标准时，快照事务将验证未被并发事务（在快照事务开始后提交）修改的数据行。

如果数据行已在快照事务以外修改，则将出现更新冲突，同时快照事务也将终止。

更新冲突由数据库引擎 处理，无法禁用更新冲突检测。

SQLServer2005使用基于行版本控制的隔离级别初探

（2）--READ_COMMITTED_SNAPSHOT 

 上一篇大概介绍了行版本隔离的概念，并简要的介绍了两种行版本隔离异同。

下面我们就一步一步来实践把。

来回顾一下READ_COMMITTED_SNAPSHOT的构架。

在读取数据时不使用锁，但是在事务中可以更新被其他事务修改的数据。

        在修改数据时，使用更新锁（U锁）完成选择要更新的行。

这与SQLServer2k默认的READCOMMITTED隔离层策略一致。

        READ_COMMITTED_SNAPSHOT的强项在于查询的时候不会下锁，影响别的事务操作，

而且可以保障查询的数据满足绝大多数的精确要求。

试想一下，当一个长时间运行的查询或聚合时，无数事务苦苦等待的惨象把。

由此可见，SQLServer2k5小组煞费苦心的使用了版本列，tempdb等一系列手段，就是为了实现在可接受的隔离层度上，尽量降低锁带来的一系列并发瓶颈。

试想一下，只要付出小小的代价（保障tempdb，数据文件会变大那么一点，查询时会小小慢那么一点），数据库的并发行就会大大的增加。

^_^，世界时多么的安逸啊…（其实这也是很多企业级应用由SQLSever2k转到2k5的一个重要原因。

设置READ_COMMITTED_SNAPSHOT：

设置READ_COMMITTED_SNAPSHOT隔离模式很简单，只要我们简单的一步操作就可以实现。

   ALTERDATABASEDATABASE_NAME

SETREAD_COMMITTED_SNAPSHOTON;

OK！

很简单对吧，READ_COMMITTED_SNAPSHOT隔离模式的机制立即被激活了。

P.S:

READ_COMMITTED_SNAPSHOT无法在tempdb、msdb或master中启用。

READ_COMMITTED_SNAPSHOT的使用：

下面是使用READ_COMMITTED_SNAPSHOT的一个例子：

USEAdventureWorks;

GO

--显示声明使用READ_COMMITTED_SNAPSHOT

SETTRANSACTIONISOLATIONLEVELREADCOMMITTED;

GO

--一个简单的例子

BEGINTRANSACTION;

   SELECTEmployeeID,VacationHours

       FROMHumanResources.Employee

       WHEREEmployeeID=4;

READ_COMMITTED_SNAPSHOT的演示：

下面演示一个使用AdventureWorks数据库的READ_COMMITTED_SNAPSHOT的例子，方便大家理解READ_COMMITTED_SNAPSHOT

在此示例中，使用行版本控制的已提交读事务与其他事务并发运行。

已提交读事务的行为与快照事务的行为有所不同。

与快照事务相同的是，即使其他事务修改了数据，已提交读事务也将读取版本化的行。

然而，与快照事务不同的是，已提交读将执行下列操作：

在其他事务提交数据更改后，读取修改的数据。

能够更新由其他事务修改的数据，而快照事务不能。

在会话1上：

USEAdventureWorks;

GO

--在数据库上开启READ_COMMITTED_SNAPSHOT。

ALTERDATABASEAdventureWorks

   SETREAD_COMMITTED_SNAPSHOTON;

GO

--开始一个read-committed级别的事务

SETTRANSACTIONISOLATIONLEVELREADCOMMITTED;

GO

BEGINTRANSACTION;

--选择EmployeeID号为4的员工的休假资料

   SELECTEmployeeID,VacationHours

       FROMHumanResources.Employee

       WHEREEmployeeID=4;

在会话2上：

USEAdventureWorks;

GO

--开始一个事务

BEGINTRANSACTION;

--我们修改了EmployeeID为4的员工的休假资料

   UPDATEHumanResources.Employee

       SETVacationHours=VacationHours-8

       WHEREEmployeeID=4;

--确认下现在EmployeeID为4的员工的休假资料

   SELECTVacationHours

       FROMHumanResources.Employee

       WHEREEmployeeID=4;

在会话1上：

--因为会话2的事务没有提交，

--EmployeeID为4的员工的休假资料因该没变

SELECTEmployeeID,VacationHours

       FROMHumanResources.Employee

       WHEREEmployeeID=4;

在会话2上：

--提交我们的更新

COMMITTRANSACTION;

GO

在会话1上：

--OK，现在看看小4同志的资料

   SELECTEmployeeID,VacationHours

       FROMHumanResources.Employee

       WHEREEmployeeID=4;

--现在我们也来尝试下修改小4同志的资料

   UPDATEHumanResources.Employee

       SETSickLeaveHours=SickLeaveHours-8

       WHEREEmployeeID=4;

--ROLLBACK之后小4的数据到底是什么？

你知道吗？

ROLLBACKTRANSACTION;

GO

上一篇介绍了READ_COMMITTED_SNAPSHOT的构架。

下面我们来学习实践SNAPSHOT 行版本隔离级别把。

 回顾一下SNAPSHOT的构架：

       SNAPSHOT隔离就像真实的快照，它会无视涉及行的变化。

在SNAPSHOT隔离下运行的事务将读取数据，然后由另一事务修改此数据。

SNAPSHOT事务不阻塞由其他事务执行的更新操作，它忽略数据的修改继续从版本化的行读取数据。

但是，当快照事务尝试修改已由其他事务修改的数据时，SNAPSHOT事务将生成错误并终止.

       相比READ_COMMITTED_SNAPSHOT，SNAPSHOT真正做到了快照隔离，完全无视数据的更新。

相对READ_COMMITTED_SNAPSHOT，它更进一步减轻了对锁的依赖，在性能方面获得了更大的优势。

不可避免的是，SNAPSHOT的事务性也变得更差，但是，至少，它比NoLock要好。

^_^

 SNAPSHOT的限制：

SNAPSHOT比READ_COMMITTED_SNAPSHOT更快，但是坏消息是它的限制也多。

         1．快照隔离不支持分布式事务，包括分布式分区数据库中的查询。

         2．SQLServer不会保留多个版本的系统元数据。

表中的数据定义语言（DDL）语句和其他数据库对象（索引、视图、数据类型、存储过程和公共语言运行时函数）会更改元数据。

如果DDL语句修改一个对象，那么在快照隔离下对该对象的任何并发引用都将导致快照事务失败。

READ_COMMITTED_SNAPSHOT数据库选项为ON时，已提交读事务没有此限制。

例如，数据库管理员执行下面的ALTERINDEX语句。

USEAdventureWorks;

GO

ALTERINDEXAK_Employee_LoginID

   ONHumanResources.EmployeeREBUILD;

GO

         执行ALTERINDEX语句后，任何在执行ALTERINDEX语句时处于活动状态的快照事务，如果试图引用HumanResources.Employee表，都将收到错误。

而使用行版本控制的已提交读事务不受影响。

          3．BULKINSERT操作可能会导致对目标表元数据的更改（例如，禁用约束检查时）。

如果出现这种情况，访问大容量插入表的并发快照隔离事务将失败。

设置SNAPSHOT：

       设置SNAPSHOT隔离模式也很简单，只要我们简单的一步操作就可以实现。

ALTERDATABASEDATABASE_NAME

SETALLOW_SNAPSHOT_ISOLATIONON;

      但是要注意：

如果ALLOW_SNAPSHOT_ISOLATION数据库选项设置为ON，则数据库中数据已修改的所有活动事务完成之前，MicrosoftSQLServerDatabaseEngine 实例不会为已修改的数据生成行版本。

如果存在活动的修改事务，SQLServer将把该选项的状态设置为PENDING_ON。

所有修改事务完成后，该选项的状态更改为ON。

在该选项完全处于ON状态之前，用户无法在数据库中启动快照事务。

数据库管理员将ALLOW_SNAPSHOT_ISOLATION选项设置为OFF后，数据库将跳过PENDING_OFF状态。

下面是ALLOW_SNAPSHOT_ISOLATION选项的各个状态

当前数据库的快照隔离框架状态

说明

OFF

未启用对快照隔离事务的支持。

不允许执行快照隔离事务。

PENDING_ON

对快照隔离事务的支持处于转换状态（从OFF到ON）。

打开的事务必须完成。

不允许执行快照隔离事务。

ON

已启用对快照隔离事务的支持。

允许执行快照事务。

PENDING_OFF

对快照隔离事务的支持处于转换状态（从ON到OFF）。

此后启动的快照事务无法访问此数据库。

更新事务仍会导致此数据库中出现版本控制开销。

现有快照事务仍可以访问此数据库，不会遇到任何问题。

直到数据库快照隔离状态为ON时处于活动状态的所有快照事务完成后，状态PENDING_OFF才变为OFF。

SNAPSHOT的使用：

           下面是使用READ_COMMITTED_SNAPSHOT的一个例子：

          在快照隔离下运行的事务可以访问数据库中为快照启用的表。

若要访问没有为快照启用的表，则必须更改隔离级别。

例如，下面的代码示例显示了在快照事务下运行时联接两个表的SELECT语句。

一个表属于未启用快照隔离的数据库。

当SELECT语句在快照隔离下运行时，该语句无法成功执行。

SETTRANSACTIONISOLATIONLEVELSNAPSHOT;

BEGINTRAN

   SELECTt1.col5,t2.col5

       FROMTable1ast1

       INNERJOINSecondDB.dbo.Table2ast2

           ONt1.col1=t2.col2;

下面的代码示例显示了已修改为从事务隔离级别更改为已提交读隔离级别的相同SELECT语句。

由于此更改，SELECT语句将成功执行。

SETTRANSACTIONISOLATIONLEVELSNAPSHOT;

BEGINTRAN

   SELECTt1.col5,t2.col5

       FROMTable1ast1

       WITH（READCOMMITTED）

       INNERJOINSecondDB.dbo.Table2ast2

           ONt1.col1=t2.col2;

SNAPSHOT的演示:

      在此示例中，在快照隔离下运行的事务将读取数据，然后由另一事务修改此数据。

快照事务不阻塞由其他事务执行的更新操作，它忽略数据的修改继续从版本化的行读取数据。

但是，当快照事务尝试修改已由其他事务修改的数据时，快照事务将生成错误并终止。

在会话1上：

USEAdventureWorks;

GO

--在数据库上开启snapshot隔离级别.

ALTERDATABASEAdventureWorks

 SETALLOW_SNAPSHOT_ISOLATIONON;

GO

--开始一个snapshot事务

SETTRANSACTIONISOLATIONLEVELSNAPSHOT;

GO

BEGINTRANSACTION;

--选择EmployeeID号为4的员工的休假资料

 SELECTEmployeeID,VacationHours

 FROMHumanResources.Employee

WHEREEmployeeID=4;

在会话2上：

USEAdventureWorks;

GO

--开始一个事务

BEGINTRANSACTION;

--我们修改了EmployeeID为4的员工的休假资料

UPDATEHumanResources.Employee

 SETVacationHours=VacationHours-8

WHEREEmployeeID=4;

--确认下现在EmployeeID为4的员工的休假资料

 SELECTVacationHours

FROMHumanResources.Employee

WHEREEmployeeID=4;

在会话1上：

--因为会话二的事务没有提交，

--EmployeeID为4的员工的休假资料因该没变

SELECTEmployeeID,VacationHours

FROMHumanResources.Employee

WHEREEmployeeID=4;

在会话2上：

--提交我们的更新，数据被更改了

COMMITTRANSACTION;

GO

在会话1上：

--OK，现在看看小4同志的资料，被修改的数据被华丽的无视了

SELECTEmployeeID,VacationHours

 FROMHumanResources.Employee

WHEREEmployeeID=4;

--现在我们也来尝试下修改小4同志的资料，

--事务即将崩溃，请系紧安全带。

^_^.

 UPDATEHumanResources.Employee

 SETSickLeaveHours=SickLeaveHours-8

WHEREEmployeeID=4;

--ROLLBACK之后小4的数据到底是什么？

你知道吗？

ROLLBACKTRANSACTION

GO