事務的隔離級別
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
。打開第二個查詢窗口并執行以下批來
UPDATE EmailAddress
，但不提交事務：
USE
AdventureWorks
;
?
BEGIN
TRAN
UPDATE
????Person
.
Contact
SET
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
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
}
[ ; ]
????
上面的例子中沒有提到的幾種隔離級別的說明：
- 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
隔離級別上運行的事務中的各語句請求共享鎖，而不是行版本控制。
????
注意：設置
READ_COMMITTED_SNAPSHOT
選項時，數據庫中僅允許存在執行
ALTER DATABASE
命令的連接。在
ALTER DATABASE
完成之前，數據庫中不允許有其他打開的連接。數據庫不必處于單用戶模式。
- 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
。
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
