SQL Server 事务隔离级别的解析
近來在項目中遇到的一些有關事務的問題,跟同事間討論了一下,后面翻看了一些書籍和做了一些測試,趁有點時間把它寫下來,一來加深印象,二來希望對大家有所幫助,當然,由于自身水平問題,如理解有誤,還請大牛指出, 本人在此先行謝過.
事情首先是這樣引起的, 同事寫的一個導入,但在導入的過程中,由于要插多條數據,當有些數據未能插入時,卻沒有回滾所有的數據,而是往下執行,這樣問題就來了,無法得知系統到底導了多少條記錄.用戶需要的結果是,要么都導入成功,要么都不成功.
if OBJECT_ID('tb1') is not null drop table tb1 go create table tb1(id int identity primary key ,name varchar(10)) -- set xact_abort on begin transaction testinsert into tb1 values('getonjew'); insert into tb1 values('caozx'); insert into tb1 values('kevin-xxxxxxxx'); insert into tb1 values('toby'); insert into tb1 values('idawong'); commit transaction testgo select * from tb1以上代碼得出信息與結果
信息
(1 row(s) affected)
(1 row(s) affected)
Msg 8152, Level 16, State 14, Line 7 String or binary data would be truncated. The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
(4 row(s) affected)
結果(這里大家要注意下,即使第三條數據未成功,但其ID已被占用)
1?getonjew
2?caozx
4?toby
5?idawong
這里就是與項目中遇到的情況類似了,但這不是我們想要的結果,如果想達到想要的結果,只需要把上面的注釋行.set xact_abort on??打開即可了. 但這不是這里所要講述的. 有關事務的問題,我重新的梳理了一下. 現整理如下.請大家批評指證.
SQL事務級別用于控制并發用戶如何讀寫數據的操作，同時對性能也有一定的影響作用。大家應根據實際的使用情況使用不同的級別.
事務隔離級別主要通過影響讀操作來間接地影響寫操作；可以在會話級別上設置事務隔離的級別,也可以在表上設置事務隔離級別。
事務隔離級別總共有6個級別：
READ UNCOMMITTED(未提交讀,讀臟),相當于(NOLOCK)
READ COMMITTED(已提交讀,默認級別)
REPEATABLE READ(可以重復讀),相當于(HOLDLOCK)
SERIALIZABLE(可序列化)
SNAPSHOT(快照)
READ COMMITTED SNAPSHOT(已經提交讀隔離)
對于前四個隔離級別：READ UNCOMMITTED<READ COMMITTED<REPEATABLE READ<SERIALIZABLE
隔離級別越高,讀操作的請求鎖定就越嚴格,鎖的持有時間久越長;所以隔離級別越高,一致性就越高,并發性就越低,同時性能也相對影響越大.
首先,可以在命令窗口輸入 ?DBCC USEROPTIONS 查看目前隔離級別,這里同時能查看到一些其它的屬性.
?
下面我們來說一下如何設置會話隔離級別
SET TRANSACTION ISOLATION LEVEL <ISOLATION NAME> --設置查詢表隔離 SELECT ....FROM <TABLE> WITH (<ISOLATION NAME>)?1.READ UNCOMMITTED
READ UNCOMMITTED:未提交讀，可能讀到臟數據
READ UNCOMMITTED:讀操作不申請鎖,運行讀取未提交的修改,也就是允許讀臟數據,讀操作不會影響寫操作請求排他鎖.
下面舉例說明:
建立一個Courses表, 里面包含以下數據
新建一會話(即新開一查詢窗口)運行以下命令
BEGIN TRANSACTION UPDATE Courses SET SCORE=SCORE+7 WHERE ID=1SELECT ID,SCORE FROM Courses WHERE ID=1得到以下結果
新建另一個會話,執行
/*先不添加隔離級別,默認是READ COMMITTED,由于數據之前的更新操作使用了排他鎖(事務沒有提交), 查詢一直在等待鎖釋放*/ SELECT ID,SCORE FROM Courses WHERE ID=1如果將隔離級別設置為
---將查詢的隔離級別設置為READ UNCOMMITTED允許未提交讀，讀操作之前不請求共享鎖。 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT ID,SCORE FROM Courses WHERE ID=1 --當然也可以使用表隔離,效果是一樣的 SELECT ID,SCORE FROM Courses WITH (NOLOCK) WHERE ID=1得出結果
假設在會話1中對操作執行回滾操作ROLLBACK TRANSACTION,這樣分數還是之前的80但是會話2中則讀取到的是回滾前的分數87,這樣就屬于一個讀臟操作.
2.READ COMMITTED
READ COMMITTED（已提交讀）是SQL SERVER默認的隔離級別，可以避免讀取未提交的數據，隔離級別比READ UNCOMMITTED未提交讀的級別高; 該隔離級別讀操作之前首先申請并獲得共享鎖，允許其他讀操作讀取該鎖定的數據，但是寫操作必須等待鎖釋放，一般讀操作讀取完就會立刻釋放共享鎖。
首先,再提交之前會話1的代碼
BEGIN TRANSACTION UPDATE Courses SET SCORE=SCORE+7 WHERE ID=1SELECT ID,SCORE FROM Courses WHERE ID=1這個時候,在會話2中,讀取數據時一直在等待. 直到會話1提交了事務之后
COMMIT TRANSACTION此時在會話2才能讀到數據,但這個時候讀到的數據結果是修改后的結果,所以讀的不是臟數據.
但是由于READ COMMITTED讀操作一完成就立即釋放共享鎖,讀操作不會在一個事務過程中保持共享鎖,也就是說在一個事務的的兩個查詢過程之間有另一個會話對數據資源進行了更改,會導致一個事務的兩次查詢得到的結果不一致,這種現象稱之為不可重復讀,這個時候我們就要引入更高一級的隔離級別了.
?3.REPEATABLE READ
REPEATABLE READ(可重復讀)：保證在一個事務中的兩個讀操作之間，其他的事務不能修改當前事務讀取的數據，該級別事務獲取數據前必須先獲得共享鎖同時獲得的共享鎖不立即釋放一直保持共享鎖至事務完成，所以此隔離級別查詢完并提交事務很重要。
首先,我們重置我們ID=1的數據為80,
在會話1中執行查詢ID=1,將回話級別設置為REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION SELECT ID,SCORE FROM Courses WHERE ID=1新建會話2修改ID=1的分數.
---由于會話1的隔離級別REPEATABLE READ申請的共享鎖一直要保持到事務結束，所以回話2無法獲取排他鎖，處于等待狀態 UPDATE Courses SET SCORE=SCORE+7 WHERE ID=1在會話1中執行下面語句，然后提交事務
SELECT ID,SCORE FROM Courses WHERE ID=1 COMMIT TRANSACTION得出以下結果,結果一致.
?
會話1的兩次查詢得到的結果一致，前面的兩個隔離級別無法得到一致的數據，此時事務已提交同時釋放共享鎖，會話2申請排他鎖成功，對行執行更新操作.
REPEATABLE READ隔離級別保證一個事務中的兩次查詢到的結果一致，同時保證了丟失更新,所謂的丟失更新是：兩個事務同時讀取了同一個值然后基于最初的值進行計算，接著再更新，就會導致兩個事務的更新相互覆蓋。 例如公司開會申請會議室，兩個人同時預定同一會議室，首先兩個人同時查詢到還有一間房間可以預定，然后兩個人同時提交預定操作，事務1執行num=1-1，同時事務2也執行num=1-1最后修改num=0,這就導致兩個人其中一個人的操作被另一個人所覆蓋，REPEATABLE READ隔離級別就能避免這種丟失更新的現象，當事務1查詢房間時事務就一直保持共享鎖直到事務提交，而不是像前面的幾個隔離級別查詢完就不共享鎖，就能避免其他事務獲取排他鎖。(當然這里只是舉做例子,在實際開發中未必需要設置這個級別,可以在提交的過程中再到數據庫驗證一下,如果符合條件就訂會議室,否則給出提示,根據情況而定)
4.SERIALIZABLE
SERIALIZABLE(可序列化)，對于前面的REPEATABLE READ能保證事務可重復讀，但是事務只鎖定查詢第一次運行時獲取的數據資源（數據行），而不能鎖定查詢結果之外的行，就是原本不存在于數據表中的數據。因此在一個事務中當第一個查詢和第二個查詢過程之間，有其他事務執行插入操作且插入數據滿足第一次查詢讀取過濾的條件時，那么在第二次查詢的結果中就會存在這些新插入的數據，使兩次查詢結果不一致，這種讀操作稱之為幻讀。 為了避免幻讀需要將隔離級別設置為SERIALIZABLE
-- 先測試一下,之前的可重復讀不能保證幻讀 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION SELECT ID,SCORE FROM Courses得到以下結果
然后在會話2插入一條數據.
INSERT INTO Courses VALUES(6,99)返回會話1,再查詢并提交事務.
SELECT ID,SCORE FROM Courses COMMIT TRANSACTION得出結果
第二次查詢到的數據包含了會話2新插入的數據，兩次查詢結果不一致（驗證之前的隔離級別不能保證幻讀）
如果在前面設置隔離級別為SERIALIZABLE的話,則兩次的查詢結果會一致, 也即會話2插入的數據,不會在第二次查詢結果中得到.
實驗完后,重置會話級別顯默認級別
SET TRANSACTION ISOLATION LEVEL READ COMMITTED5.SNAPSHOT
SNAPSHOT快照：SNAPSHOT和READ COMMITTED SNAPSHOT兩種隔離（可以把事務已經提交的行的上一版本保存在TEMPDB數據庫中） SNAPSHOT隔離級別在邏輯上與SERIALIZABLE類似, READ COMMITTED SNAPSHOT隔離級別在邏輯上與 READ COMMITTED類似 不過在快照隔離級別下讀操作不需要申請獲得共享鎖，所以即便是數據已經存在排他鎖也不影響讀操作。而且仍然可以得到和SERIALIZABLE與READ COMMITTED隔離級別類似的一致性；如果目前版本與預期的版本不一致，讀操作可以從TEMPDB中獲取預期的版本。
如果啟用任何一種基于快照的隔離級別，DELETE和UPDATE語句在做出修改前都會把行的當前版本復制到TEMPDB中，而INSERT語句不需要在TEMPDB中進行版本控制，因為此時還沒有行的舊數據
無論啟用哪種基于快照的隔離級別都會對更新和刪除操作產生性能的負面影響，但是有利于提高讀操作的性能因為讀操作不需要獲取共享鎖；
?
5.1 SNAPSHOT
SNAPSHOT 在SNAPSHOT隔離級別下,當讀取數據時可以保證操作讀取的行是事務開始時可用的最后提交版本 同時SNAPSHOT隔離級別也滿足前面的已提交讀,可重復讀,不幻讀;該隔離級別實用的不是共享鎖,而是行版本控制 使用SNAPSHOT隔離級別首先需要在數據庫級別上設置相關選
在打開的所有查詢窗口中執行以下操作
ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON;然后
在會話1中打開事務,將ID=1的分數加7,并查詢跟新后的分數 BEGIN TRANSACTION UPDATE Courses SET SCORE=SCORE+7 WHERE ID=1SELECT ID,SCORE FROM Courses WHERE ID=1 ---查詢到更新后的分數87---在會話2中將隔離級別設置為SNAPSHOT,并打開事務(此時查詢也不會因為會話1的排他鎖而等待,依然可以查詢到數據) SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRANSACTION SELECT ID,SCORE FROM Courses WHERE ID=1---查詢到的結果還是會話1修改前的分數,由于會話1在默認的READ COMMITTED隔離級別下運行,SQL SERVER必須在更新前把行的一個副本復制到TEMPDB數據庫中 --在SNAPSHOT級別啟動事務會請求行版本---現在在會話1中執行提交事務,此時ID=1的分數是87 COMMIT TRANSACTION---再次在會話2中查詢ID=1的分數并提交事務,結果還是80,因為事務要保證兩次查詢的結果相同SELECT ID,SCORE FROM Courses WHERE ID=1COMMIT TRANSACTION---此時如果在回話2中重新打開一個事務,查詢到的ID=1的分數是87 BEGIN TRANSACTION SELECT ID,SCORE FROM Courses WHERE ID=1COMMIT TRANSACTION/*SNAPSHOT隔離級別保證操作讀取的行是事務開始時可用的最后已提交版本,由于會話1的事務未提交,所以ID=1的最后提交版本還是修改前的分數80,所以會話2讀取到的價格是會話2事務開始前的已提交版本分數80,當會話1提交事務后,會話2重新新建一個事務此時事務開啟前的分數已經是87了，
所以查詢到的分數是87，同時SNAPSHOT隔離級別還能保證SERIALIZABLE的隔離級別*/
5.2 READ COMMITTED SNAPSHOT
READ COMMITTED SNAPSHOT也是基于行版本控制,但是READ COMMITTED SNAPSHOT的隔離級別是讀操作之前的最后已提交版本,而不是事務前的已提交版本，有點類似前面的READ COMMITTED能保證已提交讀,但是不能保證可重復讀,不能避免幻讀,但是又比 READ COMMITTED隔離級別多出了不需要獲取共享鎖就可以讀取數據
要啟用READ COMMITTED SNAPSHOT隔離級別同樣需要修改數據庫選項，在會話1，會話2中執行以下操作(執行下面的操作當前連接必須是數據庫的唯一連接，可以通過查詢已連接當前數據庫的進程,然后KILL掉那些進程,然后再執行該操作,否則可能無法執行成功)
?
開始前重置 ID=1的分數為80
-----在會話1中打開事務,將ID=1的分數加7,并查詢跟新后的分數,并保持事務一直處于打開狀態 BEGIN TRANSACTION UPDATE Courses SET SCORE=SCORE+7 WHERE ID=1--查詢到的分數是87, select ID,SCORE FROM Courses WHERE ID=1---在會話2中打開事務查詢ID=1并一直保持事務處于打開狀態(此時由于會話1還未提交事務,所以會話2中查詢到的還是會話1執行事務之前保存的行版本) BEGIN TRANSACTION select ID,SCORE FROM Courses WHERE ID=1 --查詢到的分數還是80---在會話1中提交事務 COMMIT TRANSACTION ---在會話2中再次執行查詢ID=1的分數,并提交事務 select ID,SCORE FROM Courses WHERE ID=1 COMMIT TRANSACTION --此時的分數為會話1修改后的分數87,而不是事務之前已提交版本的價格,也就是READ COMMITTED SNAPSHOT隔離級別在同一事務中兩次查詢的結果不一致.?
?
?
?
?
?
轉載于:https://www.cnblogs.com/Geton/p/4023964.html
總結
以上是生活随笔為你收集整理的SQL Server 事务隔离级别的解析的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 探秘腾讯Android手机游戏平台之不安
- 下一篇： TinyXml高速入门（一）