sql server行版本控制的隔离级别

ゝ一世哀愁。 2022-04-11 06:09 265阅读 0赞

在SQL Server标准的已提交读（READ COMMITTED）隔离级别下，一个读操作会和一个写操作相互阻塞。未提交读（READ UNCOMMITTED）虽然不会有这种阻塞，但是读操作可能会读到脏数据，这是大部分用户不能接受的。有些关系型数据库（例如Oracle）使用的是另 一种处理方式。**在任何一个修改之前，先对修改前的版本做一个复制\[WX1\] ，后续的一切读操作都会去读这个复制的版本，修改将创建一个新的版本。**在这种处理方式下，读、写操作不会相互阻塞。使用这种行版本控制机制的好处，是程序的并发性比较高，但是缺点是用户读到的虽然不是一个脏数据，但是可能是个正在被修改马上就要过期的数据值\[WX2\] 。如果根据这个过期的值做数据修改，会产生逻辑错误。

--------------------

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

\[WX2\]假如读跟写同时进行，读到的不是现在正被修改的值，如果是读到正被修改的值那就是脏读了。读到的是修改前的值。但是这个值随时会过期。等到修改完就过期了。

有些用户可能为了更高的并发性而不在乎这种缺点，所以更喜欢Oracle的那种处理方法。为了满足这部分用户的需求，SQL Server 2005也引入了这种机制，来实现类似的功能。所以选取行版本控制隔离级别也可以成为消除阻塞和死锁的一种手段。

SQL Server有两种行版本控制，使用行版本控制的已提交读隔离（READ\_COMMITTED\_SNAPSHOT）和直接使用SNAPSHOT事务隔离级别。

*  READ\_COMMITTED\_SNAPSHOT数据库选项为ON时，READ\_COMMITTED事务通过使用行版本控制提供**语句级**读取一致性。
 *  ALLOW\_SNAPSHOT\_ISOLATION数据库选项为ON时，SNAPSHOT事务通过使用行版本控制提供**事务级**读取一致性。

下列示例可以说明使用普通已提交读事务，行版本控制的快照隔离事务和行版本控制的已提交读事务的行为差异。

## 实验1：Read Committed Isolation level ##

query1：事务1

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

USE AdventureWorks;
    GO
    
    --step1:开启第一个事务
    BEGIN TRAN tran1
        --step2:执行select操作,查看VacationHours,对查找的记录加S锁
        SELECT EmployeeID, VacationHours
            FROM HumanResources.Employee 
            WHERE EmployeeID = 4;
    
        --step6: 在第一个事务中重新运行查询语句，发现查询被阻塞
        --这是因为在VacationHours上面有排他锁,现在要查询VacationHours字段又必须获得S锁,但是X锁与S锁冲突
        --所以不能执行查询,被阻塞.
        SELECT EmployeeID, VacationHours
            FROM HumanResources.Employee
            WHERE EmployeeID = 4;
    
        --step8:因为会话2已经提交了事务,不再阻塞当前查询，因此返回会话2修改好的新数据：40
    
    --step9:回滚或者提交事务
    ROLLBACK TRANSACTION;
    commit tran tran1
    GO

![复制代码][copycode.gif]

query2：事务2

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

USE AdventureWorks;
    GO
    
    --step3:开启第二个事务
    BEGIN TRAN tran2;
        --step4:修改VacationHours,需要获得更新锁U,在VacationHours上有S锁,US不冲突,因此可以进行修改.
        --在修改VacationHours以后,更新锁U变成了排他锁X
        UPDATE HumanResources.Employee 
            SET VacationHours = VacationHours - 8  
            WHERE EmployeeID = 4;
    
        -- step5:在当前事务中查询VacationHours,发现只有40小时
        SELECT VacationHours
            FROM HumanResources.Employee
            WHERE EmployeeID = 4;
    
    --step7:回滚事务
    rollback tran tran2
    --commit tran tran2

![复制代码][copycode.gif]

总结：

1.  事务1中的读操作没有阻塞事务2中的写操作
2.  事务2中的更新操作阻塞了事务1中后来的读操作，如下图所示：![2012073113180960.png][]
3.  事务1两次查询得到的数据分别是48跟40，两次获得的数据内容不一样。所以也成read committed为不可重复读。在read committed隔离级别中，只在语句级别加锁，当语句执行完以后自动释放锁。比如事务1中的第一次查询，虽然查询在事务中进行，并且事务没有提交，但 是此时查询语句执行完以后在table上就找不到锁了。

## 实验2：Snapshot Isolation ##

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

query1：事务1，快照事务

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

--实验2:Read Committed Snapshot Isolation level -------------------
    USE AdventureWorks;
    GO
    
    --step1：启用快照隔离
    ALTER DATABASE AdventureWorks
        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 HumanResources.Employee 
            WHERE EmployeeID = 4;
    
        --step8:在事务2中修改了数据以后,在事务1中再次运行查询语句
        --此时查询语句没有被阻塞，返回的值是48，也就是事务2修改之前的数据
        --这是因为事务1是从版本化的行读取数据 
        SELECT EmployeeID, VacationHours
            FROM HumanResources.Employee
            WHERE EmployeeID = 4;
    
        --step10:在事务2提交以后，事务1再次执行查询操作
        --发现查询结果还是48，这是因为事务依然从版本化的行中读取数据
        SELECT EmployeeID, VacationHours
            FROM HumanResources.Employee
            WHERE EmployeeID = 4;
    
    
        --step11：在事务2提交修改以后，事务1想再做任何修改时，这里我们修改SickLeaveHours字段的值
        --此时会遇到3960错误，事务1会自动回滚,事务2中的修改不会被回滚.
        UPDATE HumanResources.Employee
            SET SickLeaveHours = SickLeaveHours - 8
            WHERE EmployeeID = 4;
    
        --附:假如事务2中执行了修改操作,但是没有提交,此时在事务1中执行修改操作会被阻塞
        --此时如果提交事务2中的修改操纵,事务1会遇到3960错误,跟上面一样.
        UPDATE HumanResources.Employee
            SET SickLeaveHours = SickLeaveHours - 8
            WHERE 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 'HumanResources.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结束------------------------------------

![复制代码][copycode.gif]

query2：事务2

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

--实验2:Read Committed Snapshot Isolation level -------------------
    USE AdventureWorks;
    GO
    
    --step5:开启第二个事务
    BEGIN TRAN tran2;
        --step6:修改VacationHours,需要获得更新锁U,在VacationHours上有S锁,US不冲突,因此可以进行修改.
        --在修改VacationHours以后,更新锁U变成了排他锁X
        UPDATE HumanResources.Employee 
            SET VacationHours = VacationHours - 8  
            WHERE EmployeeID = 4;
    
        -- step7:在当前事务中查询VacationHours,发现只有40小时
        SELECT VacationHours
            FROM HumanResources.Employee
            WHERE EmployeeID = 4;
    
    --step9:提交事务2
    commit tran tran2
    --实验2结束------------------------------------

![复制代码][copycode.gif]

总结：

1.  快照事务1的读操作没有阻塞普通事务2的读操作，但是阻塞了事务2的删除操作，如果在事务2中执行delete操作的话会报错：Employees cannot be deleted. They can only be marked as not current.Msg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted.
2.  普通事务2的更新操作，没有阻塞事务1的读操作，但是我们发现事务1中读到数据是事务2更新之前的内容。因为读取的是版本化中的行数据。

在上述实验中，我们发现下面两条语句使一起使用的，也就是首先允许数据库开启snapshot isolation，然后再将isolation level设定为snapshot。

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

在执行完step1以前，我们可以在sys.databases中查看AdvantureWorks的snapshot\_isolation\_state和 is\_read\_committed\_snapshot\_on这两个属性：

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

查询结果如下图所示：

![2012073019300670.png][]

假如我们不执行step1，只执行step2，然后开启事务进行查询，会报如下错误：

Msg 3952, Level 16, State 1, Line 3  
Snapshot isolation transaction failed accessing database 'AdventureWorks' because **snapshot isolation is not allowed** in this database. Use ALTER DATABASE to allow snapshot isolation.

执行完step1以后，我们再次查看sys.databases中的内容，发现snapshot\_isolation\_state由0变为1，如下图所示：

![2012073019333167.png][]

## 实验3：使用行版本控制的已提交读隔离（READ\_COMMITTED\_SNAPSHOT） ##

在此示例中，使用**行版本控制的已提交读事务**与其他事务并发运行。已提交读事务的行为与快照事务的行为有所不同。与快照事务相同的是，即使其他事务修改了数据，已提交读事务也将读取版本化的行。与快照事务不同的是，已提交读将执行下列操作：

*  在其他事务提交数据更改后，读取修改的数据。
 *  能够更新由其他事务修改的数据，而快照事务不能。

query1：事务1

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

--实验3:READ_COMMITTED_SNAPSHOT -------------------
    
    --stpe1:启用行版本控制的已提交读
    -- 注意运行这句话的时候，不可以有其他连接同时使用AdventureWorks
    use master
    ALTER DATABASE AdventureWorks
        SET READ_COMMITTED_SNAPSHOT ON;
    GO
    
    --step2:设置使用已提交读隔离级别
    USE AdventureWorks;
    GO
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    GO
    
    --step3:开启第一个事务
    BEGIN TRAN tran1
        --step4:执行select操作,查看VacationHours,对查找的记录加S锁
        SELECT EmployeeID, VacationHours
            FROM HumanResources.Employee 
            WHERE EmployeeID = 4;
    
        --step8:在事务2中修改了数据以后,在事务1中再次运行查询语句
        --此时查询语句没有被阻塞，返回的值是48，也就是事务2修改之前的数据
        --这是因为事务1是从版本化的行读取数据 
        SELECT EmployeeID, VacationHours
            FROM HumanResources.Employee
            WHERE EmployeeID = 4;
    
        --step10:在事务2提交以后，事务1再次执行查询操作
        -- 这里和实验2不同，事务1始终返回已提交的值,所以这里返回40，因为会话2已经提交了事务
        SELECT EmployeeID, VacationHours
            FROM HumanResources.Employee
            WHERE EmployeeID = 4;
    
        --step11：这里修改会成功,不会报错.
        UPDATE HumanResources.Employee
            SET SickLeaveHours = SickLeaveHours - 8
            WHERE EmployeeID = 4;
    
    rollback tran tran1
    --实验3结束------------------------------------

![复制代码][copycode.gif]

query2：事务2

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

--实验3:READ_COMMITTED_SNAPSHOT  -------------------
    USE AdventureWorks;
    GO
    
    --step5:开启第二个事务
    BEGIN TRAN tran2;
        --step6:修改VacationHours,需要获得更新锁U,在VacationHours上有S锁,US不冲突,因此可以进行修改.
        --在修改VacationHours以后,更新锁U变成了排他锁X
        UPDATE HumanResources.Employee 
            SET VacationHours = VacationHours - 8  
            WHERE EmployeeID = 4;
    
        -- step7:在当前事务中查询VacationHours,发现只有40小时
        SELECT VacationHours
            FROM HumanResources.Employee
            WHERE EmployeeID = 4;
    
    --step9:提交事务2
    commit tran tran2
    --实验3结束------------------------------------

![复制代码][copycode.gif]

总结：

1.  在事务2修改数据之后，提交之前，事务1中读到的是快照数据，也就是事务2没有修改之前的值。
2.  在事务2提交修改之后，事务1读到了修改之后的数据。并且事务1可以修改由其他数据修改了的数据。

## 结论 ##

1.  使用 sys.databases 目录视图可以确定两个行版本控制数据库选项的状态。
2.  对用户表和存储在 master 和 msdb 中的某些系统表的任何更新都会生成行版本。
3.  在 master 和 msdb 数据库中，ALLOW\_SNAPSHOT\_ISOLATION 选项自动设置为 ON，并且不能禁用。
4.  在 master 数据库、tempdb 数据库或 msdb 数据库中，用户不能将 READ\_COMMITTED\_SNAPSHOT 选项设置为 ON。
5.  从上面的测试可以看到，原先会发生阻塞的两个会话在使用行版本控制的隔离级别后，都不会遇到阻塞了。但是两种行版本控制的结果又有不同。可以用表1来总结。

表1  使用行版本控制隔离级别后的不同

<table style="width:529px;"> 
 <tbody> 
 <tr> 
 <td> 会话1 </td> 
 <td> 会话2 </td> 
 <td colspan="5"> 结果 </td> 
 </tr> 
 <tr> 
 <td> A. 普通已提交事务 </td> 
 <td colspan="2"> B. 使用快照隔离 </td> 
 <td colspan="2"> C. 使用行版本控制的已提交读 </td> 
 </tr> 
 <tr> 
 <td> BEGIN TRAN 查询1 </td> 
 <td> &nbsp; </td> 
 <td colspan="5"> 48 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> BEGIN TRAN 修改1 </td> 
 <td colspan="5"> 成功 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> 查询1 </td> 
 <td colspan="5"> 40 </td> 
 </tr> 
 <tr> 
 <td> 查询2 </td> 
 <td> &nbsp; </td> 
 <td colspan="2"> 被阻塞 </td> 
 <td colspan="3"> 48 </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> COMMIT TRAN </td> 
 <td colspan="2"> 查询2返回40 </td> 
 <td colspan="3"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 查询3 </td> 
 <td> &nbsp; </td> 
 <td colspan="2"> 40 </td> 
 <td colspan="2"> 48 </td> 
 <td> 40 </td> 
 </tr> 
 <tr> 
 <td> 修改2 ROLLBACK TRAN </td> 
 <td> &nbsp; </td> 
 <td colspan="2"> 成功 </td> 
 <td colspan="2"> 失败 </td> 
 <td> 成功 </td> 
 </tr> 
 </tbody> 
</table>

补充：如果要查看锁状态，可以使用如下两种方法：

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

--常看锁状态有一下两种方式
    --1.查看视图sys.dm_tran_locks
    SELECT request_session_id, resource_type, resource_associated_entity_id,
            request_status, request_mode, resource_description
        FROM sys.dm_tran_locks
    
    --2.使用存储过程
    sp_lock

![复制代码][copycode.gif]

[ExpandedBlockStart.gif]: /images/20220411/6e39b33051d64cc09bd10dc75b4f2bf8.png
[copycode.gif]: /images/20220411/74b650a90fb54c319e011522ea3533cf.png
[2012073113180960.png]: /images/20220411/9141c26d492643d2b4762bd1d4feda85.png
[2012073019300670.png]: /images/20220411/97bc334b910541bca32a8e5fa82a44b9.png
[2012073019333167.png]: /images/20220411/53818c8c33b344b18e2b9af6334691bd.png