事務(wù)的隔離級(jí)別
SQL Server
通過在鎖資源上使用不同類型的鎖來隔離事務(wù)。為了開發(fā)安全的事務(wù)，定義事務(wù)內(nèi)容以及應(yīng)在何種情況下回滾至關(guān)重要，定義如何以及在多長(zhǎng)時(shí)間內(nèi)在事務(wù)中保持鎖定也同等重要。這由隔離級(jí)別決定。應(yīng)用不同的隔離級(jí)別，
SQL Server
賦予開發(fā)者一種能力，讓他們?yōu)槊恳粋€(gè)單獨(dú)事務(wù)定義與其他事務(wù)的隔離程度。事務(wù)隔離級(jí)別的定義如下：
- 是否在讀數(shù)據(jù)的時(shí)候使用鎖
- 讀鎖持續(xù)多長(zhǎng)時(shí)間
- 在讀數(shù)據(jù)的時(shí)候使用何種類型的鎖
-
讀操作希望讀已經(jīng)被其他事務(wù)排他鎖住的數(shù)據(jù)時(shí)，怎么辦？在這種情況下，
SQL Server
可以：
- 一直等到其他事務(wù)釋放鎖
- 讀沒有提交的數(shù)據(jù)
- 讀數(shù)據(jù)最后提交后的版本
ANSI 99
定義了
4
種事務(wù)隔離級(jí)別，
SQL Server 2005
能夠完全支持這些級(jí)別：
- 未提交讀 在讀數(shù)據(jù)時(shí)不會(huì)檢查或使用任何鎖。因此，在這種隔離級(jí)別中可能讀取到?jīng)]有提交的數(shù)據(jù)。
- 已提交讀 只讀取提交的數(shù)據(jù)并等待其他事務(wù)釋放排他鎖。讀數(shù)據(jù)的共享鎖在讀操作完成后立即釋放。已提交讀是 SQL Server 的默認(rèn)隔離級(jí)別。
- 可重復(fù)讀 像已提交讀級(jí)別那樣讀數(shù)據(jù)，但會(huì)保持共享鎖直到事務(wù)結(jié)束。
- 可序列化 工作方式類似于可重復(fù)讀。但它不僅會(huì)鎖定受影響的數(shù)據(jù)，還會(huì)鎖定這個(gè)范圍。這就阻止了新數(shù)據(jù)插入查詢所涉及的范圍，這種情況可以導(dǎo)致幻像讀。
?
此外，
SQL Server
還有兩種使用行版本控制來讀取數(shù)據(jù)的事務(wù)級(jí)別
(
本章后文將詳細(xì)檢驗(yàn)這些隔離級(jí)別
)
。行版本控制允許一個(gè)事務(wù)在數(shù)據(jù)排他鎖定后讀取數(shù)據(jù)的最后提交版本。由于不必等待到鎖釋放就可進(jìn)行讀操作，因此查詢性能得以大大增強(qiáng)。這兩種隔離級(jí)別如下：
- 已提交讀快照 它是一種提交讀級(jí)別的新實(shí)現(xiàn)。不像一般的提交讀級(jí)別， SQL Server 會(huì)讀取最后提交的版本并因此不必在進(jìn)行讀操作時(shí)等待直到鎖被釋放。這個(gè)級(jí)別可以替代提交讀級(jí)別。
- 快照 這種隔離使用行版本來提供事務(wù)級(jí)別的讀取一致性。這意味著在一個(gè)事務(wù)中，由于讀一致性可以通過行版本控制實(shí)現(xiàn)，因此同樣的數(shù)據(jù)總是可以像在可序列化級(jí)別上一樣被讀取而不必為防止來自其他事務(wù)的更改而被鎖定。
?
無論定義什么隔離級(jí)別，對(duì)數(shù)據(jù)的更改總是通過排他鎖來鎖定并直到事務(wù)結(jié)束時(shí)才釋放。
很多情況下，定義正確的隔離級(jí)別并不是一個(gè)簡(jiǎn)單的決定。作為一種通用的規(guī)則，要選擇在盡可能短的時(shí)間內(nèi)鎖住最少數(shù)據(jù)，但同時(shí)依然可以為事務(wù)提供它所需的安全程度的隔離級(jí)別。
已提交讀
在
SQL Server 2005
中，已提交讀隔離級(jí)別是建立連接時(shí)的默認(rèn)隔離級(jí)別。這個(gè)級(jí)別存在兩種類型：已提交讀和已提交讀快照隔離級(jí)別。應(yīng)用哪種類型由數(shù)據(jù)庫(kù)選項(xiàng)定義。已提交讀級(jí)別會(huì)在讀數(shù)據(jù)之前等待，直到阻塞鎖被釋放。已提交讀快照級(jí)別會(huì)在數(shù)據(jù)被其他事務(wù)阻塞時(shí)使用行版本控制來讀數(shù)據(jù)最后一次提交的版本。
使用已提交讀級(jí)別：
BEGIN
TRAN
?
SELECT
????FirstName
,
LastName
,
EmailAddress
FROM
????Person
.
Contact
WHERE
ContactID
=
1
?
現(xiàn)在假設(shè)另一事務(wù)在事務(wù)打開狀態(tài)下更改了
EmailAddress
。打開第二個(gè)查詢窗口并執(zhí)行以下批來
UPDATE EmailAddress
，但不提交事務(wù)：
USE
AdventureWorks
;
?
BEGIN
TRAN
UPDATE
????Person
.
Contact
SET
WHERE
????ContactID
=
1
????
這個(gè)
UPDATE
語句會(huì)正常運(yùn)行。一行受到了影響，即使數(shù)據(jù)在這個(gè)事務(wù)還沒有運(yùn)行完之前已被查詢窗口
1
中的事務(wù)讀取。因?yàn)橐烟峤蛔x級(jí)別并不會(huì)在事務(wù)結(jié)束前保持用于
SELECT
語句的共享鎖。共享鎖會(huì)在數(shù)據(jù)讀取之后立即被
SQL Server
釋放。需要一致讀的時(shí)候這將是一個(gè)問題。我們將下面的"獲取一致的可重復(fù)讀操作"實(shí)現(xiàn)。
????
現(xiàn)在切換到查詢窗口
1
并嘗試再次讀數(shù)據(jù)：
????
SELECT
????????FirstName
,
LastName
,
EmailAddress
FROM
????????Person
.
Contact
WHERE
????????ContactID
=
1
?
????
由于
SELECT
語句被阻塞，因此這個(gè)查詢并沒有結(jié)束。
SQL Server
會(huì)嘗試在
ContactID= 1
的鍵上獲取一個(gè)共享鎖，但是由于在查詢窗口
2
中的
UPDATE
語句對(duì)其有一個(gè)排他鎖，因此這個(gè)操作不可能完成。雖然查詢窗口
2
處于已提交讀級(jí)別
(
由于您沒有更改默認(rèn)級(jí)別
)
，但排他鎖依然存在。這個(gè)阻塞將持續(xù)存在，因?yàn)閿?shù)據(jù)更改的排他鎖會(huì)一直保持直到事務(wù)結(jié)束。
切換到查詢窗口
2
，讓查詢窗口
1
中的查詢繼續(xù)運(yùn)行。鍵入并執(zhí)行以下
SELECT
語句檢查數(shù)據(jù)庫(kù)中的授權(quán)和等待的鎖。
可以看一個(gè)狀態(tài)為
WAIT
的共享鎖。這是查詢窗口
1
中運(yùn)行的查詢。它在等待查詢窗口
2
中的查詢，后者在同樣的資源上有一個(gè)排他鎖。
在查詢窗口
2
中執(zhí)行一個(gè)
ROLLBACK
TRAN
語句來回滾
UPDATE
語句。然后切換回查詢窗口
1
。可以看到，查詢窗口
1
中的查詢完成了，并且其結(jié)果與以前的一樣。查詢窗口
2
中的事務(wù)結(jié)束的時(shí)候，鎖被釋放了，以至查詢窗口
1
中的查詢不再被阻塞。由于查詢窗口
2
中的事務(wù)回滾，因此查詢窗口
1
中得到的結(jié)果是原來的數(shù)據(jù)。如果查詢窗口
2
中的事務(wù)被提交，則查詢窗口
1
中會(huì)得到新的數(shù)據(jù)作為結(jié)果。
在查詢窗口
1
中執(zhí)行一個(gè)
COMMIT
TRAN
語句并關(guān)閉所有的查詢窗口。
可以看出，在
(
默認(rèn)
)
已提交讀級(jí)別中
SQL Server
會(huì)等到排他鎖釋放之后再進(jìn)行讀操作，以此來獲取真正的提交數(shù)據(jù)。還可以看出，共享鎖會(huì)持續(xù)到數(shù)據(jù)被讀取之后，而排他鎖會(huì)持續(xù)到事務(wù)提交之后。在許多事務(wù)幾乎同時(shí)更改數(shù)據(jù)的時(shí)候這種行為可能會(huì)造成問題。在這些情況下，由于排他鎖造成的阻塞，讀數(shù)據(jù)會(huì)非常慢。但在有些情況下，使用最后提交的數(shù)據(jù)版本是恰當(dāng)?shù)摹Ｔ谶@些情況下，可以將已提交讀級(jí)別更改為已提交讀快照級(jí)別。
如果要在窗口
1
讀取數(shù)據(jù)的話，可以使用這樣的方法：
SELECT
????FirstName
,
LastName
,
EmailAddress
FROM
????Person
.
Contact
WITH
(
NOLOCK
)
WHERE
????ContactID
=
1
????
讓它取消所有的鎖機(jī)制，那么排他鎖也不會(huì)影響到這句查詢。
????
使用
NOLOCK
注意：在
SQL Server
中，
NOLOCK
提示將啟用"未提交讀"行為。在
SQL Server Mobile
中，使用
NOLOCK
提示仍會(huì)賦予"提交讀"隔離級(jí)別。
SQL Server Mobile
將維護(hù)數(shù)據(jù)副本，以確保可以讀取數(shù)據(jù)而不需要使用共享鎖幫助保護(hù)數(shù)據(jù)。
使用已提交讀快照級(jí)別
激活已提交讀快照級(jí)別
USE
master
;
ALTER
DATABASE
AdventureWorks
SET
READ_COMMITTED_SNAPSHOT
ON
????
注意
：設(shè)置
READ_COMMITTED_SNAPSHOT
選項(xiàng)時(shí)，數(shù)據(jù)庫(kù)中僅允許存在執(zhí)行
ALTER DATABASE
命令的連接。在
ALTER DATABASE
完成之前，數(shù)據(jù)庫(kù)中不允許有其他打開的連接。數(shù)據(jù)庫(kù)不必處于單用戶模式。
現(xiàn)在，執(zhí)行以下代碼開始一個(gè)事務(wù)并像前面一樣更改
EmailAddress(
但要讓事務(wù)處于打開狀態(tài)
)
：
USE
AdventureWorks
;
BEGIN
TRAN
UPDATE
Person
.
Contact
WHERE
ContactID
=
1
;
打開第二個(gè)查詢窗口并執(zhí)行以下語句來讀取
ContactID 1
的列
Name
和
EmailAddress
列。
????
USE
AdventureWorks
;
BEGIN
TRAN
SELECT
FirstName
,
LastName
,
EmailAddress
FROM
Person
.
Contact
WHERE
ContactID
=
1
;
返回了聯(lián)系人
Gustavo Achong
的
EmailAddress
gustavo0@adventure-works.com
，這是這一行最后提交的版本。不像沒有快照的已提交讀級(jí)別那樣，這個(gè)查詢不會(huì)被阻塞。關(guān)閉查詢窗口
2
并切換到查詢窗口
1
。
執(zhí)行以下語句來回滾事務(wù)并切換回已提交讀級(jí)別
(
這個(gè)查詢將等待直到關(guān)閉查詢窗口
2)
：
ROLLBACK
TRAN
GO
USE
master
;
ALTER
DATABASE
AdventureWorks
SET
READ_COMMITTED_SNAPSHOT
OFF
重要提示
這個(gè)隔離級(jí)別可以用于減少阻塞。但要意識(shí)到這是一個(gè)數(shù)據(jù)庫(kù)選項(xiàng)。當(dāng)它發(fā)生了更改，將在數(shù)據(jù)庫(kù)系統(tǒng)中使用已提交讀級(jí)別的所有事務(wù)也會(huì)改變它們的行為。因此，只有在所有這些事務(wù)讀最后提交的數(shù)據(jù)版本與讀真正提交的數(shù)據(jù)版本在邏輯上同樣正確的時(shí)候，使用這種級(jí)別才是明智的。
獲取一致的可重復(fù)讀操作
已提交讀級(jí)別的一個(gè)缺點(diǎn)是，一個(gè)事務(wù)讀取的數(shù)據(jù)在事務(wù)運(yùn)行期間可能被另一個(gè)事務(wù)更改。因此，在兩種已提交讀級(jí)別下，不能保證一致性讀。獲取一致性讀的意思是，在一個(gè)事務(wù)中，讀取的數(shù)據(jù)始終是一樣的。
1.?
已提交讀在讀數(shù)據(jù)的時(shí)候使用共享鎖，但在讀操作完成后會(huì)立即釋放這個(gè)鎖。因此，其他事務(wù)可以更改剛被讀過的數(shù)據(jù)。
2.?
已提交讀快照讀取最后一次提交的數(shù)據(jù)版本。當(dāng)它第二次讀數(shù)據(jù)的時(shí)候，最后一次提交的版本可能由于第二個(gè)事務(wù)已經(jīng)提交了對(duì)數(shù)據(jù)的更改而變成一個(gè)新版本。
在需要一致性讀的時(shí)候
(
例如對(duì)于報(bào)表
)
，可能這種不一致性會(huì)導(dǎo)致問題。想象一下，您的事務(wù)通過數(shù)據(jù)計(jì)算了一些商業(yè)數(shù)值。在已提交讀級(jí)別中進(jìn)行這種計(jì)算的時(shí)候，可能由于基礎(chǔ)數(shù)據(jù)在事務(wù)計(jì)算過程中發(fā)生了變化而導(dǎo)致這些值被錯(cuò)誤計(jì)算。為了成功地執(zhí)行這個(gè)計(jì)算，可以使用快照隔離級(jí)別。它會(huì)使用行版本管理來提供數(shù)據(jù)的提交版本，但與已提交讀快照不同的是，它總會(huì)提供在開始事務(wù)時(shí)最后提交的數(shù)據(jù)版本。因此，
SQL Server
始終會(huì)在整個(gè)事務(wù)執(zhí)行過程中獲取同樣的數(shù)據(jù)。
使用快照隔離級(jí)別
????
快照隔離級(jí)別需要在數(shù)據(jù)庫(kù)中一次性地激活。激活之后，每個(gè)連接可以在需要的時(shí)候使用它。
????
USE
master
;
ALTER
DATABASE
AdventureWorks
SET
ALLOW_SNAPSHOT_ISOLATION
ON
;
????
現(xiàn)在假設(shè)我們希望運(yùn)行一些基于
Sales.SalesOrderDetail
表的報(bào)表，但需要一致性的讀操作。執(zhí)行以下語句為事務(wù)激活快照隔離級(jí)別并開始一個(gè)返回訂單行合計(jì)的事務(wù)。記住
OrderTotal
的值。
USE
AdventureWorks
;
SET
TRANSACTION
ISOLATION
LEVEL
SNAPSHOT
BEGIN
TRAN
SELECT
SUM
(
LineTotal
)
as
OrderTotal
FROM
Sales
.
SalesOrderDetail
WHERE
SalesOrderID
=
43659
參數(shù)
SNAPSHOT
的含義：
1.
?????
指定事務(wù)中任何語句讀取的數(shù)據(jù)都將是在事務(wù)開始時(shí)便存在的數(shù)據(jù)的事務(wù)上一致的版本。事務(wù)只能識(shí)別在其開始之前提交的數(shù)據(jù)修改。在當(dāng)前事務(wù)中執(zhí)行的語句將看不到在當(dāng)前事務(wù)開始以后由其他事務(wù)所做的數(shù)據(jù)修改。其效果就好像事務(wù)中的語句獲得了已提交數(shù)據(jù)的快照，因?yàn)樵摂?shù)據(jù)在事務(wù)開始時(shí)就存在。
2.
?????
除非正在恢復(fù)數(shù)據(jù)庫(kù)，否則
SNAPSHOT
事務(wù)不會(huì)在讀取數(shù)據(jù)時(shí)請(qǐng)求鎖。讀取數(shù)據(jù)的
SNAPSHOT
事務(wù)不會(huì)阻止其他事務(wù)寫入數(shù)據(jù)。寫入數(shù)據(jù)的事務(wù)也不會(huì)阻止
SNAPSHOT
事務(wù)讀取數(shù)據(jù)。
3.
?????
在數(shù)據(jù)庫(kù)恢復(fù)的回滾階段，如果嘗試讀取由其他正在回滾的事務(wù)鎖定的數(shù)據(jù)，則
SNAPSHOT
事務(wù)將請(qǐng)求一個(gè)鎖。在事務(wù)完成回滾之前，
SNAPSHOT
事務(wù)會(huì)一直被阻塞。當(dāng)事務(wù)取得授權(quán)之后，便會(huì)立即釋放鎖。
4.
?????
必須將
ALLOW_SNAPSHOT_ISOLATION
數(shù)據(jù)庫(kù)選項(xiàng)設(shè)置為
ON
，才能開始一個(gè)使用
SNAPSHOT
隔離級(jí)別的事務(wù)。如果使用
SNAPSHOT
隔離級(jí)別的事務(wù)訪問多個(gè)數(shù)據(jù)庫(kù)中的數(shù)據(jù)，則必須在每個(gè)數(shù)據(jù)庫(kù)中將
ALLOW_SNAPSHOT_ISOLATION
都設(shè)置為
ON
。
5.
?????
不能將通過其他隔離級(jí)別開始的事務(wù)設(shè)置為
SNAPSHOT
隔離級(jí)別，否則將導(dǎo)致事務(wù)中止。如果一個(gè)事務(wù)在
SNAPSHOT
隔離級(jí)別開始，則可以將它更改為另一個(gè)隔離級(jí)別，然后再返回
SNAPSHOT
。一個(gè)事務(wù)從執(zhí)行
BEGIN TRANSACTION
語句開始。
6.
?????
在
SNAPSHOT
隔離級(jí)別下運(yùn)行的事務(wù)可以查看由該事務(wù)所做的更改。例如，如果事務(wù)對(duì)表執(zhí)行
UPDATE
，然后對(duì)同一個(gè)表發(fā)出
SELECT
語句，則修改后的數(shù)據(jù)將包含在結(jié)果集中。
????
打開第二個(gè)查詢窗口并更新
SalesOrderDetail
表以更改查詢窗口
1
中用到的基礎(chǔ)數(shù)據(jù)。
(
如果希望重復(fù)這個(gè)示例，將
OrderQty
的值
5
更改為其他數(shù)字以使以下代碼能真正地更改數(shù)據(jù)庫(kù)中的數(shù)據(jù)
)
：
????
USE
AdventureWorks
;
UPDATE
Sales
.
SalesOrderDetail
SET
OrderQty
=
5
WHERE
SalesOrderID
=
43659
AND
ProductID
=
777
????
關(guān)閉查詢窗口
2
，切換到查詢窗口
1
，然后重復(fù)下面的
SELECT
語句。
????
SELECT
SUM
(
LineTotal
)
as
OrderTotal
FROM
Sales
.
SalesOrderDetail
WHERE
SalesOrderID
=
43659
????
可以看出，由于快照隔離級(jí)別忽略了事務(wù)運(yùn)行過程中數(shù)據(jù)的更改，因此結(jié)果與以前的相同。在快照級(jí)別下總會(huì)提供在事務(wù)開始時(shí)最后提交的值。
提交這個(gè)事務(wù)并執(zhí)行以下代碼再次重復(fù)這個(gè)查詢：現(xiàn)在可看到，由于事務(wù)結(jié)束了，因此結(jié)果發(fā)生了變化。
COMMIT
TRAN
SELECT
SUM
(
LineTotal
)
as
OrderTotal
FROM
Sales
.
SalesOrderDetail
WHERE
SalesOrderID
=
43659
????
執(zhí)行以下代碼關(guān)閉
AdventureWorks
數(shù)據(jù)庫(kù)的快照隔離級(jí)別：
????
ALTER
DATABASE
AdventureWorks
SET
ALLOW_SNAPSHOT_ISOLATION
OFF
;
避免同時(shí)發(fā)生的數(shù)據(jù)更新
如前所述，快照隔離級(jí)別并不在讀操作的時(shí)候鎖定數(shù)據(jù)，但能夠在整個(gè)事務(wù)中提供一致性的視圖。在某些情況下，有必要在整個(gè)事務(wù)的執(zhí)行過程中鎖定數(shù)據(jù)以避免其他事務(wù)對(duì)數(shù)據(jù)的更改。假設(shè)希望為一個(gè)訂單開發(fā)票。首先需要獲取數(shù)據(jù)并檢查它，然后為其生成發(fā)票。在這種情況下，需要從事務(wù)起始就鎖定數(shù)據(jù)以避免其他事務(wù)更改它。在這種情況下，快照隔離或者已提交讀隔離級(jí)別都不是好的選擇。對(duì)于這種情況，可以使用可重復(fù)讀隔離級(jí)別。這個(gè)隔離級(jí)別與沒有快照的已提交讀級(jí)別的工作過程相似，但它會(huì)保持共享鎖直至事務(wù)結(jié)束。因此，它防止了對(duì)數(shù)據(jù)的更新。
使用可重復(fù)讀隔離級(jí)別
????
假設(shè)希望處理
OrderID
為
43659
的訂單。首先，必須選擇數(shù)據(jù)。為了防止其他事務(wù)更改正在讀的數(shù)據(jù)，使用可重復(fù)讀隔離。
????
USE
AdventureWorks
;
SET
TRANSACTION
ISOLATION
LEVEL
REPEATABLE
READ
BEGIN
TRAN
SELECT
SalesOrderID
,
SalesOrderDetailID
,
ProductID
,
OrderQty
FROM
Sales
.
SalesOrderDetail
WHERE
SalesOrderID
=
43659
????
參數(shù)
REPEATABLE READ
的含義：
1.?
指定語句不能讀取已由其他事務(wù)修改但尚未提交的行，并且指定，其他任何事務(wù)都不能在當(dāng)前事務(wù)完成之前修改由當(dāng)前事務(wù)讀取的數(shù)據(jù)。
2.?
對(duì)事務(wù)中的每個(gè)語句所讀取的全部數(shù)據(jù)都設(shè)置了共享鎖，并且該共享鎖一直保持到事務(wù)完成為止。這樣可以防止其他事務(wù)修改當(dāng)前事務(wù)讀取的任何行。其他事務(wù)可以插入與當(dāng)前事務(wù)所發(fā)出語句的搜索條件相匹配的新行。如果當(dāng)前事務(wù)隨后重試執(zhí)行該語句，它會(huì)檢索新行，從而產(chǎn)生幻讀。由于共享鎖一直保持到事務(wù)結(jié)束，而不是在每個(gè)語句結(jié)束時(shí)釋放，所以并發(fā)級(jí)別低于默認(rèn)的
READ COMMITTED
隔離級(jí)別。此選項(xiàng)只在必要時(shí)使用。
打開第二個(gè)查詢窗口并執(zhí)行以下代碼嘗試更新
SalesOrderDetail
表以更改查詢窗口
1
中要使用的基礎(chǔ)數(shù)據(jù)：
????
UPDATE
Sales
.
SalesOrderDetail
SET
OrderQty
=
5
WHERE
SalesOrderID
=
43659
AND
ProductID
=
777
????
查詢會(huì)等待。不像快照隔離級(jí)別，不可能更新數(shù)據(jù)，因?yàn)楣蚕礞i會(huì)保持以防止其他事務(wù)更改數(shù)據(jù)。這個(gè)鎖可以通過前面用過的管理視圖
sys.dm_tran_locks
查看。
????
單擊工具條上的
"
取消執(zhí)行查詢
"
按鈕取消在查詢窗口
2
中的查詢。而執(zhí)行以下
INSERT
語句在訂單中加入一個(gè)新行項(xiàng)。
????
INSERT
INTO
Sales
.
SalesOrderDetail
(
????SalesOrderID
,
????CarrierTrackingNumber
,
????OrderQty
,
????ProductID
,
????SpecialOfferID
,
????UnitPrice
,
????UnitPriceDiscount
)
VALUES
(
43659
,
'4911-403C-98'
,
1
,
758
,
1
,
874
,
0
)
????
注意，即使正處于可重復(fù)讀隔離級(jí)別，這個(gè)語句也會(huì)成功執(zhí)行。因?yàn)榭芍貜?fù)讀會(huì)鎖定數(shù)據(jù)以阻止數(shù)據(jù)的更新，但
INSERT
語句向數(shù)據(jù)庫(kù)中插入新數(shù)據(jù)，這是允許的。新行處于查詢窗口
1
中事務(wù)
SELECT
語句的查詢范圍之中，所以會(huì)在事務(wù)下一次獲取相同數(shù)據(jù)的時(shí)候被讀取到。這稱作幻像讀。
????
重復(fù)
SELECT
語句并提交這個(gè)事務(wù)，如下所示：
????
SELECT
SalesOrderID
,
SalesOrderDetailID
,
ProductID
,
OrderQty
FROM
Sales
.
SalesOrderDetail
WHERE
SalesOrderID
=
43659
COMMIT
TRAN
????
可以觀察到，新行被
SELECT
語句讀取了，因?yàn)樗幱谶@個(gè)語句的查詢范圍之內(nèi)。可重復(fù)讀級(jí)別會(huì)阻止現(xiàn)有數(shù)據(jù)被更改，但不會(huì)阻止新數(shù)據(jù)插入
SELECT
語句的查詢范圍內(nèi)。
其他
????
SET
TRANSACTION
一共有以下幾種級(jí)別：
????
SET
TRANSACTION
ISOLATION
LEVEL
{
READ
UNCOMMITTED
|
READ
COMMITTED
|
REPEATABLE
READ
|
SNAPSHOT
|
SERIALIZABLE
}
[ ; ]
????
上面的例子中沒有提到的幾種隔離級(jí)別的說明：
- READ UNCOMMITTED
指定語句可以讀取已由其他事務(wù)修改但尚未提交的行。
在
READ UNCOMMITTED
級(jí)別運(yùn)行的事務(wù)，不會(huì)發(fā)出共享鎖來防止其他事務(wù)修改當(dāng)前事務(wù)讀取的數(shù)據(jù)。
READ UNCOMMITTED
事務(wù)也不會(huì)被排他鎖阻塞，排他鎖會(huì)禁止當(dāng)前事務(wù)讀取其他事務(wù)已修改但尚未提交的行。設(shè)置此選項(xiàng)之后，可以讀取未提交的修改，這種讀取稱為臟讀。在事務(wù)結(jié)束之前，可以更改數(shù)據(jù)中的值，行也可以出現(xiàn)在數(shù)據(jù)集中或從數(shù)據(jù)集中消失。該選項(xiàng)的作用與在事務(wù)內(nèi)所有
SELECT
語句中的所有表上設(shè)置
NOLOCK
相同。這是隔離級(jí)別中限制最少的級(jí)別。
在
SQL Server 2005
中，您還可以使用下列任意一種方法，在保護(hù)事務(wù)不臟讀未提交的數(shù)據(jù)修改的同時(shí)盡量減少鎖定爭(zhēng)用：
1.?
READ COMMITTED
隔離級(jí)別，并將
READ_COMMITTED_SNAPSHOT
數(shù)據(jù)庫(kù)選項(xiàng)設(shè)置為
ON
。
2.?
SNAPSHOT
隔離級(jí)別。
- READ COMMITTED
指定語句不能讀取已由其他事務(wù)修改但尚未提交的數(shù)據(jù)。這樣可以避免臟讀。其他事務(wù)可以在當(dāng)前事務(wù)的各個(gè)語句之間更改數(shù)據(jù)，從而產(chǎn)生不可重復(fù)讀取和幻像數(shù)據(jù)。該選項(xiàng)是
SQL Server
的默認(rèn)設(shè)置。
READ COMMITTED
的行為取決于
READ_COMMITTED_SNAPSHOT
數(shù)據(jù)庫(kù)選項(xiàng)的設(shè)置：
1.?
如果將
READ_COMMITTED_SNAPSHOT
設(shè)置為
OFF
（默認(rèn)設(shè)置），則數(shù)據(jù)庫(kù)引擎
會(huì)使用共享鎖防止其他事務(wù)在當(dāng)前事務(wù)執(zhí)行讀取操作期間修改行。共享鎖還會(huì)阻止語句在其他事務(wù)完成之前讀取由這些事務(wù)修改的行。語句完成后便會(huì)釋放共享鎖。
2.?
如果將
READ_COMMITTED_SNAPSHOT
設(shè)置為
ON
，則數(shù)據(jù)庫(kù)引擎
會(huì)使用行版本控制為每個(gè)語句提供一個(gè)在事務(wù)上一致的數(shù)據(jù)快照，因?yàn)樵摂?shù)據(jù)在語句開始時(shí)就存在。不使用鎖來防止其他事務(wù)更新數(shù)據(jù)。
當(dāng)
READ_COMMITTED_SNAPSHOT
數(shù)據(jù)庫(kù)選項(xiàng)設(shè)置為
ON
時(shí)，您可以使用
READCOMMITTEDLOCK
表提示為
READ_COMMITTED
隔離級(jí)別上運(yùn)行的事務(wù)中的各語句請(qǐng)求共享鎖，而不是行版本控制。
????
注意：設(shè)置
READ_COMMITTED_SNAPSHOT
選項(xiàng)時(shí)，數(shù)據(jù)庫(kù)中僅允許存在執(zhí)行
ALTER DATABASE
命令的連接。在
ALTER DATABASE
完成之前，數(shù)據(jù)庫(kù)中不允許有其他打開的連接。數(shù)據(jù)庫(kù)不必處于單用戶模式。
- SERIALIZABLE
請(qǐng)指定下列內(nèi)容：
1.?
語句不能讀取已由其他事務(wù)修改但尚未提交的數(shù)據(jù)。
2.?
任何其他事務(wù)都不能在當(dāng)前事務(wù)完成之前修改由當(dāng)前事務(wù)讀取的數(shù)據(jù)。
3.?
在當(dāng)前事務(wù)完成之前，其他事務(wù)不能使用當(dāng)前事務(wù)中任何語句讀取的鍵值插入新行。
范圍鎖處于與事務(wù)中執(zhí)行的每個(gè)語句的搜索條件相匹配的鍵值范圍之內(nèi)。這樣可以阻止其他事務(wù)更新或插入任何行，從而限定當(dāng)前事務(wù)所執(zhí)行的任何語句。這意味著如果再次執(zhí)行事務(wù)中的任何語句，則這些語句便會(huì)讀取同一組行。在事務(wù)完成之前將一直保持范圍鎖。這是限制最多的隔離級(jí)別，因?yàn)樗i定了鍵的整個(gè)范圍，并在事務(wù)完成之前一直保持范圍鎖。因?yàn)椴l(fā)級(jí)別較低，所以應(yīng)只在必要時(shí)才使用該選項(xiàng)。該選項(xiàng)的作用與在事務(wù)內(nèi)所有
SELECT
語句中的所有表上設(shè)置
HOLDLOCK
相同。
需要注意的地方：
1.?
一次只能設(shè)置一個(gè)隔離級(jí)別選項(xiàng)，而且設(shè)置的選項(xiàng)將一直對(duì)那個(gè)連接始終有效，直到顯式更改該選項(xiàng)為止。事務(wù)中執(zhí)行的所有讀取操作都會(huì)在指定的隔離級(jí)別的規(guī)則下運(yùn)行，除非語句的
FROM
子句中的表提示為表指定了其他鎖定行為或版本控制行為。
2.?
事務(wù)隔離級(jí)別定義了可為讀取操作獲取的鎖類型。針對(duì)
READ COMMITTED
或
REPEATABLE READ
獲取的共享鎖通常為行鎖，盡管當(dāng)讀取引用了頁(yè)或表中大量的行時(shí)，行鎖可以升級(jí)為頁(yè)鎖或表鎖。如果某行在被讀取之后由事務(wù)進(jìn)行了修改，則該事務(wù)會(huì)獲取一個(gè)用于保護(hù)該行的排他鎖，并且該排他鎖在事務(wù)完成之前將一直保持。例如，如果
REPEATABLE READ
事務(wù)具有用于某行的共享鎖，并且該事務(wù)隨后修改了該行，則共享行鎖便會(huì)轉(zhuǎn)換為排他行鎖。
3.?
在事務(wù)進(jìn)行期間，可以隨時(shí)將事務(wù)從一個(gè)隔離級(jí)別切換到另一個(gè)隔離級(jí)別，但有一種情況例外。即在從任一隔離級(jí)別更改到
SNAPSHOT
隔離時(shí)，不能進(jìn)行上述操作。否則會(huì)導(dǎo)致事務(wù)失敗并回滾。但是，可以將在
SNAPSHOT
隔離中啟動(dòng)的事務(wù)更改為任何其他隔離級(jí)別。
4.?
將事務(wù)從一個(gè)隔離級(jí)別更改為另一個(gè)隔離級(jí)別之后，便會(huì)根據(jù)新級(jí)別的規(guī)則對(duì)更改后讀取的資源執(zhí)行保護(hù)。在更改前讀取的資源將繼續(xù)按照以前級(jí)別的規(guī)則受到保護(hù)。例如，如果某個(gè)事務(wù)從
READ COMMITTED
更改為
SERIALIZABLE
，則在該事務(wù)結(jié)束前，更改后所獲取的共享鎖將一直處于保留狀態(tài)。
5.?
如果在存儲(chǔ)過程或觸發(fā)器中發(fā)出
SET TRANSACTION ISOLATION LEVEL
，則當(dāng)對(duì)象返回控制時(shí)，隔離級(jí)別會(huì)重設(shè)為在調(diào)用對(duì)象時(shí)有效的級(jí)別。例如，如果在批處理中設(shè)置
REPEATABLE READ
，并且該批處理調(diào)用一個(gè)將隔離級(jí)別設(shè)置為
SERIALIZABLE
的存儲(chǔ)過程，則當(dāng)該存儲(chǔ)過程將控制返回給該批處理時(shí)，隔離級(jí)別就會(huì)恢復(fù)為
REPEATABLE READ
。
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
