關(guān)于SQL Server事務(wù)隔離級(jí)別的探討

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

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

中圖分類號(hào)：TP311 文獻(xiàn)標(biāo)志碼：A 文章編號(hào)：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ù)處理的一部分，就像交通信號(hào)燈一樣，確保在多用戶訪問(wèn)共享的數(shù)據(jù)庫(kù)時(shí)，不會(huì)“碰撞”到其他用戶。每個(gè)時(shí)刻只允許同一用戶來(lái)訪問(wèn)數(shù)據(jù)，是避免所有問(wèn)題的方法之一，但這個(gè)方案的最大問(wèn)題是會(huì)延長(zhǎng)其他用戶的相應(yīng)時(shí)間[2，5]。

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

1 基本概念

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

set transaction isolation level

2 READ UNCOMMITTED未提交讀

read uncommitted是最低的隔離級(jí)別。在這個(gè)隔離級(jí)別運(yùn)行的事務(wù)，讀操作不會(huì)請(qǐng)求共享鎖。如果讀操作不請(qǐng)求共享鎖，就不會(huì)和持有排他鎖的寫操作發(fā)生沖突。這意味著讀操作可以讀取未提交的修改（也稱為臟讀）；同時(shí)也意味著讀操作不會(huì)妨礙寫操作請(qǐng)求排他鎖。也就是說(shuō)，當(dāng)運(yùn)行在read uncommitted隔離級(jí)別下的讀操作正在讀取數(shù)據(jù)時(shí)，寫操作可以同時(shí)對(duì)這些數(shù)據(jù)進(jìn)行修改。下面演示什么是未提交讀（臟讀，dirty read），打開兩個(gè)查詢窗口connection1和connection2：在connection1，運(yùn)行以下代碼，打開一個(gè)事務(wù)，更新學(xué)號(hào)為'201401010101'，課程號(hào)為'B1103011'的成績(jī)（62分），增加8分（70分），然后查詢?cè)摮煽?jī)。

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中的代碼會(huì)返回以下輸出結(jié)果：

snum cnum score

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

201401010101 B1103011 70

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

set transaction isolation level read uncommitted

select * from sc where snum='201401010101'

and cnum='B1103011'

因?yàn)檫@個(gè)讀操作不用申請(qǐng)共享鎖，所以它不會(huì)和其他事務(wù)發(fā)生沖突。該查詢返回所在行修改后的狀態(tài)，即使這一修改還沒(méi)有被提交。

snum cnum score

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

201401010101 B1103011 70

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

rollback tran

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

3 READ COMMITTED已提交讀

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

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

在connection1中運(yùn)行以下代碼，以排他鎖鎖定了數(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中運(yùn)行以下代碼，將會(huì)話的隔離級(jí)別設(shè)置為read committed，再查詢：

set transaction isolation level read committed

select * from sc where snum='201401010101'

and cnum='B1103011'

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

commit tran

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

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

4 REPEATABLE READ

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

在connection1中運(yùn)行以下代碼，將會(huì)話級(jí)別設(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這時(shí)仍然持有共享鎖，在connection2中運(yùn)行以下代碼，嘗試對(duì)這一行進(jìn)行修改。

update sc set score=score+8 where snum=

'201401010101' and cnum='B1103011'

SQL Server會(huì)阻塞這一修改，因?yàn)樾薷牟僮髡?qǐng)求的排他鎖與前面讀操作授予的共享鎖沖突。

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

select * from sc where snum='201401010101'

and cnum='B1103011'

commit tran

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

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

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

5 SERIALIZABLE可序列化

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

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

以下例子演示如何用serializable隔離級(jí)別來(lái)避免幻讀。

connection1：

set transaction isolation level serializable

begin tran

select * from sc where snum='201401010101'

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

在connection2中運(yùn)行以下代碼，嘗試選一門新課。

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

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

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

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

commit tran

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

6 結(jié)束語(yǔ)

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

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

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

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

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

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

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

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

2014.

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

計(jì)算機(jī)時(shí)代，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.