Aaron Yang

ISO標準在SQL  Server中的MSSQL的隔離層級,再另一篇文章中有介紹,

此篇文章介紹使用資料列版本控制(Row Version)的隔離層級,

資料列版本控制

資料列版本控制最重要的要點,

使用snapshot的方式,當將要修改資料前,給于一版本號,交資料存入tempdb中,

當需要讀取時,就可以從tempdb中讀取,而不在將讀取的資料使用共享鎖.

從而減少了鎖開銷與資源等待. ==> 減少阻塞與dead lock的發生

1. Read Committed Snapshot 

     藉由陳述式層級的快照的方式實作了Read Committed隔離層級.

     陳述式就是T-SQL的語句,一般會變動資料的就是insert, update, delete.

2. Snapshot Isolation(快照集隔離)

     交易等級的讀取一致性. (begin trans ....... commit/rollback)

由下表可知,Read committed with snapshot是利用snapshot達到原本使用共享鎖的read committed層級,

而Snapshot Isolation能夠避免掉所有的並發問題.

關於snapshot isolation的運作方式可參考

SQL Server 中的快照集隔離內的快照集隔離及資料列版本控制的運作方式,

設定Read Committed with Snapshot

   將READ_COMMITTED_SNAPSHOT設定為ON

   T-SQL
   ALTER DATABASE DB_name SET READ_COMMITTED_SNAPSHOT ON

   基本上資料庫引擎的預設隔離層級為Read Committed,只要啟用READ_COMMITTED_SNAPSHOT,

   所有的sessions就使用Read Committed with Snapshot.

設定Snapshot Isolation

   將ALLOW_SNAPSHOT_ISOLATION設定為ON

   T-SQL
   ALTER DATABASE DB_name SET ALLOW_SNAPSHOT_ISOLATION ON

   如果要使用Snapshot Isolation,

   則該Session TSQL前需要先執行以下的隔離層級設定.

   SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

   and then your T-SQL...

啟用快照隔離也可以從SSMS上啟用,如下

Snapshot Isolation使用說明

首先對Test資料庫啟用ALLOW_SNAPSHOT_ISOLATION,

並從sys.databases 檢視表查看

更新一筆資料但不提交事務,但此時已經啟用快照功能,

在預設隔離層級下查詢該筆被更新的資料,無法回傳結果

查詢另一比沒有更新的資料,回傳正確資料

該session設定為snapshot isolation,查詢備更新的資料,回傳更新前的數據eric

開啟READ_COMMITTED_SNAPSHOT,讓session預設的隔離層級改為read committed with snapshot.

再次查詢被修改的資料,可以得到修改前的資料

提交修改後,

額外開三個查詢頁,來看看交易等級與陳述式層級的範圍性.

• 不使用事務的read committed with snapshot隔離
• 使用事務的read committed with snapshot隔離
• 使用事務的snapshot isolation隔離

第一次查詢回傳結果都是noon,

此時再次修改該筆資料

則不使用事務的read committed with snapshot隔離會查到新的值ethan.

(每一個陳述式都具原子性,所以查到最接近當下的資料列版本,就是剛才commit的值)

(不使用事務的snapshot isolation也是這個結果,因為忘了截圖,僅文字說明)

使用事務的read committed with snapshot隔離會查到新的值ethan.

(read committed with snapshot所保證的讀取一致性,是陳述式範圍)

使用事務的snapshot isolation隔離會查到舊的值noon.

(snapshot isolation所保證的讀取一致性,是交易等級)

雖然利用資料列版本控制有很多好處,但對tempdb的效能要求也相對高,這點需注意

參考

Database Engine 中資料列版本控制式的隔離等級

資料列版本控管 SnapShot Isolation V.S Read Committed Snapshot