SQL Server事務的隔離級別

iSQlServer發表於2010-02-24
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為gustavo0@adventure-works.com的聯絡人Gustavo Achong。
現在假設另一事務在事務開啟狀態下更改了EmailAddress。開啟第二個查詢視窗並執行以下批來UPDATE EmailAddress，但不提交事務：
USE AdventureWorks;
 
BEGIN TRAN
UPDATE
    Person.Contact
SET
    EmailAddress = 'uncommitted@email.at'
WHERE
    ContactID = 1
    這個UPDATE 語句會正常執行。一行受到了影響，即使資料在這個事務還沒有執行完之前已被查詢視窗1中的事務讀取。因為已提交讀級別並不會在事務結束前保持用於SELECT語句的共享鎖。共享鎖會在資料讀取之後立即被SQL Server釋放。需要一致讀的時候這將是一個問題。我們將下面的"獲取一致的可重複讀操作"實現。
    現在切換到查詢視窗1並嘗試再次讀資料：
    SELECT
        FirstName, LastName, EmailAddress
FROM
        Person.Contact
WHERE
        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讀取資料的話，可以使用這樣的方法：
SELECT
    FirstName, LastName, EmailAddress
FROM
    Person.Contact WITH (NOLOCK)
WHERE
    ContactID = 1
    讓它取消所有的鎖機制，那麼排他鎖也不會影響到這句查詢。
    使用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(但要讓事務處於開啟狀態)：
USE AdventureWorks;
BEGIN TRAN
UPDATE Person.Contact
SET EmailAddress = 'uncommitted@email.at'
WHERE ContactID = 1;
開啟第二個查詢視窗並執行以下語句來讀取ContactID 1的列Name和EmailAddress列。
    USE AdventureWorks;
BEGIN TRAN
SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE ContactID = 1;
返回了聯絡人Gustavo Achong的EmailAddress gustavo0@adventure-works.com，這是這一行最後提交的版本。不像沒有快照的已提交讀級別那樣，這個查詢不會被阻塞。關閉查詢視窗2並切換到查詢視窗1。
執行以下語句來回滾事務並切換回已提交讀級別(這個查詢將等待直到關閉查詢視窗2)：
ROLLBACK TRAN
GO
USE master;
ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT OFF
重要提示 這個隔離級別可以用於減少阻塞。但要意識到這是一個資料庫選項。當它發生了更改，將在資料庫系統中使用已提交讀級別的所有事務也會改變它們的行為。因此，只有在所有這些事務讀最後提交的資料版本與讀真正提交的資料版本在邏輯上同樣正確的時候，使用這種級別才是明智的。
獲取一致的可重複讀操作
已提交讀級別的一個缺點是，一個事務讀取的資料在事務執行期間可能被另一個事務更改。因此，在兩種已提交讀級別下，不能保證一致性讀。獲取一致性讀的意思是，在一個事務中，讀取的資料始終是一樣的。
1. 已提交讀在讀資料的時候使用共享鎖，但在讀操作完成後會立即釋放這個鎖。因此，其他事務可以更改剛被讀過的資料。
2. 已提交讀快照讀取最後一次提交的資料版本。當它第二次讀資料的時候，最後一次提交的版本可能由於第二個事務已經提交了對資料的更改而變成一個新版本。
在需要一致性讀的時候(例如對於報表)， 可能這種不一致性會導致問題。想象一下，您的事務通過資料計算了一些商業數值。在已提交讀級別中進行這種計算的時候，可能由於基礎資料在事務計算過程中發 生了變化而導致這些值被錯誤計算。為了成功地執行這個計算，可以使用快照隔離級別。它會使用行版本管理來提供資料的提交版本，但與已提交讀快照不同的是， 它總會提供在開始事務時最後提交的資料版本。因此，SQL Server始終會在整個事務執行過程中獲取同樣的資料。
使用快照隔離級別
    快照隔離級別需要在資料庫中一次性地啟用。啟用之後，每個連線可以在需要的時候使用它。
    USE master;
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON;
    現在假設我們希望執行一些基於Sales.SalesOrderDetail表的報表，但需要一致性的讀操作。執行以下語句為事務啟用快照隔離級別並開始一個返回訂單行合計的事務。記住OrderTotal的值。
USE AdventureWorks;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
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更改為其他數字以使以下程式碼能真正地更改資料庫中的資料)：
    USE AdventureWorks;
UPDATE Sales.SalesOrderDetail
SET OrderQty = 5
WHERE SalesOrderID = 43659
AND ProductID = 777
    關閉查詢視窗2，切換到查詢視窗1，然後重複下面的SELECT語句。
    SELECT SUM(LineTotal) as OrderTotal
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43659
    可以看出，由於快照隔離級別忽略了事務執行過程中資料的更改，因此結果與以前的相同。在快照級別下總會提供在事務開始時最後提交的值。
提交這個事務並執行以下程式碼再次重複這個查詢：現在可看到，由於事務結束了，因此結果發生了變化。
COMMIT TRAN
SELECT SUM(LineTotal) as OrderTotal
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43659
    執行以下程式碼關閉AdventureWorks資料庫的快照隔離級別：
    ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION OFF;
避免同時發生的資料更新
如 前所述，快照隔離級別並不在讀操作的時候鎖定資料，但能夠在整個事務中提供一致性的檢視。在某些情況下，有必要在整個事務的執行過程中鎖定資料以避免其他 事務對資料的更改。假設希望為一個訂單開發票。首先需要獲取資料並檢查它，然後為其生成發票。在這種情況下，需要從事務起始就鎖定資料以避免其他事務更改 它。在這種情況下，快照隔離或者已提交讀隔離級別都不是好的選擇。對於這種情況，可以使用可重複讀隔離級別。這個隔離級別與沒有快照的已提交讀級別的工作 過程相似，但它會保持共享鎖直至事務結束。因此，它防止了對資料的更新。
使用可重複讀隔離級別
    假設希望處理OrderID為43659的訂單。首先，必須選擇資料。為了防止其他事務更改正在讀的資料，使用可重複讀隔離。
    USE AdventureWorks;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43659
    引數REPEATABLE READ的含義：
1. 指定語句不能讀取已由其他事務修改但尚未提交的行，並且指定，其他任何事務都不能在當前事務完成之前修改由當前事務讀取的資料。
2. 對 事務中的每個語句所讀取的全部資料都設定了共享鎖，並且該共享鎖一直保持到事務完成為止。這樣可以防止其他事務修改當前事務讀取的任何行。其他事務可以插 入與當前事務所發出語句的搜尋條件相匹配的新行。如果當前事務隨後重試執行該語句，它會檢索新行，從而產生幻讀。由於共享鎖一直保持到事務結束，而不是在 每個語句結束時釋放，所以併發級別低於預設的 READ COMMITTED 隔離級別。此選項只在必要時使用。
開啟第二個查詢視窗並執行以下程式碼嘗試更新SalesOrderDetail表以更改查詢視窗1中要使用的基礎資料：
    UPDATE Sales.SalesOrderDetail
SET OrderQty = 5
WHERE SalesOrderID = 43659
AND ProductID = 777
    查詢會等待。不像快照隔離級別，不可能更新資料，因為共享鎖會保持以防止其他事務更改資料。這個鎖可以通過前面用過的管理檢視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
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43659
COMMIT TRAN
    可以觀察到，新行被SELECT語句讀取了，因為它處於這個語句的查詢範圍之內。可重複讀級別會阻止現有資料被更改，但不會阻止新資料插入SELECT語句的查詢範圍內。
其他
    SET TRANSACTION一共有以下幾種級別：
    SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
[ ; ]
    上面的例子中沒有提到的幾種隔離級別的說明：
  1. READ UNCOMMITTED
指定語句可以讀取已由其他事務修改但尚未提交的行。
在 READ UNCOMMITTED 級別執行的事務，不會發出共享鎖來防止其他事務修改當前事務讀取的資料。READ UNCOMMITTED 事務也不會被排他鎖阻塞，排他鎖會禁止當前事務讀取其他事務已修改但尚未提交的行。設定此選項之後，可以讀取未提交的修改，這種讀取稱為髒讀。在事務結束之前，可以更改資料中的值，行也可以出現在資料集中或從資料集中消失。該選項的作用與在事務內所有 SELECT 語句中的所有表上設定 NOLOCK 相同。這是隔離級別中限制最少的級別。
在 SQL Server 2005 中，您還可以使用下列任意一種方法，在保護事務不髒讀未提交的資料修改的同時儘量減少鎖定爭用：
1. READ COMMITTED 隔離級別，並將 READ_COMMITTED_SNAPSHOT 資料庫選項設定為 ON。
2. SNAPSHOT 隔離級別。
  1. 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 隔離級別上執行的事務中的各語句請求共享鎖，而不是行版本控制。
    注意：設定 READ_COMMITTED_SNAPSHOT 選項時，資料庫中僅允許存在執行 ALTER DATABASE 命令的連線。在 ALTER DATABASE 完成之前，資料庫中不允許有其他開啟的連線。資料庫不必處於單使用者模式。
  1. 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。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-627731/，如需轉載，請註明出處，否則將追究法律責任。

相關文章