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

關(guān)鍵詞： 并發(fā)； 隔離級別； 排他鎖； 共享鎖

中圖分類號：TP311 文獻(xiàn)標(biāo)志碼：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 引言

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

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

1 基本概念

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

set transaction isolation level

2 READ UNCOMMITTED未提交讀

read uncommitted是最低的隔離級別。在這個隔離級別運行的事務(wù)，讀操作不會請求共享鎖。如果讀操作不請求共享鎖，就不會和持有排他鎖的寫操作發(fā)生沖突。這意味著讀操作可以讀取未提交的修改（也稱為臟讀）；同時也意味著讀操作不會妨礙寫操作請求排他鎖。也就是說，當(dāng)運行在read uncommitted隔離級別下的讀操作正在讀取數(shù)據(jù)時，寫操作可以同時對這些數(shù)據(jù)進(jìn)行修改。下面演示什么是未提交讀（臟讀，dirty read），打開兩個查詢窗口connection1和connection2：在connection1，運行以下代碼，打開一個事務(wù)，更新學(xué)號為'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'

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

snum cnum score

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

201401010101 B1103011 70

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

set transaction isolation level read uncommitted

select * from sc where snum='201401010101'

and cnum='B1103011'

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

snum cnum score

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

201401010101 B1103011 70

再在connection1中運行以下代碼回滾事務(wù)：

rollback tran

撤銷對成績的更新，臟讀產(chǎn)生了。

3 READ COMMITTED已提交讀

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

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

在connection1中運行以下代碼，以排他鎖鎖定了數(shù)據(jù)行。

begin tran

update sc set score=score+8 where snum=

'201401010101' and cnum='B1103011'

select * from sc where snum='201401010101'

and cnum='B1103011'

這段代碼將返回以下輸出結(jié)果：

snum cnum score

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

201401010101 B1103011 70

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

set transaction isolation level read committed

select * from sc where snum='201401010101'

and cnum='B1103011'

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

commit tran

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

清理數(shù)據(jù)，將成績改回62。

4 REPEATABLE READ

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

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

set transaction isolation level repeatable read

begin tran

select * from sc where snum='201401010101'

and cnum='B1103011'

這段代碼將返回以下輸出結(jié)果。

snum cnum score

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

201401010101 B1103011 70

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

update sc set score=score+8 where snum=

'201401010101' and cnum='B1103011'

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

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

select * from sc where snum='201401010101'

and cnum='B1103011'

commit tran

前后兩次的讀取結(jié)果相同。現(xiàn)在讀操作的事務(wù)已經(jīng)提交了，共享鎖也釋放了，所以connection2中的修改操作就能獲得它正等待的排他鎖，進(jìn)行更新。

清理數(shù)據(jù)，將成績改回62。

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

5 SERIALIZABLE可序列化

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

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

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

connection1：

set transaction isolation level serializable

begin tran

select * from sc where snum='201401010101'

可以得到21行輸出結(jié)果。

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

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

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

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

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

commit tran

得到的輸出結(jié)果和前面的一樣，沒有幻影行?，F(xiàn)在讀操作事務(wù)已經(jīng)提交了，共享鎖的范圍也隨之釋放，所以，connection2中的修改操作就獲得了等候已久的排他鎖，插入新行。

6 結(jié)束語

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

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

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

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

參考文獻(xiàn)（References）：

[1] 吳達(dá)勝，劉麗娟.《數(shù)據(jù)庫原理與技術(shù)》的理論與實踐教學(xué)的

整體優(yōu)化研究[J].計算機(jī)時代，2005.11：31-32

[2] 王珊，薩師煊.數(shù)據(jù)庫系統(tǒng)概論（第5版）[M].高等教育出版社，

2014.

[3] 劉麗娟，吳達(dá)勝.關(guān)于MySQL數(shù)據(jù)庫中觸發(fā)器的學(xué)習(xí)探討[J].

計算機(jī)時代，2014，4：66-68.

[4] Itzik Ben-Gan. Microsoft SQL Server 2008技術(shù)內(nèi)幕[M].電

子工業(yè)出版社，2009.

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

2013.