SQL Server 中的6個交易隔離等級簡介

標籤：不同的   簡單   理解   控制   完全   共用   定義   執行計畫   com   

 

本文出處：http://www.cnblogs.com/wy123/p/7218316.html 
（保留出處並非什麼原創作品權利，本人拙作還遠遠達不到，僅僅是為了連結到原文，因為後續對可能存在的一些錯誤進行修正或補充，無他）

 

資料庫中的事物是具有原子性(Atomicity)，一致性(Consistemcy)，隔離性(Isolation)，持久性(Durability)四個特徵。
在上述四個特性中的一致性和隔離性的實現中，是通過鎖來實現對相同資料的訪問隔離的。
事物的隔離等級又可以影響鎖的申請和時間的時機。
因此，不同的事物隔離等級又可以對鎖的申請和釋放產生不同的影響，因此，在對資料庫做事物控制的時候需要瞭解隔離等級對事物的影響。
SQL Server實現SQL99標準規定的事務的四個隔離等級（未提交讀，已提交讀，可重複讀，序列化）之外，另外增加了兩個隔離等級（快照個基於行版本的已提交讀隔離等級）。
不同的隔離等級對控制髒讀，不可重複讀取，幻讀有一定的控制，也會並發有一定程度的影響，
隔離等級越低，並發性越高，但是產生髒讀，不可重複讀取，幻讀等可能性越大；隨著事物隔離等級的提交，可以控制髒讀，不可重複讀取，以及幻讀的現象，但是並發性也會隨之降低。
事物隔離等級和執行計畫都可以影響鎖（範圍）的申請和釋放時機，本文暫不討論執行計畫對鎖申請的影響，僅在隔離等級上說明鎖的申請和釋放。
以下簡單介紹SQL Server中的六個隔離等級以及每個隔離等級的特徵，在此基礎上說明每個隔離等級可能存在的問題解決方案。

未提交讀

運行當前Session讀取其他事務已修改但是尚未提交的資料，也即當前Session可以讀取到“髒資料”。
當前Session不會對讀取的資料加共用鎖定。
set transaction isolation level read uncommitted;
或者
select * from table with(nolock)

特點：未提交讀是最低的一種隔離等級。
存在的問題：髒讀，不一致讀，幻讀等。

如下是未提交度的存在髒讀一種示範。

 　　

 

已提交讀

set transaction isolation level read committed;
或者
select * from table  預設人就是已提交讀
運行當前Session不能讀取其他事務已修改但是尚未提交的資料。
如果其他事務提對當前Session讀取的資料有修改且尚未提交，當前Session被阻塞。
原因是在以已提交讀隔離等級情況下：當前Session會對讀取的資料加共用鎖定，如果遇到讀取的資料尚未提交，當前查詢被阻塞。

特點：相比為提交讀隔離等級，解決了未提交讀隔離等級下的讀取“髒資料”的問題，
存在的問題：存在不可重複度或者幻讀的問題。

 

已提交讀隔離等級下存在不可重複讀取的現象（兩次讀取的同一行資料結果不一致）

 

 

 不可重複讀取隔離等級下存在的幻讀現象（一個事物中，同樣的條件，讀到的資料行數不一致）

 　　

 

可重複讀

set transaction isolation level repeatable read;
運行當前Session不能讀取其他事務已修改但是尚未提交的資料，並且當前Session運行期間，其他Session不能修改當前Session讀取到的資料
也就是說，當前Session運行期間，讀取到的資料是被加了共用鎖定的，所加的共用鎖定一直保持，直到事務提交的時候才釋放。
相比已提交讀最大的特點就是事務運行期間，共用鎖定將一直保持，直到當前Session事務提交，
因此可以保持當前Session讀取到的資料不被其他Session修改，所以就不存在兩次讀取的資料不一致的現象。
可重複讀隔離等級解決了不可重複讀取的問題，原因就是在當前Session執行期間，第一次查詢的共用鎖定一直保持到事務結束，
在此期間，其他Session無法修改當前Session讀取的資料，因此可以實現可重複讀。

特點：相比前一種隔離等級，可重複讀解決了已提交讀隔離等級的不可重複讀取的問題，也即兩次讀取的同一行資料是一致的
存在的問題：相比已提交讀，依舊存在幻讀的問題。

 

　　如下是可重複讀隔離等級的幻讀的現象，也即在同一個事物的兩次讀取期間，其他事物可以寫入當前事物讀取的資料（範圍）

　　

可序列化

當前Session不能讀取其他Session已修改但未提交的資料（不允許髒讀）
當前Session讀取的資料上的共用鎖定一直保持直到事務提交（可重複讀）
當前Session事務提交之前，其他Session不能插入當前Session中讀取的索引值（解決了幻讀的問題）
set transaction isolation level serializable
或者開啟事務之後對錶加holdlock提示
select * from table with(holdlock) where id = n
可序列化解決了另外一個非常經典的問題，使用update table with(holdlock) 或者select * from table with(xlock,holdlock)，並發情況下的“存在則更新不存在則插入”重複插入的問題。
參考：http://www.cnblogs.com/TeyGao/p/6929246.html

可序列化鎖定的原理是加範圍鎖的方式來實現的，當一個Session發起了請求之後，對於當前Session範圍內的資料，不管是否存在，都加一個共用鎖定。
比如在可序列化的隔離等級之下，select * from table with where id>=100 and id<= 120
在Session執行期間，SQL Server會鎖定 100<=id<= 120這個範圍的資料，不管表中這個區間是否存在資料， 都鎖定這個Id的範圍，不允許該Id範圍的資料寫入。
也即100<=id<= 120這個範圍被所鎖定（無法增加刪除或者修改這個範圍的資料）

 

可序列化隔離等級解決了幻讀的問題，也就是說，當前事物的兩次讀中間，其他Session對當前Session讀取資料範圍之內的資料修改的時候，會被阻塞，直到當前事物提交。

 　　

 

 

基於資料列版本設定的隔離等級

　　預設隔離等級，也即已提交讀隔離等級下，存在一個明顯的問題就是寫會阻塞讀，也就是說，一個寫資料的事物未提交之前，會阻塞其他事物對當前操作資料的讀取，直到當前寫事物的操作提交。
　　基於資料列版本設定的已提交讀隔離等級下，寫不會阻塞讀，寫資料的事物未提交之前，會將修改的資料之前的版本，寫入臨時資料庫，
　　讀資料的事物在讀取的時候，發現要讀取的資料被修改，會轉向臨時庫中讀取出來一個寫事物修改資料之前的版本，這樣可以在一定程度上提高並發性（當然臨時庫會承擔一定的壓力）。
　　SQL Server有兩種基於資料列版本設定的隔離等級：快照隔離等級（snapshot）和基於資料列版本設定的已提交讀隔離等級（read_committed_snapshot）
 　   兩種資料列版本設定分別要基於資料層級開啟allow_snapshot_isolation和read_committed_snapshot

（1）快照隔離等級（snapshot）

資料庫層級設定快照隔離等級
alter database Test set allow_snapshot_isolation on;

 

 Session層級設定快照隔離等級：set transaction isolation level snapshot

 

快照隔離等級最大的特點是，當前Session讀取其他事物修改的資料的時候，不會被阻塞，讀取的是其他事物已經修改，但是尚未提交的資料
但是當前事物嘗試修改“在其他其他事物中提交修改之後的資料”，會報錯。
具體過程如下，從時間的維度來看，步驟如下
1）Session2 開啟事物，修改Id =1的資料，暫不提交
2）Session1 讀取id=1的資料，不會被阻塞，讀取到的是Session2修改之前的資料的版本
3）Session2修改Id =1的資料之後，事物提交
4）Session1嘗試修改Id=1的資料，報錯

　　

　　實際操作上看，如下

 

（2）基於資料列版本設定的已提交讀隔離等級（read_committed_snapshot）

資料庫層級設定為基於資料列版本設定的已提交讀隔離級
alter database Test set read_committed_snapshot on;
go

--將當前事物設定為已提交讀快照隔離等級
set transaction isolation level read committed

快照隔離等級最大的特點是，當前Session讀取其他事物修改的資料的時候，不會被阻塞，讀取的是其他事物已經修改，但是尚未提交的資料
與快照隔離等級相對，當前Session嘗試修改“在其他其他Session中提交修改之後的資料”，可以成功提交。
具體過程如下，從時間的維度來看，步驟如下
1）Session2 開啟事物，修改Id =1的資料，暫不提交
2）Session1 讀取id=1的資料，不會被阻塞，讀取到的是Session2修改之前的資料的版本
3）Session2修改Id =1的資料之後，事物提交
4）Session1嘗試修改Id=1的資料，成功提交，

　　基於資料列版本設定的已提交讀隔離等級最大的特點是，當前讀取的資料是，其他Session已修改尚未提交之前的版本，但是當前事物嘗試修改時，可以成功提交
　　這樣一來，就忽略掉了當前事物運行期間，其他事物修改且提交的那個版本的資料，有點繞，需要慢慢理解。

　　資料列版本設定的已提交讀隔離等級的問題也很明顯，當前Session讀取資料的時候，是其他事物修改之前的版本，當前Session對讀取到的資料可以在其他事物提價之前的版本上執行修改，
　　而忽略了當前Session在讀和寫的間隔期間，其他Session修改並且提交事物的影響，為此可能會產生一定程度的影響。

 

 　　從時間維度上看如所示

 

 

具體執行現象如下：
存在的問題就是，Session1第一次讀取的時候，讀取的Id = 1資料的那麼是AAA，實際上此時其他Session2已經將Id = 1的那麼修改為了Update_AAA，
隨後Session2事物提交，當前Session執行修改的時候，忽略了Session2修改後的資料，可以直接將資料修改為AAA+++
需要注意的是，Session1修改成功的前提是Session2的事物提交，如果Session2修改事物沒有提交，Session1的修改操作被阻塞。

中第一行的備忘沒有修改過來，應該是快照已提價讀隔離等級

 　　

　　

 總結：

　　本文簡單闡述了SQL Server中的幾種隔離等級，SQL Server實現了SQL99定義的四個標準隔離等級，並且額外實現了兩個快照隔離等級。
　　需要說明的是，不同的DBMS的預設隔離等級和對隔離等級的實現是不完全一樣的，也不一定是完全按照SQL99定義的四個標準隔離等級來實現的，
　　因此在做事物控制的時候，需要瞭解具體的隔離等級以及具體特性。

SQL Server 中的6個交易隔離等級簡介