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能夠避免掉所有的並發問題.
| Dirty Read | Unrepeatable Read | Phantom Read | |
| Read uncommitted | V | V | V |
| Read committed | - | V | V |
| Read committed with snapshot | - | V | V |
| Repeatable read | - | - | V |
| Snapshot | - | - | - |
| Serializable | - | - | - |
關於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的效能要求也相對高,這點需注意
參考
