SQL Server 2005使用基于行版本控制的隔离级别初探(3)

(^o^)/~完美

SQL Server 2005使用基于行版本控制的隔离级别初探(2) -- SNAPSHOT

       上一篇介绍了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．SQL Server 不会保留多个版本的系统元数据。表中的数据定义语言 (DDL) 语句和其他数据库对象（索引、视图、数据类型、存储过程和公共语言运行时函数）会更改元数据。如果 DDL 语句修改一个对象，那么在快照隔离下对该对象的任何并发引用都将导致快照事务失败。READ_COMMITTED_SNAPSHOT 数据库选项为 ON 时，已提交读事务没有此限制。
例如，数据库管理员执行下面的 ALTER INDEX 语句。
   

USE AdventureWorks; GO ALTER INDEX AK_Employee_LoginID     ON HumanResources.Employee REBUILD; GO

          执行 ALTER INDEX 语句后，任何在执行 ALTER INDEX 语句时处于活动状态的快照事务，如果试图引用 HumanResources.Employee 表，都将收到错误。而使用行版本控制的已提交读事务不受影响。

           3．BULK INSERT 操作可能会导致对目标表元数据的更改（例如，禁用约束检查时）。如果出现这种情况，访问大容量插入表的并发快照隔离事务将失败。

设置SNAPSHOT：

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

ALTER DATABASE DATABASE_NAME
SET ALLOW_SNAPSHOT_ISOLATION ON;

       但是要注意：如果 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON，则数据库中数据已修改的所有活动事务完成之前，Microsoft SQL Server Database Engine 实例不会为已修改的数据生成行版本。如果存在活动的修改事务，SQL Server 将把该选项的状态设置为 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 语句在快照隔离下运行时，该语句无法成功执行。

SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRAN     SELECT t1.col5, t2.col5         FROM Table1 as t1         INNER JOIN SecondDB.dbo.Table2 as t2             ON t1.col1 = t2.col2;

下面的代码示例显示了已修改为从事务隔离级别更改为已提交读隔离级别的相同 SELECT 语句。由于此更改，SELECT 语句将成功执行。

SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRAN     SELECT t1.col5, t2.col5         FROM Table1 as t1         WITH (READCOMMITTED)         INNER JOIN SecondDB.dbo.Table2 as t2             ON t1.col1 = t2.col2;

SNAPSHOT的演示:

       在此示例中，在快照隔离下运行的事务将读取数据，然后由另一事务修改此数据。快照事务不阻塞由其他事务执行的更新操作，它忽略数据的修改继续从版本化的行读取数据。但是，当快照事务尝试修改已由其他事务修改的数据时，快照事务将生成错误并终止。

在会话 1 上：

USE AdventureWorks;
GO

--在数据库上开启snapshot隔离级别.
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- 开始一个snapshot事务
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
BEGIN TRANSACTION;

--选择EmployeeID号为4的员工的休假资料
SELECT EmployeeID, VacationHours
FROM HumanResources.Employee
WHERE EmployeeID = 4;

在会话 2 上：
USE AdventureWorks;
GO

-- 开始一个事务
BEGIN TRANSACTION;

--我们修改了EmployeeID为4的员工的休假资料
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE EmployeeID = 4;

-- 确认下现在EmployeeID为4的员工的休假资料
SELECT VacationHours
FROM HumanResources.Employee
WHERE EmployeeID = 4;

在会话 1 上：

--因为会话二的事务没有提交，
--EmployeeID为4的员工的休假资料因该没变
SELECT EmployeeID, VacationHours
FROM HumanResources.Employee
WHERE EmployeeID = 4;

在会话 2 上：
--提交我们的更新，数据被更改了
COMMIT TRANSACTION;
GO

在会话 1 上：

--OK，现在看看小4同志的资料，被修改的数据被华丽的无视了
SELECT EmployeeID, VacationHours
FROM HumanResources.Employee
WHERE EmployeeID = 4;

--现在我们也来尝试下修改小4同志的资料，
--事务即将崩溃，请系紧安全带。^_^.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE EmployeeID = 4;

--ROLLBACK之后小4的数据到底是什么？你知道吗？
ROLLBACK TRANSACTION
GO