【转修正】sql server行版本控制的隔离级别

[WX1]复制的内容保存在tempdb当中。

CREATE TABLE [dbo].[Employee](
    [EmployeeID] [int] NULL,
    [VacationHours] [int] NULL,
    [SickLeaveHours] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Employee] ([EmployeeID], [VacationHours], [SickLeaveHours]) VALUES (4, 48, 48)

--step1:开启第一个事务
BEGIN TRAN tran1
    --step2:执行select操作,查看VacationHours,对查找的记录加S锁
    SELECT EmployeeID, VacationHours
        FROM Employee 
        WHERE EmployeeID = 4;

    --step6: 在第一个事务中重新运行查询语句，发现查询被阻塞
    --这是因为在VacationHours上面有排他锁,现在要查询VacationHours字段又必须获得S锁,但是X锁与S锁冲突
    --所以不能执行查询,被阻塞.
    SELECT EmployeeID, VacationHours
        FROM Employee
        WHERE EmployeeID = 4;

    --step8:因为会话2已经提交了事务,不再阻塞当前查询，因此返回会话2修改好的新数据：40

--step9:回滚或者提交事务
ROLLBACK TRANSACTION;
commit tran tran1
GO

--step3:开启第二个事务
BEGIN TRAN tran2;
    --step4:修改VacationHours,需要获得更新锁U,在VacationHours上有S锁,US不冲突,因此可以进行修改.
    --在修改VacationHours以后,更新锁U变成了排他锁X
    UPDATE Employee 
        SET VacationHours = VacationHours - 8WHERE EmployeeID = 4;

    --step5:在当前事务中查询VacationHours,发现只有40小时
    SELECT VacationHours
        FROM Employee
        WHERE EmployeeID = 4;

--step7:回滚事务
rollback tran tran2
--commit tran tran2

--step1：启用快照隔离
ALTER DATABASE HumanResources
    SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

--step2：设置使用快照隔离级别，前面没有设定是因为数据库默认的隔离界别就是Read Committed
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

--step3:开启第一个事务
BEGIN TRAN tran1
    --step4:执行select操作,查看VacationHours,对查找的记录加S锁
    SELECT EmployeeID, VacationHours
        FROM Employee 
        WHERE EmployeeID = 4;

    --step8:在事务2中修改了数据以后,在事务1中再次运行查询语句
    --此时查询语句没有被阻塞，返回的值是48，也就是事务2修改之前的数据
    --这是因为事务1是从版本化的行读取数据 
    SELECT EmployeeID, VacationHours
        FROM Employee
        WHERE EmployeeID = 4;

    --step10:在事务2提交以后，事务1再次执行查询操作
    --发现查询结果还是48，这是因为事务依然从版本化的行中读取数据
    SELECT EmployeeID, VacationHours
        FROM Employee
        WHERE EmployeeID = 4;


    --step11：在事务2提交修改以后，事务1想再做任何修改时，这里我们修改SickLeaveHours字段的值
    --此时会遇到3960错误，事务1会自动回滚,事务2中的修改不会被回滚.
    UPDATE Employee
        SET SickLeaveHours = SickLeaveHours - 8WHERE EmployeeID = 4;

    --附:假如事务2中执行了修改操作,但是没有提交,此时在事务1中执行修改操作会被阻塞
    --此时如果提交事务2中的修改操纵,事务1会遇到3960错误,跟上面一样.
    UPDATE Employee
        SET SickLeaveHours = SickLeaveHours - 8WHERE EmployeeID = 4;

rollback tran tran1
commit tran tran1
/*Msg 3960, Level 16, State 2, Line 1
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to
 access table 'Employee' directly or indirectly in database 'AdventureWorks' to update,
  delete, or insert the row that has been modified or deleted by another transaction. Retry the 
  transaction or change the isolation level for the update/delete statement.
*/
--实验2结束------------------------------------

--step5:开启第二个事务
BEGIN TRAN tran2;
    --step6:修改VacationHours,需要获得更新锁U,在VacationHours上有S锁,US不冲突,因此可以进行修改.
    --在修改VacationHours以后,更新锁U变成了排他锁X
    UPDATE Employee 
        SET VacationHours = VacationHours - 8WHERE EmployeeID = 4;

    --step7:在当前事务中查询VacationHours,发现只有40小时
    SELECT VacationHours
        FROM Employee
        WHERE EmployeeID = 4;

--step9:提交事务2
commit tran tran2
--实验2结束------------------------------------

--step1：启用快照隔离
ALTER DATABASE AdventureWorks
    SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

--step2：设置使用快照隔离级别，前面没有设定是因为数据库默认的隔离界别就是Read Committed
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

SELECT name,snapshot_isolation_state, snapshot_isolation_state_desc, is_read_committed_snapshot_on 
FROM sys.databases where name='AdventureWorks';

--实验3:READ_COMMITTED_SNAPSHOT -------------------

--stpe1:启用行版本控制的已提交读
--注意运行这句话的时候，不可以有其他连接同时使用AdventureWorks
use master
ALTER DATABASE HumanResources
    SET READ_COMMITTED_SNAPSHOT ON
    WITH ROLLBACK IMMEDIATE
GO

--step2:设置使用已提交读隔离级别
USE HumanResources;
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

--step3:开启第一个事务
BEGIN TRAN tran1
    --step4:执行select操作,查看VacationHours,对查找的记录加S锁
    SELECT EmployeeID, VacationHours
        FROM Employee 
        WHERE EmployeeID = 4;

    --step8:在事务2中修改了数据以后,在事务1中再次运行查询语句
    --此时查询语句没有被阻塞，返回的值是48，也就是事务2修改之前的数据
    --这是因为事务1是从版本化的行读取数据 
    SELECT EmployeeID, VacationHours
        FROM Employee
        WHERE EmployeeID = 4;

    --step10:在事务2提交以后，事务1再次执行查询操作
    --这里和实验2不同，事务1始终返回已提交的值,所以这里返回40，因为会话2已经提交了事务
    SELECT EmployeeID, VacationHours
        FROM Employee
        WHERE EmployeeID = 4;

    --step11：这里修改会成功,不会报错.
    UPDATE Employee
        SET SickLeaveHours = SickLeaveHours - 8WHERE EmployeeID = 4;

rollback tran tran1
--实验3结束------------------------------------

--step5:开启第二个事务
BEGIN TRAN tran2;
    --step6:修改VacationHours,需要获得更新锁U,在VacationHours上有S锁,US不冲突,因此可以进行修改.
    --在修改VacationHours以后,更新锁U变成了排他锁X
    UPDATE Employee 
        SET VacationHours = VacationHours - 8WHERE EmployeeID = 4;

    --step7:在当前事务中查询VacationHours,发现只有40小时
    SELECT VacationHours
        FROM Employee
        WHERE EmployeeID = 4;

--step9:提交事务2
commit tran tran2
--实验3结束------------------------------------