sql语句大全之数据库的快照隔离级别（Snapshot I

隔离级别定义事务处理数据读取操作的隔离程度，在SQL Server中，隔离级别只会影响读操作申请的共享锁（Shared Lock），而不会影响写操作申请的互斥锁（Exclusive Lock），隔离级别控制读操作的行为：

• 在读数据时是否使用共享锁，申请何种类型的锁；
• 事务持有读锁的时间；
• 读操作引用被其他事务更新，但尚未提交的数据行时，控制读操作的行为：
  • 被阻塞，等待其他事务释放互斥锁；
  • 获取更新之前的数据值，从tempdb中读取行版本，该行版本在事务开始时已经提交；Retrieves the committed version of the row that existed at the time the statement or transaction started.
  • 读没有提交的数据，获取更新之后的数据值；

在执行写操作时，事务总是持有互斥锁，直到事务结束才释放，互斥锁不受事务隔离级别的影响。在SQL Server中，互斥锁和任意锁都不兼容，在同一时间，同一个数据行上，只能有一个事务持有互斥锁，就是说，写操作是顺序进行的，完全隔离的，不能并发执行。隔离和并发，此消彼长。

Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. 

事务的隔离级别共有5个，使用SET命令修改Session-Level的隔离级别，使用DBCC UserOptions 查看当前Session的隔离级别：

 

一，事务的隔离级别

SQL Server 数据库级别默认的事务隔离级别是Read Committed，用户不能修改Database-Level默认的隔离级别，但是，用户能够修改Session-Level默认的事务隔离级别。Session-Level默认的事务隔离级别是Read Committed，该隔离级别受到数据库选项 READ_COMMITTED_SNAPSHOT 的影响，决定Read Committed隔离级别是使用行版本控制事务的读操作，还是使用加共享锁来控制事务的读操作，在默认的Read Committed隔离级别下：

• 如果设置选项READ_COMMITTED_SNAPSHOT为OFF，那么事务在执行读操作时申请共享锁，阻塞其他事务的写操作；
• 如果设置选项READ_COMMITTED_SNAPSHOT为ON，那么事务在执行读操作时使用Row Versioning，不会申请共享锁，不会阻塞其他事务的写操作;

在任何隔离级别下，事务在执行写操作时都申请互斥锁（exclusive lock），持有互斥锁直到事务结束，互斥锁不受隔离级别的控制；而共享锁（Shared Lock）受到隔离级别的控制，隔离级别影响Shared Lock的申请和释放：

• 在 Read Uncommitted隔离级别下，读操作不会申请Shared Lock；
• 在 Read Committed（不使用row-versioning），Repeatable Read 和 Serializable隔离级别下，都会申请Shared Lock；
• 在 Read Committed（不使用row-versioning） 隔离级别下，在读操作执行时，申请和持有Share Lock；一旦读操作完成，释放Shared Lock;
• 在 Repeatable Read 和 Serializable隔离级别下，事务会持有Shared Lock，直到事务结束（提交或回滚）；
• 在Serializable隔离级别下，事务会持有范围Shared Lock（Range Lock），锁定一个范围，在事务活跃期间，其他事务不允许在该范围中进行更新（Insert 或 delete）操作；

SQL Server支持使用Row Version的隔离级别，事务的读操作只申请SCH-S 表级锁，不会申请Page 锁和Row 锁，事务的修改操作仍然申请锁：

• 当数据库选项 READ_COMMITTED_SNAPSHOT 设置为ON，Read Committed隔离级别使用Row Version提供语句级别（Statement-Level）的读一致性；
  • When a transaction runs at the read committed isolation level, all statements see a snapshot of data as it exists at the start of the statement. 
• Snapshot隔离级别使用Row Version 提供事务级别（Transaction-Level）的读一致性。在当前事务开始时，任何读操作，都基于相同的数据库snapshot。当读取被其他事务修改的数据行时，从tempdb中获取行版本数据。使用Snapshot隔离级别时，必须设置数据库选项ALLOW_SNAPSHOT_ISOLATION为ON；
  • When reading rows modified by another transaction, they retrieve the version of the row that existed when the transaction started.
• 在snapshot 和 read committed snpshot隔离级别下，事务读取的数据都是已提交的；
• 注意语句级别的读一致性和事务级别的读一致性是snapshot 和 read committed snpshot 最大的区别：
  • 事务级别的读一致性是指：在事务开始，到事务提交期间，该事务持有数据的一个快照。如果在该事务活动期间，其他事务更新表数据，该事务只会读取快照数据，不会读取到被其他事务更新的数据值；
  • 语句级别的读一致性是指：单个语句（single statement）看到的数据是一致性的；在当前事务活动期间，事务中的语句能够读取到被其他事务提交更新的数据值；例如，在语句stmt1执行时，事务没有提交更新，stmt1看到Reader1的值是2；当语句stmt2执行时，事务提交更新，stmt2看到Reader2的值是3；

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

在默认的隔离级别Read Commited下，在执行读操作时，事务申请shared lock，读写操作相互阻塞。在隔离级别Read Uncommitted下，事务不会申请shared lock，因此读操作不会阻塞写操作，但是读操作可能会读到脏数据。脏数据是指被其它尚未提交的事务修改之后的数据值，不是指更新之前的数据值。

行版本是指存储在tempdb中，含有数据行和TSN的数据。数据表的一个Data Row，可以有多个Row Version。修改操作发生时，SQL Server 创建一个Row Version，将Original Row复制到Row Version，并将当前事务的TSN也存储在Row Version中。因此，Row Version存储的是修改之前的数据值。

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

1，启用Snapshot隔离级别

设置数据库选项 ALLOW_SNAPSHOT_ISOLATION 为 ON，没有改变Session-Level的事务隔离级别，需要修改Session-Level的事务隔离级别为SNAPSHOT，才能使用行版本数据

alter database current
set allow_snapshot_isolation on;

在使用Snapshot隔离级别时，必须将当前Session的隔离级别设置为Snapshot，只有这样，当前事务才能访问Row Versioning的数据：

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

2，数据库选项READ_COMMITTED_SNAPSHOT（简称RCS）

在默认的隔离级别Read Committed下，使事务能够访问Row Versioning数据，需要将数据库选项READ_COMMITTED_SNAPSHOT设置为ON：

alter database current
set allow_snapshot_isolation on;

alter database current 
set read_committed_snapshot on;

前提是必须设置数据库选项ALLOW_SNAPSHOT_ISOLATION为ON；一旦启用RCS选项，在默认的Read Committed 隔离级别中，事务访问版本化的数据行。在RCS隔离级别下，事务有两个特性：

• 事务使用行版本（Row version）代替加锁，读操作不会阻塞其他事务的写操作；
• RCS隔离级别保证语句级别的事务一致性，查询语句只能读取在该语句执行时已经提交的数据，如果在该语句执行时数据更新尚未提交，该语句读取不到；

3，READ COMMITTED Snapshot隔离级别

在Read Committed 隔离级别下，事务不能读取被其他事务修改，但尚未提交的数据，即只能读取已提交更新的数据，READ COMMITTED隔离级别的行为受到数据库选项：READ_COMMITTED_SNAPSHOT的影响：

• 如果设置RCS选项为OFF（默认设置），数据库引擎使用Shared Lock阻止其他事务修改当前事务正在读取的数据；当读取被其他事务修改，但尚未提交更新的数据行时，该读操作将被阻塞；
  • If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed.
• 如果设置RCS选项为ON，数据库引擎使用行版本化（Row Versioning）的数据实现语句级别的一致性，不会阻塞其他事务的写操作，但只能读取已提交更新的数据
  • If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

三，启用快照隔离级别

1，使用snapshot 隔离级别

step1，设置数据库选项

ALTER DATABASE CURRENT SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE; 
ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON; 
--ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT OFF; 
ALTER DATABASE CURRENT SET MULTI_USER;

step2，修改Session-Level的隔离级别为snapshot

set transaction isolation level snapshot

2，使用Read_Committed_Snapshot隔离级别

ALTER DATABASE CURRENT SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE; 
ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON; 
ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON; 
ALTER DATABASE CURRENT SET MULTI_USER;

四，引用徐海蔚老师的例子，测试隔离级别的行为

 

snapshot隔离级别不会阻塞其他事务的写操作，该隔离级别忽略数据的修改操作，只读取row versioning的数据，就是说，读取到的是数据修改之前的版本，当snapshot事务尝试修改由其他事务修改的数据时，产生更新冲突，写操作异常终止。

read committed snapshot隔离级别，读取行版本化的已提交数据：

• 当其他事务未提交更新时，读取行版本化的数据，即读取修改之前的数据值；
• 当其他事务提交数据更新后，读取修改后数据值；
• 由于该隔离级别不会申请共享锁，因此不会阻塞其他事务的更新操作；
• 能够更新由其他事务修改的数据；

五，Snapshot隔离级别（翻译MSDN）

在SNAPSHOT隔离级别下，任何写操作都会将更新之前的数据行保存到tempdb中，读取操作要么从Original Database的数据表中读取数据，要么从tempdb中读取行版本数据。Snapshot隔离级别指定：在一个事务中，任何语句读取的数据，是事务一致性的版本。事务一致性是指在事务开始时，在表级别创建数据快照，只能识别其他事务已提交的数据更新。在事务开始之后，当前事务不会识别其他事务执行的数据更新。Sanpshot隔离级别实现事务级别的数据一致性。SQL Server 使用tempdb来存储行版本化（row versioning）的数据，如果数据更新较多，存储的行版本太多，会导致tempdb成为系统瓶颈。

Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

1，在Snapshot隔离级别下，更新操作创建Row Version

一旦启用Snapshot隔离级别，在事务中执行更新操作时，SQL Server将被更新的数据行的原始版本存储在tempdb中，即在tempdb中保存数据行的Original data，因此，读取行版本的数据，都只能读取到数据行被更新之前的值。每一个事务都拥有一个唯一的，递增的顺序号，记作TSN（Transaction Sequence Number），TSN能够唯一标识一个事务，每一个行版本都存储一个TSN，标识创建该行版本的事务。

Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version.

2，Snapshot隔离实现事务一致性

Snapshot隔离级别实现事务级别的数据一致性，这意味着，在单个事务中的所有查询语句，看到的是相同版本的数据。在Snapshot隔离级别下，事务在读取数据不需要加行级锁或页级锁，读写操作互不阻塞。

The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.

3，Snapshot 使用乐观并发模式

Snapshot隔离级别使用乐观并发模式，如果一个Snapshot 事务尝试去提交数据行的更新，但是该数据行已经被其他事务修改，并且修改的时间早于当前事务开始的时间，那么SQL Server将当前事务作为失败者，并回滚其事务操作。乐观并发模式用于冲突较少的环境中，如果Application在更新数据时经常发生冲突，Snapshot隔离级别可能不是最好的选择。

Snapshot isolation uses an optimistic concurrency model. If a snapshot transaction attempts to commit modifications to data that has changed since the transaction began, the transaction will roll back and an error will be raised. 

4，Snapshot 隔离和 Row Version的工作模式

当启用Snapshot隔离级别时，每一个更新数据的操作都会在tempdb中存储该行的原始副本，术语叫作行版本（RowVersion），SQL Server为每个行版本添加事务的TSN，该TSN能够唯一标识更新操作所在的事务。读操作在读数据时，按照以下顺序进行：

• 创建一个新的事务，为其分配TSN，一个唯一，递增的序号；
• snapshot事务从数据表中读取数据行，从tempdb中读取行版本（row version），该行版本的TSN最接近当前事务的TSN，但比当前事务的TSN小；
• 在创建Snapshot时，从已提交的事务中获取行版本数据，如果行版本数据标识的事务尚未提交，那么从更早的事务中获取已提交更新的数据；
• 事务从tempdb中读取行版本数据，事务不会看到新插入的数据，因为插入数据的TSN比当前事务的TSN大；
• 事务能够看到被其他事务删除的数据，前提是删除数据的事务的TSN比当前事务的TSN大，这是因为其他事务将行版本保存到tempdb中，当前事务从tempdb中读取行版本数据；

When the SNAPSHOT isolation level is enabled, each time a row is updated, the SQL Server Database Engine stores a copy of the original row in tempdb, and adds a transaction sequence number to the row. The following is the sequence of events that occurs:

• A new transaction is initiated, and it is assigned a transaction sequence number.

• The Database Engine reads a row within the transaction and retrieves the row version from tempdb whose sequence number is closest to, and lower than, the transaction sequence number.

• The Database Engine checks to see if the transaction sequence number is not in the list of transaction sequence numbers of the uncommitted transactions active when the snapshot transaction started.

• The transaction reads the version of the row from tempdb that was current as of the start of the transaction. It will not see new rows inserted after the transaction was started because those sequence number values will be higher than the value of the transaction sequence number.

• The current transaction will see rows that were deleted after the transaction began, because there will be a row version in tempdb with a lower sequence number value.

The net effect of snapshot isolation is that the transaction sees all of the data as it existed at the start of the transaction, without holding or placing any locks on the underlying tables. This can result in performance improvements in situations where there is contention.

A snapshot transaction always uses optimistic concurrency control, with holding any locks that would prevent other transactions from updating rows. If a snapshot transaction attempts to commit an update to a row that was changed after the transaction began, the transaction is rolled back, and an error is raised.

 

参考文档：

Snapshot Isolation in SQL Server

Isolation Levels in the Database Engine

SQL SERVER – Difference Between Read Committed Snapshot and Snapshot Isolation Level