SQL Server 并发控制 第四篇：快照隔离（续）

前言：关于SQL Server的快照隔离，我学到一些新的知识点，于是整理一篇续文。

 

乐观控制模式假定阻塞很少，并使用行版本来避免大多数锁定和阻塞。当尝试修改数据时，这些行的原始数据（在此修改操作之前，且是已提交的数据）会被复制到 tempdb 并赋予事务序列号，此操作称为写时复制 (Copy on Write)。当另一个会话读取相同的数据时，将返回读取事务开始时的复制到tempdb的数据版本。

通过避免大多数锁定，这种方法可以比事务隔离以更低的成本显著提高并发性。当然，“天下没有免费的午餐！”，快照隔离也有一个隐藏的成本：tempdb 的使用量增加。

一，使用行版本（Row Version）的隔离级别

SQL Server 通过引入 SNAPSHOT 隔离级别并扩展实现了 READ COMMITTED， 新的 READ_COMMITTED_SNAPSHOT 隔离级别能以透明的方式替换所有事务的 READ COMMITTED隔离。

因此，SQL Server 中可用的两种快照隔离类型：

• READ COMMITTED SNAPSHOT ISOLATION (RCSI)：在事务开始之后，仍然会读取到其他事务提交的数据，但是不会读取到未提交的数据。
• SNAPSHOT ISOLATION（SI）：事务级别的数据一致性，在事务开始之后，只会读取在当前事务之前已提交的数据，而不会读取到在该事务开始后提交的数据。也就是说，在当前事务中，数据是可以重复读的，类似于SERIALIZABLE隔离，只不过SNAPSHOT不会阻塞写操作。

所以，快照隔离级别的数据一致性会高于RCSI，消耗也会比RCSI稍高。

从原理上解释快照隔离和RCSI的区别：

默认的并发控制模式是悲观模式，默认的隔离级别是Read Commited，在执行读操作时，事务申请shared lock，读写操作相互阻塞。在隔离级别Read Uncommitted下，事务不会申请shared lock，因此读操作不会阻塞写操作，但是读操作可能会读到脏数据。

乐观的并发控制模式有点像Read Uncommitted下，读操作不会申请shared lock，但是会使用行版本控制并发的事务。那么什么是行版本（Row Version）？可以这样理解，一旦启用了快照隔离或RCSI，当修改数据行时，每一行的数据结构中都额外包含一个事务序列号（TSN），这个TSN是递增的，这样的一行数据叫做行版本，也就是说，行版本存是含有业务数据和TSN（事务序列号）的一行数据。

当修改操作发生时，SQL Server 创建一个Row Version，将Original Row复制到Row Version，并将当前事务的TSN也存储在Row Version中。因此，Row Version存储的是修改之前的数据值。

在RCSI和快照隔离模式下，行版本的行为是不同的：

• 在RCSI隔离下，行版本只存在于修改操作期间，当修改提交后，行版本就会被释放。读事务只能读取到已经提交的数据。
• 在快照隔离下，行版本在修改操作发生时创建，同时生成一个TSN，当早于该TSN的所有事务都提交时，该行版本也会被释放。一个读事务只会读取早于当前TSN的已经提交的数据，对于晚于当前TSN的已经提交的数据，则不会读取。因此，数据表的一个Data Row，可以有多个Row Version。

不管哪种隔离级别，行版本都是临时的数据，已提交的数据修改已经作用于数据表中了，即使行版本丢失，也不会影响已经提交的数据修改，因此，SQL Server把行版本存储在tempdb中。

在Snapshot隔离级别下，事务在修改任何数据之前，先将原始数据行复制到tempdb，创建数据行的一个原始版本（Row Version)，注意，SQL Server只会复制被修改的数据行，对于未修改的数据行，不会保存行版本数据。后续其他事务的读操作都去读取该复制的行版本。在Snapshot隔离级别下，读写操作不会互相阻塞，使用行版本控制能够提高事务的并发性，但是有一个明显的缺点，虽然用户读到的不是脏数据，但是数据可能正在被修改，很快就要过期。如果根据这个过期的数据做数据修改，可能会产生业务逻辑错误。

在RCSI隔离下，在当前事务存续期间，仍然会读取到其他事务已经提交的数据，需要维护的事务链和行版本会少很多，因此，所需要消耗的资源更小。

二，启用Read Committed Snapshot Isolation (RCSI)

在数据库上启用RCSI，在启用RCSI后，默认的事务隔离级别Read Committed被Read Committed Snapshot自动取代，且不存在Read Committed隔离：

ALTER DATABASE <database name> SET READ_COMMITTED_SNAPSHOT ON;

检查数据库的隔离级别：

SELECT DB_NAME(database_id) as [DB Name],
    snapshot_isolation_state,
    snapshot_isolation_state_desc,
    is_read_committed_snapshot_on    
FROM sys.databases
WHERE database_id = DB_ID()

字段注释：

snapshot_isolation_state：正常情况下是0和1， 0表示不允许，1表示允许，而2和3是过度状态，都不能使用快照。注意，允许并不代表着目前的隔离级别就是快照隔离。

State of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option:

• 0 = Snapshot isolation state is OFF (default). Snapshot isolation is disallowed.
• 1 = Snapshot isolation state ON. Snapshot isolation is allowed.
• 2 = Snapshot isolation state is in transition to OFF state. All transactions have their modifications versioned. Can't start new transactions using snapshot isolation. The database remains in the transition to OFF state until all transactions that were active when ALTER DATABASE was run can be completed.
• 3 = Snapshot isolation state is in transition to ON state. New transactions have their modifications versioned. Transactions can't use snapshot isolation until the snapshot isolation state becomes 1 (ON). The database remains in the transition to ON state until all update transactions that were active when ALTER DATABASE was run can be completed.

snapshot_isolation_state_Desc: Description of state of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option.

is_read_committed_snapshot_on：

• 1 = READ_COMMITTED_SNAPSHOT option is ON. Read operations under the read-committed isolation level are based on snapshot scans and don't acquire locks.
• 0 = READ_COMMITTED_SNAPSHOT option is OFF (default). Read operations under the read-committed isolation level use share locks.

RCSI的工作原理是：将所有运行在RCSI下的查询都改为使用快照扫描（snapshot scan），并且除了 Sch-S（schema stability)）锁之外，不再获取其他锁。这样，即使是那些没有请求访问权限的查询，也能自动访问版本化的行。请看以下两个查询：

也可以通过以下代码查看当前Session的事务隔离级别：

SELECT CASE transaction_isolation_level
       WHEN 0 THEN 'Unspecified'
       WHEN 1 THEN 'ReadUncommitted'
       WHEN 2 THEN 'ReadCommitted'
       WHEN 3 THEN 'Repeatable'
       WHEN 4 THEN 'Serializable'
       WHEN 5 THEN 'Snapshot' 
       END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID  --current session

三，设置快照隔离

在数据库上把Allow  Snapshot Isolation选项设置为ON，但是这并不意味着启用了Snapshot Isolation，只是允许启用。

ALTER DATABASE MyOrders SET ALLOW_SNAPSHOT_ISOLATION ON;

快照隔离级别只能在会话级别上启用，下面的代码允许Snapshot Isolation，并在Session级别上启用了Snapshot Isolation，也就是说：在 SQL Server 中启用快照隔离级别，需要同时进行两个步骤：运行和启用

ALTER DATABASE <DB_NAME> SET ALLOW_SNAPSHOT ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

这里用两个Query来模型快照隔离下的更新冲突：

抛出的错误消息是：

Msg 3960, Level 16, State 5, Line 7

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘Orders. Orders’ directly or indirectly in database ‘MyOrders’ 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

 

最重要的部分是第一句话：“事务因更新冲突而中止。” 问题在于两个查询都试图更新同一行。当尝试提交第二个更新的行版本时，SQL Server 发现第一个事务也在尝试更新同一行。数据库引擎判断第二个更新操作的优先级更低，因此果断阻止第二个更新操作。这在某些方面类似于在悲观的事务隔离下可能发生的死锁。在事务隔离下，当两个或多个事务因为试图获取一个或多个不兼容的锁而发生锁冲突时，就会检测到死锁。使用行版本控制，可能会出现更新冲突，就像我遇到的情况一样。区别在于，锁冲突发生在事务提交之前，而更新冲突发生在提交期间，因为数据库引擎会将版本存储中的行与已提交的行进行比较，并发现不匹配。

 

引用文档：

Snapshot Isolation in SQL Server