共計(jì) 3960 個(gè)字符，預(yù)計(jì)需要花費(fèi) 10 分鐘才能閱讀完成。
這篇文章將為大家詳細(xì)講解有關(guān) SQL Server 中四類事務(wù)并發(fā)問(wèn)題的示例分析，丸趣 TV 小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。
SQL Server 中四類事務(wù)并發(fā)問(wèn)題的實(shí)例再現(xiàn)
首先，讓我們先來(lái)了解一下并行問(wèn)題以及事務(wù)隔離級(jí)別這兩個(gè)概念。
在數(shù)據(jù)庫(kù)中，假設(shè)如果沒有鎖定且多個(gè)用戶同時(shí)訪問(wèn)一個(gè)數(shù)據(jù)庫(kù)，則當(dāng)他們的事務(wù)同時(shí)使用相同的數(shù)據(jù)時(shí)可能會(huì)發(fā)生問(wèn)題。并發(fā)問(wèn)題包括：
丟失或覆蓋更新。
未確認(rèn)的相關(guān)性（臟讀）。
不一致的分析（非重復(fù)讀）。
幻像讀。
下面讓我們稍花點(diǎn)時(shí)間來(lái)解釋一下這四類問(wèn)題：
1、丟失更新
當(dāng)兩個(gè)或多個(gè)事務(wù)選擇同一行，然后基于最初選定的值更新該行時(shí)，會(huì)發(fā)生丟失更新問(wèn)題。每個(gè)事務(wù)都不知道其它事務(wù)的存在。最后的更新將重寫由其它事務(wù)所做的更新，這將導(dǎo)致數(shù)據(jù)丟失。
2、未確認(rèn)的相關(guān)性（臟讀）
當(dāng)?shù)诙€(gè)事務(wù)選擇其它事務(wù)正在更新的行時(shí)，會(huì)發(fā)生未確認(rèn)的相關(guān)性問(wèn)題。第二個(gè)事務(wù)正在讀取的數(shù)據(jù)還沒有確認(rèn)并且可能由更新此行的事務(wù)所更改。
3、不一致的分析（非重復(fù)讀）
當(dāng)?shù)诙€(gè)事務(wù)多次訪問(wèn)同一行而且每次讀取不同的數(shù)據(jù)時(shí)，會(huì)發(fā)生不一致的分析問(wèn)題。不一致的分析與未確認(rèn)的相關(guān)性類似，因?yàn)槠渌聞?wù)也是正在更改第二個(gè)事務(wù)正在讀取的數(shù)據(jù)。然而，在不一致的分析中，第二個(gè)事務(wù)讀取的數(shù)據(jù)是由已進(jìn)行了更改的事務(wù)提交的。而且，不一致的分析涉及多次（兩次或更多）讀取同一行，而且每次信息都由其它事務(wù)更改；因而該行被非重復(fù)讀取。
4、幻像讀
當(dāng)對(duì)某行執(zhí)行插入或刪除操作，而該行屬于某個(gè)事務(wù)正在讀取的行的范圍時(shí)，會(huì)發(fā)生幻像讀問(wèn)題。事務(wù)第一次讀的行范圍顯示出其中一行已不復(fù)存在于第二次讀或后續(xù)讀中，因?yàn)樵撔幸驯黄渌聞?wù)刪除。同樣，由于其它事務(wù)的插入操作，事務(wù)的第二次或后續(xù)讀顯示有一行已不存在于原始讀中。
上述四個(gè)問(wèn)題都會(huì)引起數(shù)據(jù)的不一致性。我們把事務(wù)準(zhǔn)備接受不一致數(shù)據(jù)的級(jí)別稱為隔離級(jí)別。隔離級(jí)別是一個(gè)事務(wù)必須與其它事務(wù)進(jìn)行隔離的程度。較低的隔離級(jí)別可以增加并發(fā)，但代價(jià)是降低數(shù)據(jù)的正確性。相反，較高的隔離級(jí)別可以確保數(shù)據(jù)的正確性，但可能對(duì)并發(fā)產(chǎn)生負(fù)面影響。應(yīng)用程序要求的隔離級(jí)別確定了 SQL
Server 使用的鎖定行為。
SQL-92 定義了下列四種隔離級(jí)別，SQL Server 支持所有這些隔離級(jí)別：
READ UNCOMMITTED— 未提交讀（事務(wù)隔離的最低級(jí)別，僅可保證不讀取物理?yè)p壞的數(shù)據(jù)）。
READ COMMITTED— 提交讀（SQL Server 默認(rèn)級(jí)別）。
REPEATABLE READ— 可重復(fù)讀。
SERIALIZABLE— 可串行讀（事務(wù)隔離的最高級(jí)別，事務(wù)之間完全隔離）。
下表 (1) 列出了四種隔離級(jí)別允許不同類型的行為。
隔離級(jí)別臟讀不可重復(fù)讀取幻像未提交讀是是是提交讀否是是可重復(fù)讀否否是可串行讀否否否
為了再現(xiàn)以上四類問(wèn)題，我們必須做一些準(zhǔn)備工作：
1、請(qǐng)用下面的腳本創(chuàng)建測(cè)試用的表。
-- 創(chuàng)建測(cè)試用數(shù)據(jù)庫(kù) test
CREATE DATABASE test
-- 創(chuàng)建測(cè)試用表
USE test
CREATE TABLE 帳戶表
帳號(hào) CHAR(4),
余額 INT
INSERT 帳戶表
SELECT A ,100
UNION ALL
SELECT B ,2002、請(qǐng)開啟兩個(gè)查詢分析器程序，意在開啟兩個(gè)連接，模擬兩個(gè)并行的事務(wù)。以下簡(jiǎn)稱連接一和連接二。
測(cè)試正式開始：
（1）丟失更新的再現(xiàn)
先看下面這個(gè)例子：
-- 在第一個(gè)連接中執(zhí)行以下語(yǔ)句
BEGIN TRAN
UPDATE 帳戶表 SET 余額 =101 WHERE 帳號(hào) = A
WAITFOR DELAY 00:00:10 -- 等待 10 秒
COMMIT TRAN
-- 接著馬上使用第二連接執(zhí)行下面的語(yǔ)句
BEGIN TRAN
UPDATE 帳戶表 SET 余額 =102 WHERE 帳號(hào) = A
COMMIT TRAN我們會(huì)發(fā)現(xiàn)第二個(gè)連接里面的事務(wù)不能立刻執(zhí)行，必須等待第一連接的事務(wù)完成之后才能執(zhí)行下去。
這樣就避免了“丟失更新”的問(wèn)題，否則的話就會(huì)產(chǎn)生“丟失更新”的問(wèn)題了。
丟失更新的問(wèn)題是最為嚴(yán)重的一類問(wèn)題，由表一可知，無(wú)論使用哪一種事務(wù)隔離級(jí)別，都不允許丟失更新的問(wèn)題，因此該類問(wèn)題無(wú)法再現(xiàn)。
（2）未確認(rèn)的相關(guān)性（臟讀）的再現(xiàn)
由表 1 可知，當(dāng)事務(wù)的隔離級(jí)別為未提交讀（READ UNCOMMITTED）的時(shí)候，允許臟讀。
-- 在第一個(gè)連接中執(zhí)行以下語(yǔ)句
BEGIN TRAN
UPDATE 帳戶表 SET 余額 =103 WHERE 帳號(hào) = A
WAITFOR DELAY 00:00:10 -- 等待 10 秒
UPDATE 帳戶表 SET 余額 =104 WHERE 帳號(hào) = A
COMMIT TRAN
-- 接著馬上使用第二連接執(zhí)行下面的語(yǔ)句
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
SELECT 余額 FROM 帳戶表 WHERE 帳號(hào) = A
COMMIT TRAN我們會(huì)發(fā)現(xiàn)第二個(gè)連接的語(yǔ)句會(huì)立即返回，結(jié)果是 103, 但遺憾的是它讀取的是臟數(shù)據(jù)。
如果我們把第二個(gè)連接的事務(wù)隔離級(jí)別設(shè)置為 READ COMMITTED、REPEATABLE READ 或者 SERIALIZABLE，都可以避免“臟讀”的發(fā)生。
（3）不一致的分析（非重復(fù)讀）的再現(xiàn)
由表 1 可知，當(dāng)事務(wù)的隔離級(jí)別為未提交讀（READ UNCOMMITTED）或者 READ COMMITTED 的時(shí)候，便可在現(xiàn)此問(wèn)題。
請(qǐng)測(cè)試下面這個(gè)例子（假設(shè)帳號(hào) A 的余額為 100）：
-- 在第一個(gè)連接中執(zhí)行以下語(yǔ)句
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- 或者 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
SELECT 余額 FROM 帳戶表 WHERE 帳號(hào) = A
WAITFOR DELAY 00:00:10 -- 等待 10 秒
SELECT 余額 FROM 帳戶表 WHERE 帳號(hào) = A
COMMIT TRAN
-- 接著馬上使用第二連接執(zhí)行下面的語(yǔ)句
BEGIN TRAN
UPDATE 帳戶表 SET 余額 =10 WHERE 帳號(hào) = A
COMMIT TRAN我們會(huì)發(fā)現(xiàn)第一個(gè)連接中兩次返回帳號(hào) A 的余額不一樣，第一次是 100，第二次返回的是 10，這是典型的“非重復(fù)讀”問(wèn)題。
如果把連接一的事務(wù)隔離級(jí)別設(shè)置為 REPEATABLE READ 或者 SERIALIZABLE，可防止此類問(wèn)題。
（3）不一致的分析（非重復(fù)讀）的再現(xiàn)
由表 1 可知，當(dāng)事務(wù)的隔離級(jí)別為未提交讀（READ UNCOMMITTED）或者 READ COMMITTED 的時(shí)候，便可在現(xiàn)此問(wèn)題。
先看下面這個(gè)例子（假設(shè)帳號(hào) A 的余額為 100）：
-- 在第一個(gè)連接中執(zhí)行以下語(yǔ)句
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- 或者 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
SELECT 余額 FROM 帳戶表 WHERE 帳號(hào) = A
WAITFOR DELAY 00:00:10 -- 等待 10 秒
SELECT 余額 FROM 帳戶表 WHERE 帳號(hào) = A
COMMIT TRAN
-- 接著馬上使用第二連接執(zhí)行下面的語(yǔ)句
BEGIN TRAN
UPDATE 帳戶表 SET 余額 =10 WHERE 帳號(hào) = A
COMMIT TRAN我們會(huì)發(fā)現(xiàn)第一個(gè)連接中兩次返回帳號(hào) A 的余額不一樣，第一次是 100，第二次返回的是 10，這是典型的“非重復(fù)讀”問(wèn)題。
如果把連接一的事務(wù)隔離級(jí)別設(shè)置為 REPEATABLE READ 或者 SERIALIZABLE，可防止此類問(wèn)題。
（4）幻像讀的再現(xiàn)
由表 1 可知，當(dāng)事務(wù)的隔離級(jí)別為 READ UNCOMMITTED 或者 READ COMMITTED 或者 REPEATABLE READ 的時(shí)候，便可再現(xiàn)此問(wèn)題。
先看下面這個(gè)例子（假設(shè)帳號(hào) A 的余額為 100）：
-- 在第一個(gè)連接中執(zhí)行以下語(yǔ)句
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- 或者 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 或者 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM 帳戶表
WAITFOR DELAY 00:00:10 -- 等待 10 秒
SELECT * FROM 帳戶表
COMMIT TRAN
-- 接著馬上使用第二連接執(zhí)行下面的語(yǔ)句
BEGIN TRAN
INSERT INTO 帳戶表 VALUES(C , 300)
COMMIT TRAN我們會(huì)發(fā)現(xiàn)第一個(gè)連接中在同一個(gè)事務(wù)中，同樣的查詢語(yǔ)句兩次返回的結(jié)果集不一樣，第二次返回的結(jié)果集中多了一條帳號(hào)為 C 的帳號(hào)，這是典型的“幻像讀”問(wèn)題。只有將連接一的事務(wù)隔離級(jí)別設(shè)置為 SERIALIZABLE，才可防止此類問(wèn)題。
總結(jié)：為了避免事務(wù)并發(fā)帶來(lái)的問(wèn)題，可采用較高的事務(wù)隔離級(jí)別，但因此會(huì)降低事務(wù)的并行性；反過(guò)來(lái)如果追求高的并行性而使用較低的事務(wù)隔離級(jí)別，又容易帶來(lái)并發(fā)的問(wèn)題。因此 SQL Server 采用默認(rèn)隔離級(jí)別是相對(duì)比較低的“READ COMMITTED”。在實(shí)際應(yīng)用的時(shí)候，采用何種隔離級(jí)別視具體情況而定，也可以采用顯式“上鎖”的方法控制事務(wù)隔離級(jí)別，具體方法請(qǐng)留意筆者的相關(guān)文章。
關(guān)于“SQL Server 中四類事務(wù)并發(fā)問(wèn)題的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。
