關于SQL Server事務隔離級別的探討

摘 要： 隔離級別用于決定如何控制并發用戶讀寫數據的操作，讀操作可以是任何檢索數據的語句，默認使用共享鎖。寫操作是指任何對表作出修改的語句，需要使用排他鎖。對于操作獲得的鎖，以及鎖的持續時間而言，雖然不能控制寫操作的處理方式，但可以控制讀操作的處理方式。當然，作為對讀操作的行為進行控制的一種結果，也將隱含地影響寫操作的行為方式。為此，可以在會話級別上用會話選項來設置隔離級別。

關鍵詞： 并發； 隔離級別； 排他鎖； 共享鎖

中圖分類號：TP311 文獻標志碼：A 文章編號：1006-8228（2015）12-38-03

Discussion on SQL server transaction isolation level

Liu Lijuan

（Zhejiang Agriculture and Forestry University， Lin'an， Zhejiang 311300， China）

Abstract： The isolation level is used to determine how to control the operation of concurrent users read and write data， the read operations can be a statement of any retrieved data， the default use of shared lock. The write operations can be a statement to update data， need to use the exclusive lock. For the lock and lock time， the processing mode of read can be controlled， although the processing mode of write can't be controlled. Of course， as a result of controlling the action of the read operation， the action of the write operation is also implicitly affected. So， the isolation level can be set at the session level with session options.

Key words： concurrency； isolation level； exclusive lock； shared locks

0 引言

并發控制是事務處理的一部分，就像交通信號燈一樣，確保在多用戶訪問共享的數據庫時，不會“碰撞”到其他用戶。每個時刻只允許同一用戶來訪問數據，是避免所有問題的方法之一，但這個方案的最大問題是會延長其他用戶的相應時間[2，5]。

根據SQL Server自身情況來定義事務的隔離級別，將會更好的滿足需求，減少死鎖和阻塞，提升性能和響應[4]。

1 基本概念

SQL Server可以設置的隔離級別有6個：read uncommitted（未提交讀）、read committed（已提交讀＼默認值）、repeatable read（可重復讀）、serializable（可序列化）、snapshot（快照）和read committed snapshot（已經提交度隔離）。 最后兩個級別是在SQL Server2005中引入的，可以把事務已經提交的上一個版本保存在tempdb數據庫中，以這種行版本控制為基礎。snapshot隔離級別在邏輯上和serializable隔離級別類似，read committed snapshot隔離級別和read committed隔離級別類似，它們能解決和不能解決的不一致問題是一樣的。但是在基于快照的隔離級別下，讀操作不需要使用共享鎖，所以即使請求的數據被其他事務以排他鎖鎖定，讀操作也不會等待。但是，無論使用哪種基于快照的隔離級別，都會對數據更新和刪除操作的性能產生負面影響。因此，本文不對后兩種隔離級別作深入探討。設置整個會話的隔離級別時，可以使用以下命令：

set transaction isolation level

2 READ UNCOMMITTED未提交讀

read uncommitted是最低的隔離級別。在這個隔離級別運行的事務，讀操作不會請求共享鎖。如果讀操作不請求共享鎖，就不會和持有排他鎖的寫操作發生沖突。這意味著讀操作可以讀取未提交的修改（也稱為臟讀）；同時也意味著讀操作不會妨礙寫操作請求排他鎖。也就是說，當運行在read uncommitted隔離級別下的讀操作正在讀取數據時，寫操作可以同時對這些數據進行修改。下面演示什么是未提交讀（臟讀，dirty read），打開兩個查詢窗口connection1和connection2：在connection1，運行以下代碼，打開一個事務，更新學號為'201401010101'，課程號為'B1103011'的成績（62分），增加8分（70分），然后查詢該成績。

begin tran

update sc set score=score+8 where snum=

'201401010101' and cnum='B1103011'

select * from sc where snum='201401010101'

and cnum='B1103011'

在以上代碼中，事務保持打開，這意味著這一行被排他鎖鎖定。connection1中的代碼會返回以下輸出結果：

snum cnum score

----------- ------ -----

201401010101 B1103011 70

在connetion2中，運行以下代碼，將會話的隔離級別設置為read uncommitted。

set transaction isolation level read uncommitted

select * from sc where snum='201401010101'

and cnum='B1103011'

因為這個讀操作不用申請共享鎖，所以它不會和其他事務發生沖突。該查詢返回所在行修改后的狀態，即使這一修改還沒有被提交。

snum cnum score

----------- ------ -----

201401010101 B1103011 70

再在connection1中運行以下代碼回滾事務：

rollback tran

撤銷對成績的更新，臟讀產生了。

3 READ COMMITTED已提交讀

能夠防止臟讀的最低隔離級別是read committed， 這也是SQL Server默認使用的隔離級別。這個隔離級別只允許讀取已經提交過的修改。它要求讀操作必須獲得共享鎖才能操作，從而防止讀取未提交過的修改。這意味著，如果寫操作持有排他鎖，讀操作提出的共享鎖請求就會和寫操作發生沖突，所以讀操作不得不等待。一旦寫操作提交了事務，讀操作才能獲得它請求的共享鎖，而這時讀到的只能是修改提交過的數據。

下面演示在read committed隔離級別下，讀操作只能讀取修改提交過的數據。

在connection1中運行以下代碼，以排他鎖鎖定了數據行。

begin tran

update sc set score=score+8 where snum=

'201401010101' and cnum='B1103011'

select * from sc where snum='201401010101'

and cnum='B1103011'

這段代碼將返回以下輸出結果：

snum cnum score

----------- ------ -----

201401010101 B1103011 70

在connection2中運行以下代碼，將會話的隔離級別設置為read committed，再查詢：

set transaction isolation level read committed

select * from sc where snum='201401010101'

and cnum='B1103011'

read committed是SQL Server默認的隔離級別，不需要顯示地設置該隔離級別。執行該查詢時，SELECT語句會被阻塞，因為這時它需要獲得共享鎖才能進行讀操作，而該共享鎖請求與connection1中寫操作的排他鎖相沖突。接下來，在connection1中運行以下代碼，即提交代碼。

commit tran

按照鎖的持有時間來說，在read committed隔離級別中，讀操作一完成，就立即釋放資源上的共享鎖。若其他事務在兩個讀操作之間更改數據資源，會產生不可重復讀。

清理數據，將成績改回62。

4 REPEATABLE READ

如果想保證在事務內部進行的兩個讀操作之間，其他任何事務都不能修改由當前事務讀取的數據，則需要把隔離級別升級為read repeatable。在這種隔離級別下，事務中的讀操作不但需要獲得共享鎖才能讀數據，而且該鎖會一直保持到事務完成為止。這樣就可以保證實現可重復的讀取。

在connection1中運行以下代碼，將會話級別設為repeatable read。

set transaction isolation level repeatable read

begin tran

select * from sc where snum='201401010101'

and cnum='B1103011'

這段代碼將返回以下輸出結果。

snum cnum score

----------- ------ -----

201401010101 B1103011 70

connection1這時仍然持有共享鎖，在connection2中運行以下代碼，嘗試對這一行進行修改。

update sc set score=score+8 where snum=

'201401010101' and cnum='B1103011'

SQL Server會阻塞這一修改，因為修改操作請求的排他鎖與前面讀操作授予的共享鎖沖突。

再回到connection1，運行以下代碼，再次查詢，并提交事務。

select * from sc where snum='201401010101'

and cnum='B1103011'

commit tran

前后兩次的讀取結果相同。現在讀操作的事務已經提交了，共享鎖也釋放了，所以connection2中的修改操作就能獲得它正等待的排他鎖，進行更新。

清理數據，將成績改回62。

repeatable read隔離級別能夠防止的另一種并發負面影響是丟失更新，而較低的隔離級別不能防止這種問題。在repeatable read 隔離級別下，一個事務在第一次讀數據操作之后都保留它們獲得的共享鎖，所以任何一個事務都不能為了更新數據而需要排他鎖。這種情況最終會導致死鎖，不過避免了更新沖突。

5 SERIALIZABLE可序列化

在repeatable read隔離操作級別下運行的事務，讀操作獲得的共享鎖一直保持到事務完成為止。但是，事務只鎖定查詢第一次運行時找到的那些數據資源（例如，行），而不會鎖定查詢結果范圍以外的其他行。因此，在同一事務中進行第二次讀取之前，如果其他事務插入了新行，而且新行也能滿足讀操作的查詢過濾條件，那么這些新行也會出現在第二次讀操作返回的結果中。這些新行稱為幻影，這種讀操作也叫做幻讀。

為了避免幻讀，需要將隔離級別設置為更高級的serializable。大多數時候，serializable隔離級別的處理方式和repeatable read類似，不過，serializable隔離級別增加了一個新內容——邏輯上，這個隔離級別會讓讀操作鎖定滿足查詢搜索條件的鍵的整個范圍。這就意味著讀操作不僅鎖定了滿足查詢條件的現有的那些行，還鎖定了未來可能滿足查詢條件的行。更準確地說，如果其他事務試圖增加能夠滿足讀操作的查詢條件的新行，當前事務就會阻塞這樣的操作。

以下例子演示如何用serializable隔離級別來避免幻讀。

connection1：

set transaction isolation level serializable

begin tran

select * from sc where snum='201401010101'

可以得到21行輸出結果。

在connection2中運行以下代碼，嘗試選一門新課。

insert into sc values（'201401010101'，'C01'，null）

在所有低于serializable隔離級別下，這樣的插入操作將會成功。而在serializable隔離級別下，這樣的操作將被阻塞。

再回到connection1，運行以下代碼，并提交事務

*/select * from sc where snum='201401010101'

commit tran

得到的輸出結果和前面的一樣，沒有幻影行。現在讀操作事務已經提交了，共享鎖的范圍也隨之釋放，所以，connection2中的修改操作就獲得了等候已久的排他鎖，插入新行。

6 結束語

以下總結了每種隔離級別能否解決的各種一致性問題：

[隔離級別＼&臟讀＼&不可重復讀＼&丟失更新＼&幻讀＼&Read uncommitted＼&是＼&是＼&是＼&是＼&Read committed＼&否＼&是＼&是＼&是＼&Repeatable read＼&否＼&否＼&否＼&是＼&serializable＼&否＼&否＼&否＼&否＼&]

較低的隔離級別可以增強許多用戶同時訪問數據的能力，但也增加了用戶可能遇到的并發副作用的數量。相反，較高的隔離級別減少了用戶可能遇到的并發副作用的類型，但需要更多的系統資源，并增加了一個事務阻塞其他事務的可能性。

應該平衡應用程序的數據完整性要求與每個隔離級別的開銷，在此基礎上選擇相應的隔離級別。最高隔離級別（可序列化）保證事務在每次重復讀取操作時都能準確檢索到相同的數據，但需要通過執行某種級別的鎖定來完成此操作，而鎖定可能會影響多用戶系統中的其他用戶。最低隔離級別（未提交讀）可以檢索其他事務已經修改但未提交的數據。在未提交讀中，所有并發副作用都可能發生，但因為沒有讀取鎖定或版本控制，所以開銷最少[4]。

參考文獻（References）：

[1] 吳達勝，劉麗娟.《數據庫原理與技術》的理論與實踐教學的

整體優化研究[J].計算機時代，2005.11：31-32

[2] 王珊，薩師煊.數據庫系統概論（第5版）[M].高等教育出版社，

2014.

[3] 劉麗娟，吳達勝.關于MySQL數據庫中觸發器的學習探討[J].

計算機時代，2014，4：66-68.

[4] Itzik Ben-Gan. Microsoft SQL Server 2008技術內幕[M].電

子工業出版社，2009.

[5] Joe Celko.SQ權威指南（第4版）[M].人民郵電出版社，

2013.