--B 使用快照隔离 --此示例中，在快照隔离下运行的事务将读取数据，然后由另一个事务修改此数据。快照事 务 --不阻塞由其他事务执行的更新操作，他忽略数据的修改继续从版本化的行读取数据。也就 是 --说，读取到的是数据修改前的版本。但是，当快照事务尝试修改已由其他事务修改的数据 时， --他将生成错误并终止 --在会话 1 上： USE[AdventureWorks] GO --启用快照隔离 ALTERDATABASE[AdventureWorks]SET ALLOW_SNAPSHOT_ISOLATION ON GO --设置使用快照隔离级别 SETTRANSACTION ISOLATION LEVEL SNAPSHOT GO BEGINTRAN --查询 1 --查询返回员工有 48 小时假期 SELECT[EmployeeID],[VacationHours]FROM[HumanResources].[Employee]WHERE[Employe eID]=4 ----------------------------------------------------------------------------------------------在会话 2 上： USE[AdventureWorks] GO BEGINTRAN --修改 1 --假期时间减 8 --修改不会被会话 1 阻塞 UPDATE[HumanResources].[Employee]
解决事务引起的超时和死锁的问题
SQL 有两种行版本控制： --（1）行版本控制的已提交读隔离（read_committed_snapshot） --（2）直接使用 snapshot 事务隔离级别 --（1）（read_committed_snapshot）：read_committed_snapshot 数据库选项为 ON 时， read_committed 事务通过使用行 --版本控制提供语句级读取一致性 --（2）（snapshot tion 数据库选项为 ON 时，snapshot 事务通过使用行版本 --控制提供事务级读取一致性
----------------------------------------------------------------------------------------------
--C 使用行版本控制的已提交读 --在此示例中，使用行版本控制的已提交读事务与其他事务并发运行。已提交读事务的行为 与快照事务的行为 --有所不同。与快照事务相同的是，即使其他事务修改了数据，已提交读事务也将读取版本 化的行。 --与快照事务不同的是，已提交读将执行下列操作： --（1）在其他事务提交数据更改之后，读取修改的数据 --（2）能够更新由其他事务修改的数据，而快照事务不能 --在会话 1 上： USE[AdventureWorks] GO --启用行版本控制的已提交读 --注意运行这句话的时候，不可以有其他连接同时使用[AdventureWorks] ALTERDATABASE[AdventureWorks]SET READ_COMMITTED_SNAPSHOT ON GO --设置使用已提交读隔离级别 SETTRANSACTION ISOLATION LEVEL READ COMMITTED GO BEGINTRAN --查询 1 --这里将返回初始值 48 SELECT[EmployeeID],[VacationHours]FROM[HumanResources].[Employee]WHERE[Employe eID]=4 -----------------------------------------------------------------------------------------------------在会话 2 上： USE[AdventureWorks] GO BEGINTRAN --修改 1 --假期时间减 8 --修改不会被会话 1 阻塞 UPDATE[HumanResources].[Employee]
--下列示例可以说明使用普通已提交读事务，行版本控制的快照隔离事务和行版本 --控制的已提交读事务的行为差异 --示例： --A 普通已提交事务 --在此示例中，一个普通 read committed 事务将读取数据，然后由另一事务修改此数据。执 行 --完的读操作不阻塞由其他事务执行的更新操作。但是，在其他事务已经做了更新操作后， 读 --操作会被阻塞住，直到更新操作事务提交为止 --在会话 1 上： USE[AdventureWorks] GO BEGINTRAN --查询 1 --这个查询将返回员工有 48 小时休假时间 SELECT[EmployeeID],[VacationHours]FROM[HumanResources].[Employee]WHERE[Employe eID]=4 ------------------------------------------------------------------------------------------------在会话 2 上： USE[AdventureWorks] GO BEGINTRAN --修改 1 --休假时间减去 8
SET[VacationHours]=[VacationHours]-8 WHERE[EmployeeID]=4 --查询 1 --确认值已经被修改为 40 SELECT[VacationHours]FROM[HumanResources].[Employee]WHERE[EmployeeID]=4 -------------------------------------------------------------------------------------------------在会话 1 上： --查询 2 --再次运行查询语句 --还是返回 48（修改前的值），因为会话 2 还没有提交 --会话 1 是从版本化的行读取数据 SELECT[EmployeeID],[VacationHours]FROM[HumanResources].[Employee]WHERE[Employe eID]=4 --------------------------------------------------------------------------------------------------在会话 2 上： --提交事务 COMMITTRAN GO ---------------------------------------------------------------------------------------------------在会话 1 上： --查询 3 --这里和范例 B 不同，会话 1 始终返回已提交的值 --这里返回 40，因为会话 2 已经提交了事务 SELECT[EmployeeID],[VacationHours]FROM[HumanResources].[Employee]WHERE[Employe eID]=4 --修改 2 --这里会成功 UPDATE[HumanResources].[Employee] SET[SickLeaveHours]=[SickLeaveHours]-8 WHERE[EmployeeID]=4 --可以回滚会话 1 的修改 --会话 2 的修改不会受影响 ROLLBACKTRAN GO
--在会话 1 上： --重新运行查询语句，会被会话 2 阻塞 --查询 2 SELECT[EmployeeID],[VacationHours]FROM[HumanResources].[Employee]WHERE[Employe eID]=4 ------------------------------------------------------------------------------------------------在会话 2 上： --提交事务 COMMITTRAN GO ------------------------------------------------------------------------------------------------在会话 1 上： --此时先前被阻塞的查询结束，返回会话 2 修改好的新数据：40 --查询 3 --这里返回 40，因为会话 2 已经提交了事务 SELECT[EmployeeID],[VacationHours]FROM[HumanResources].[Employee]WHERE[Employe eID]=4 --修改 2 --这里会成功 UPDATE[HumanResources].[Employee] SET[SickLeaveHours]=[SickLeaveHours]-8 WHERE[EmployeeID]=4 SELECT[SickLeaveHours]FROM[HumanResources].[Employee]WHERE[EmployeeID]=4 --可以回滚会话 1 的修改 --会话 2 的修改不会受影响
--修改不会被阻塞，因为会话 1 不会持有 S 锁不放 UPDATE[HumanResources].[Employee] SET[VacationHours]=[VacationHours]-8 WHERE[EmployeeID]=4 --查询 1 --现在休假时间只有 40 小时 SELECT[VacationHours]FROM[HumanResources].[Employee]WHERE[EmployeeID]=4 --------------------------------------------------------------------------------------------------
SET[VacationHours]=[VacationHours]-8 WHERE[EmployeeID]=4 --查询 1 --确认值已经被改成 40 SELECT[VacationHours]FROM[HumanResources].[Employee]WHERE[EmployeeID]=4 ------------------------------------------------------------------------------------------------在会话 1 上： --查询 2 --再次运行查询语句 --还是返回 48（修改前的值），因为会话 1 是从版本化的行读取数据 SELECT[EmployeeID],[VacationHours]FROM[HumanResources].[Employee]WHERE[Employe eID]=4 -------------------------------------------------------------------------------------------------在会话 2 上： --提交事务 COMMITTRAN GO --------------------------------------------------------------------------------------------------在会话 1 上： --查询 3 --再次运行查询语句 --还是返回 48（修改前的值），因为会话 1 还是从版本化的行读取数据 SELECT[EmployeeID],[VacationHours]FROM[HumanResources].[Employee]WHERE[Employe eID]=4 --修改 2 --因为数据已经被会话 2 修改过，会话 1 想做任何修改时 --会遇到 3960 错误 --事务会自动回滚 UPDATE[HumanResources].[Employee] SET[SickLeaveHours]=[SickLeaveHours]-8 WHERE[EmployeeID]=4 --会话 1 的修改会回滚 --会话 2 的修改不会回滚 ROLLBACKTRAN GO