[轉帖]資料庫的快照隔離級別（Snapshot Isolation）

https://developer.aliyun.com/article/386376

簡介：

隔離級別定義事務處理資料讀取操作的隔離程度，在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的隔離級別：

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

DBCC UserOptions

一，事務的隔離級別

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

作者： 悅光陰

出處： http://www.cnblogs.com/ljhdo/

本文版權歸作者和部落格園所有，歡迎轉載，但未經作者同意，必須保留此段宣告，且在文章頁面醒目位置顯示原文連線，否則保留追究法律責任的權利。

分類: 資料庫管理

標籤: SQL Server, Snapshot Isolation