資料庫的快照隔離級別（Snapshot Isolation）

隔離級別定義事務操作資源和更新數據的隔離程度，在SQL Server中，隔離級別隻會影響讀操作申請的共用鎖，而不會影響寫操作申請的互斥鎖。隔離級別控制事務在執行讀操作時：在讀數據時是否使用共用鎖，申請何種類型的隔離級別；事務持有讀鎖的時間讀操作引用其他事務更新的數據行時，控制讀操作的行為：被 ...

隔離級別定義事務操作資源和更新數據的隔離程度，在SQL Server中，隔離級別隻會影響讀操作申請的共用鎖，而不會影響寫操作申請的互斥鎖。隔離級別控制事務在執行讀操作時：

在讀數據時是否使用共用鎖，申請何種類型的隔離級別；
事務持有讀鎖的時間
讀操作引用其他事務更新的數據行時，控制讀操作的行為：
- 被阻塞，等待其他事務釋放互斥鎖；
- 讀取事務提交後的版本，該數據行在事務開始時存在；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的隔離級別：

SET TRANSACTION ISOLATION LEVEL
     READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE

DBCC UserOptions

View Code

一，事務的隔離級別

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，直到事務結束（提交或回滾）；

SQL Server支持使用Row Versioning的隔離級別，事務的讀操作只申請SCH-S 表級鎖，不會申請Page 鎖和Row 鎖：

當資料庫選項 READ_COMMITTED_SNAPSHOT設置為ON，Read Committed隔離級別使用Row Versioning提供語句級別（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 Versioning 提供事務級別（Transaction-Level）的讀一致性。當讀取被其他事務修改的數據行時，獲取在當前事務開始時的行版本數據，使用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 最大的區別：
- 事務級別的讀一致性是指：在事務開始，到事務提交期間，該事務持有表數據的一個快照。如果在該事務活動期間，其他事務更新表數據，該事務只會讀取快照數據，不會讀取到被其他事務更新的數據值；
- 語句級別的讀一致性是指：事務持有的數據快照，在語句結束時，立即釋放；在事務活動期間，能夠讀取到其他事務提交更新的數據值；

二，使用Row Versioning的隔離級別

在預設的隔離級別Read Commited下，在執行讀操作時，事務申請shared lock，讀寫操作相互阻塞。在隔離級別Read Uncommitted下，事務不會申請shared lock，因此讀操作不會阻塞寫操作，但是讀操作可能會讀到臟數據。臟數據是指被其它尚未提交的事務修改之後的數據值，不是指更新之前的數據值。

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）

使用Row-Versioning數據時，資料庫選項ALLOW_SNAPSHOT_ISOLATION必須設置為ON，將資料庫選項READ_COMMITTED_SNAPSHOT設置為ON，在預設的隔離級別Read Committed下，事務能夠訪問Row Versioning數據：

alter database current
set allow_snapshot_isolation on;

alter database current 
set read_committed_snapshot on;

前提是必須設置資料庫選項ALLOW_SNAPSHOT_ISOLATION為ON，一旦啟用RCS選項，在預設的Read Committed 隔離級別中，事務使用行版本（row versioning）代替加鎖，事務能夠訪問版本化的數據行。Read Committed Snapshot隔離級別保證語句級別的事務一致性，TSQL語句只能讀取在該語句開始時已經提交的數據。當前事務的讀操作不會阻塞其他事務的寫操作，當前事務讀取的是其他事務已提交修改的數據值。

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. Setting the READ_COMMITTED_SNAPSHOT ON option allows access to versioned rows under the default READ COMMITTED isolation level.

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.

4，快照隔離級別

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 隔離級別

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;