SQLServer事務的隔離級别

事務的隔離級别

sql server通過在鎖資源上使用不同類型的鎖來隔離事務。為了開發安全的事務，定義事務内容以及應在何種情況下復原至關重要，定義如何以及在多長時間内在事務中保持鎖定也同等重要。這由隔離級别決定。應用不同的隔離級别，sql server賦予開發者一種能力，讓他們為每一個單獨事務定義與其他事務的隔離程度。事務隔離級别的定義如下：

是否在讀資料的時候使用鎖

讀鎖持續多長時間

在讀資料的時候使用何種類型的鎖

讀操作希望讀已經被其他事務排他鎖住的資料時，怎麼辦？在這種情況下，sql server可以：

一直等到其他事務釋放鎖

讀沒有送出的資料

讀資料最後送出後的版本

ansi 99定義了4種事務隔離級别，sql server 2005能夠完全支援這些級别：

未送出讀 在讀資料時不會檢查或使用任何鎖。是以，在這種隔離級别中可能讀取到沒有送出的資料。

已送出讀 隻讀取送出的資料并等待其他事務釋放排他鎖。讀資料的共享鎖在讀操作完成後立即釋放。已送出讀是sql server的預設隔離級别。

可重複讀 像已送出讀級别那樣讀資料，但會保持共享鎖直到事務結束。

可序列化 工作方式類似于可重複讀。但它不僅會鎖定受影響的資料，還會鎖定這個範圍。這就阻止了新資料插入查詢所涉及的範圍，這種情況可以導緻幻像讀。

此外，sql server還有兩種使用行版本控制來讀取資料的事務級别(本章後文将詳細檢驗這些隔離級别)。行版本控制允許一個事務在資料排他鎖定後讀取資料的最後送出版本。由于不必等待到鎖釋放就可進行讀操作，是以查詢性能得以大大增強。這兩種隔離級别如下：

已送出讀快照 它是一種送出讀級别的新實作。不像一般的送出讀級别，sql server會讀取最後送出的版本并是以不必在進行讀操作時等待直到鎖被釋放。這個級别可以替代送出讀級别。

快照 這種隔離使用行版本來提供事務級别的讀取一緻性。這意味着在一個事務中，由于讀一緻性可以通過行版本控制實作，是以同樣的資料總是可以像在可序列化級别上一樣被讀取而不必為防止來自其他事務的更改而被鎖定。

無論定義什麼隔離級别，對資料的更改總是通過排他鎖來鎖定并直到事務結束時才釋放。

很多情況下，定義正确的隔離級别并不是一個簡單的決定。作為一種通用的規則，要選擇在盡可能短的時間内鎖住最少資料，但同時依然可以為事務提供它所需的安全程度的隔離級别。

已送出讀

在sql server 2005中，

已送出讀隔離級别是建立連接配接時的預設隔離級别。這個級别存在兩種類型：已送出讀和已送出讀快照隔離級别。應用哪種類型由資料庫選項定義。已送出讀級别會在

讀資料之前等待，直到阻塞鎖被釋放。已送出讀快照級别會在資料被其他事務阻塞時使用行版本控制來讀資料最後一次送出的版本。

使用已送出讀級别：

begin tran

select

    firstname, lastname, emailaddress

from

    person.contact

where

contactid = 1

    傳回emailaddress為[email][email protected][/email]的聯系人gustavo achong。

現在假設另一事務在事務打開狀态下更改了emailaddress。打開第二個查詢視窗并執行以下批來update emailaddress，但不送出事務：

use adventureworks;

update

set

    emailaddress = '[email][email protected][/email]'

    contactid = 1

    這個update 語句會正常運作。一行受到了影響，即使資料在這個事務還沒有運作完之前已被查詢視窗1中的事務讀取。因為已送出讀級别并不會在事務結束前保持用于select語句的共享鎖。共享鎖會在資料讀取之後立即被sql server釋放。需要一緻讀的時候這将是一個問題。我們将下面的"擷取一緻的可重複讀操作"實作。

    現在切換到查詢視窗1并嘗試再次讀資料：

    select

        firstname, lastname, emailaddress

        person.contact

        contactid = 1

    由于select語句被阻塞，是以這個查詢并沒有結束。sql server會嘗試在contactid= 1的鍵上擷取一個共享鎖，但是由于在查詢視窗2中的update語句對其有一個排他鎖，是以這個操作不可能完成。雖然查詢視窗2處于已送出讀級别(由于您沒有更改預設級别)，但排他鎖依然存在。這個阻塞将持續存在，因為資料更改的排他鎖會一直保持直到事務結束。

切換到查詢視窗2，讓查詢視窗1中的查詢繼續運作。鍵入并執行以下select語句檢查資料庫中的授權和等待的鎖。

可以看一個狀态為wait的共享鎖。這是查詢視窗1中運作的查詢。它在等待查詢視窗2中的查詢，後者在同樣的資源上有一個排他鎖。

在查詢視窗2中執行一個rollback tran語句來復原update語句。然後切換回查詢視窗1。可以看到，查詢視窗1中的查詢完成了，并且其結果與以前的一樣。查詢視窗2中的事務結束的時候，鎖被釋放了，以至查詢視窗1中的查詢不再被阻塞。由于查詢視窗2中的事務復原，是以查詢視窗1中得到的結果是原來的資料。如果查詢視窗2中的事務被送出，則查詢視窗1中會得到新的資料作為結果。

在查詢視窗1中執行一個commit tran語句并關閉所有的查詢視窗。

可以看出，在(預設)已送出讀級别中sql server會

等到排他鎖釋放之後再進行讀操作，以此來擷取真正的送出資料。還可以看出，共享鎖會持續到資料被讀取之後，而排他鎖會持續到事務送出之後。在許多事務幾乎

同時更改資料的時候這種行為可能會造成問題。在這些情況下，由于排他鎖造成的阻塞，讀資料會非常慢。但在有些情況下，使用最後送出的資料版本是恰當的。在

這些情況下，可以将已送出讀級别更改為已送出讀快照級别。

如果要在視窗1讀取資料的話，可以使用這樣的方法：

    person.contact with (nolock)

    讓它取消所有的鎖機制，那麼排他鎖也不會影響到這句查詢。

    使用nolock注意：在 sql server 中，nolock 提示将啟用"未送出讀"行為。在 sql server mobile 中，使用 nolock 提示仍會賦予"送出讀"隔離級别。sql server mobile 将維護資料副本，以確定可以讀取資料而不需要使用共享鎖幫助保護資料。

使用已送出讀快照級别

激活已送出讀快照級别

use master;

alter database adventureworks

set read_committed_snapshot on

    注意：設定 read_committed_snapshot 選項時，資料庫中僅允許存在執行 alter database 指令的連接配接。在 alter database 完成之前，資料庫中不允許有其他打開的連接配接。資料庫不必處于單使用者模式。

現在，執行以下代碼開始一個事務并像前面一樣更改emailaddress(但要讓事務處于打開狀态)：

update person.contact

set emailaddress = '[email][email protected][/email]'

where contactid = 1;

打開第二個查詢視窗并執行以下語句來讀取contactid 1的列name和emailaddress列。

    use adventureworks;

select firstname, lastname, emailaddress

from person.contact

傳回了聯系人gustavo achong的emailaddress [email][email protected][/email]，這是這一行最後送出的版本。不像沒有快照的已送出讀級别那樣，這個查詢不會被阻塞。關閉查詢視窗2并切換到查詢視窗1。

執行以下語句來復原事務并切換回已送出讀級别(這個查詢将等待直到關閉查詢視窗2)：

rollback tran

go

set read_committed_snapshot off

重要提示 這個隔離級别可以用于減少阻塞。但要意識到這是一個資料庫選項。當它發生了更改，将在資料庫系統中使用已送出讀級别的所有事務也會改變它們的行為。是以，隻有在所有這些事務讀最後送出的資料版本與讀真正送出的資料版本在邏輯上同樣正确的時候，使用這種級别才是明智的。

擷取一緻的可重複讀操作

已送出讀級别的一個缺點是，一個事務讀取的資料在事務運作期間可能被另一個事務更改。是以，在兩種已送出讀級别下，不能保證一緻性讀。擷取一緻性讀的意思是，在一個事務中，讀取的資料始終是一樣的。

1.  已送出讀在讀資料的時候使用共享鎖，但在讀操作完成後會立即釋放這個鎖。是以，其他事務可以更改剛被讀過的資料。

2.  已送出讀快照讀取最後一次送出的資料版本。當它第二次讀資料的時候，最後一次送出的版本可能由于第二個事務已經送出了對資料的更改而變成一個新版本。

在需要一緻性讀的時候(例如對于報表)，

可能這種不一緻性會導緻問題。想象一下，您的事務通過資料計算了一些商業數值。在已送出讀級别中進行這種計算的時候，可能由于基礎資料在事務計算過程中發

生了變化而導緻這些值被錯誤計算。為了成功地執行這個計算，可以使用快照隔離級别。它會使用行版本管理來提供資料的送出版本，但與已送出讀快照不同的是，

它總會提供在開始事務時最後送出的資料版本。是以，sql server始終會在整個事務執行過程中擷取同樣的資料。

使用快照隔離級别

    快照隔離級别需要在資料庫中一次性地激活。激活之後，每個連接配接可以在需要的時候使用它。

    use master;

set allow_snapshot_isolation on;

    現在假設我們希望運作一些基于sales.salesorderdetail表的報表，但需要一緻性的讀操作。執行以下語句為事務激活快照隔離級别并開始一個傳回訂單行合計的事務。記住ordertotal的值。

set transaction isolation level snapshot

select sum(linetotal) as ordertotal

from sales.salesorderdetail

where salesorderid = 43659

參數snapshot的含義：

1.      指

定事務中任何語句讀取的資料都将是在事務開始時便存在的資料的事務上一緻的版本。事務隻能識别在其開始之前送出的資料修改。在目前事務中執行的語句将看不

到在目前事務開始以後由其他事務所做的資料修改。其效果就好像事務中的語句獲得了已送出資料的快照，因為該資料在事務開始時就存在。

2.      除非正在恢複資料庫，否則 snapshot 事務不會在讀取資料時請求鎖。讀取資料的 snapshot 事務不會阻止其他事務寫入資料。寫入資料的事務也不會阻止 snapshot 事務讀取資料。

3.      在資料庫恢複的復原階段，如果嘗試讀取由其他正在復原的事務鎖定的資料，則 snapshot 事務将請求一個鎖。在事務完成復原之前，snapshot 事務會一直被阻塞。當事務取得授權之後，便會立即釋放鎖。

4.      必須将 allow_snapshot_isolation 資料庫選項設定為 on，才能開始一個使用 snapshot 隔離級别的事務。如果使用 snapshot 隔離級别的事務通路多個資料庫中的資料，則必須在每個資料庫中将 allow_snapshot_isolation 都設定為 on。

5.      不能将通過其他隔離級别開始的事務設定為 snapshot 隔離級别，否則将導緻事務中止。如果一個事務在 snapshot 隔離級别開始，則可以将它更改為另一個隔離級别，然後再傳回 snapshot。一個事務從執行 begin transaction 語句開始。

6.      在 snapshot 隔離級别下運作的事務可以檢視由該事務所做的更改。例如，如果事務對表執行 update，然後對同一個表發出 select 語句，則修改後的資料将包含在結果集中。

    打開第二個查詢視窗并更新salesorderdetail表以更改查詢視窗1中用到的基礎資料。(如果希望重複這個示例，将orderqty的值5更改為其他數字以使以下代碼能真正地更改資料庫中的資料)：

update sales.salesorderdetail

set orderqty = 5

and productid = 777

    關閉查詢視窗2，切換到查詢視窗1，然後重複下面的select語句。

    select sum(linetotal) as ordertotal

    可以看出，由于快照隔離級别忽略了事務運作過程中資料的更改，是以結果與以前的相同。在快照級别下總會提供在事務開始時最後送出的值。

送出這個事務并執行以下代碼再次重複這個查詢：現在可看到，由于事務結束了，是以結果發生了變化。

commit tran

    執行以下代碼關閉adventureworks資料庫的快照隔離級别：

    alter database adventureworks

set allow_snapshot_isolation off;

避免同時發生的資料更新

如

前所述，快照隔離級别并不在讀操作的時候鎖定資料，但能夠在整個事務中提供一緻性的視圖。在某些情況下，有必要在整個事務的執行過程中鎖定資料以避免其他

事務對資料的更改。假設希望為一個訂單×××。首先需要擷取資料并檢查它，然後為其生成發票。在這種情況下，需要從事務起始就鎖定資料以避免其他事務更改

它。在這種情況下，快照隔離或者已送出讀隔離級别都不是好的選擇。對于這種情況，可以使用可重複讀隔離級别。這個隔離級别與沒有快照的已送出讀級别的工作

過程相似，但它會保持共享鎖直至事務結束。是以，它防止了對資料的更新。

使用可重複讀隔離級别

    假設希望處理orderid為43659的訂單。首先，必須選擇資料。為了防止其他事務更改正在讀的資料，使用可重複讀隔離。

set transaction isolation level repeatable read

select salesorderid, salesorderdetailid, productid, orderqty

    參數repeatable read的含義：

1.  指定語句不能讀取已由其他事務修改但尚未送出的行，并且指定，其他任何事務都不能在目前事務完成之前修改由目前事務讀取的資料。

2.  對

事務中的每個語句所讀取的全部資料都設定了共享鎖，并且該共享鎖一直保持到事務完成為止。這樣可以防止其他事務修改目前事務讀取的任何行。其他事務可以插

入與目前事務所發出語句的搜尋條件相比對的新行。如果目前事務随後重試執行該語句，它會檢索新行，進而産生幻讀。由于共享鎖一直保持到事務結束，而不是在

每個語句結束時釋放，是以并發級别低于預設的 read committed 隔離級别。此選項隻在必要時使用。

打開第二個查詢視窗并執行以下代碼嘗試更新salesorderdetail表以更改查詢視窗1中要使用的基礎資料：

    update sales.salesorderdetail

    查詢會等待。不像快照隔離級别，不可能更新資料，因為共享鎖會保持以防止其他事務更改資料。這個鎖可以通過前面用過的管理視圖sys.dm_tran_locks檢視。

    單擊工具條上的"取消執行查詢"按鈕取消在查詢視窗2中的查詢。而執行以下insert語句在訂單中加入一個新行項。

    insert into sales.salesorderdetail

(

    salesorderid,

    carriertrackingnumber,

    orderqty,

    productid,

    specialofferid,

    unitprice,

    unitpricediscount

)

values(43659,'4911-403c-98',1,758,1,874,0)

    注意，即使正處于可重複讀隔離級别，這個語句也會成功執行。因為可重複讀會鎖定資料以阻止資料的更新，但insert語句向資料庫中插入新資料，這是允許的。新行處于查詢視窗1中事務select語句的查詢範圍之中，是以會在事務下一次擷取相同資料的時候被讀取到。這稱作幻像讀。

    重複select語句并送出這個事務，如下所示：

    select salesorderid, salesorderdetailid, productid, orderqty

    可以觀察到，新行被select語句讀取了，因為它處于這個語句的查詢範圍之内。可重複讀級别會阻止現有資料被更改，但不會阻止新資料插入select語句的查詢範圍内。

其他

    set transaction一共有以下幾種級别：

    set transaction isolation level

{ read uncommitted

| read committed

| repeatable read

| snapshot

| serializable

}

[ ; ]

    上面的例子中沒有提到的幾種隔離級别的說明：

read uncommitted

指定語句可以讀取已由其他事務修改但尚未送出的行。

在 read uncommitted 級别運作的事務，不會發出共享鎖來防止其他事務修改目前事務讀取的資料。read uncommitted 事務也不會被排他鎖阻塞，排他鎖會禁止目前事務讀取其他事務已修改但尚未送出的行。設定此選項之後，可以讀取未送出的修改，這種讀取稱為髒讀。在事務結束之前，可以更改資料中的值，行也可以出現在資料集中或從資料集中消失。該選項的作用與在事務内所有 select 語句中的所有表上設定 nolock 相同。這是隔離級别中限制最少的級别。

在 sql server 2005 中，您還可以使用下列任意一種方法，在保護事務不髒讀未送出的資料修改的同時盡量減少鎖定争用：

1.  read committed 隔離級别，并将 read_committed_snapshot 資料庫選項設定為 on。

2.  snapshot 隔離級别。

read committed

指定語句不能讀取已由其他事務修改但尚未送出的資料。這樣可以避免髒讀。其他事務可以在目前事務的各個語句之間更改資料，進而産生不可重複讀取和幻像資料。該選項是 sql server 的預設設定。

read committed 的行為取決于 read_committed_snapshot 資料庫選項的設定：

1.  如果将 read_committed_snapshot 設定為 off（預設設定），則資料庫引擎 會使用共享鎖防止其他事務在目前事務執行讀取操作期間修改行。共享鎖還會阻止語句在其他事務完成之前讀取由這些事務修改的行。語句完成後便會釋放共享鎖。

2.  如果将 read_committed_snapshot 設定為 on，則資料庫引擎 會使用行版本控制為每個語句提供一個在事務上一緻的資料快照，因為該資料在語句開始時就存在。不使用鎖來防止其他事務更新資料。

當 read_committed_snapshot 資料庫選項設定為 on 時，您可以使用 readcommittedlock 表提示為 read_committed 隔離級别上運作的事務中的各語句請求共享鎖，而不是行版本控制。

serializable

請指定下列内容：

1.  語句不能讀取已由其他事務修改但尚未送出的資料。

2.  任何其他事務都不能在目前事務完成之前修改由目前事務讀取的資料。

3.  在目前事務完成之前，其他事務不能使用目前事務中任何語句讀取的鍵值插入新行。

範

圍鎖處于與事務中執行的每個語句的搜尋條件相比對的鍵值範圍之内。這樣可以阻止其他事務更新或插入任何行，進而限定目前事務所執行的任何語句。這意味着如

果再次執行事務中的任何語句，則這些語句便會讀取同一組行。在事務完成之前将一直保持範圍鎖。這是限制最多的隔離級别，因為它鎖定了鍵的整個範圍，并在事

務完成之前一直保持範圍鎖。因為并發級别較低，是以應隻在必要時才使用該選項。該選項的作用與在事務内所有 select 語句中的所有表上設定 holdlock 相同。

需要注意的地方：

1.  一次隻能設定一個隔離級别選項，而且設定的選項将一直對那個連接配接始終有效，直到顯式更改該選項為止。事務中執行的所有讀取操作都會在指定的隔離級别的規則下運作，除非語句的 from 子句中的表提示為表指定了其他鎖定行為或版本控制行為。

2.  事務隔離級别定義了可為讀取操作擷取的鎖類型。針對 read committed 或 repeatable read 擷取的共享鎖通常為行鎖，盡管當讀取引用了頁或表中大量的行時，行鎖可以更新為頁鎖或表鎖。如果某行在被讀取之後由事務進行了修改，則該事務會擷取一個用于保護該行的排他鎖，并且該排他鎖在事務完成之前将一直保持。例如，如果 repeatable read 事務具有用于某行的共享鎖，并且該事務随後修改了該行，則共享行鎖便會轉換為排他行鎖。

3.  在事務進行期間，可以随時将事務從一個隔離級别切換到另一個隔離級别，但有一種情況例外。即在從任一隔離級别更改到 snapshot 隔離時，不能進行上述操作。否則會導緻事務失敗并復原。但是，可以将在 snapshot 隔離中啟動的事務更改為任何其他隔離級别。

4.  将事務從一個隔離級别更改為另一個隔離級别之後，便會根據新級别的規則對更改後讀取的資源執行保護。在更改前讀取的資源将繼續按照以前級别的規則受到保護。例如，如果某個事務從 read committed 更改為 serializable，則在該事務結束前，更改後所擷取的共享鎖将一直處于保留狀态。

5.  如果在存儲過程或觸發器中發出 set transaction isolation level，則當對象傳回控制時，隔離級别會重設為在調用對象時有效的級别。例如，如果在批進行中設定 repeatable read，并且該批處理調用一個将隔離級别設定為 serializable 的存儲過程，則當該存儲過程将控制傳回給該批處理時，隔離級别就會恢複為 repeatable read。