SQL Server中四類事務(wù)并發(fā)問題的示例分析

這篇文章將為大家詳細講解有關(guān)SQL Server中四類事務(wù)并發(fā)問題的示例分析，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

SQL Server中四類事務(wù)并發(fā)問題的實例再現(xiàn)

　　首先，讓我們先來了解一下并行問題以及事務(wù)隔離級別這兩個概念。
在數(shù)據(jù)庫中，假設(shè)如果沒有鎖定且多個用戶同時訪問一個數(shù)據(jù)庫，則當他們的事務(wù)同時使用相同的數(shù)據(jù)時可能會發(fā)生問題。并發(fā)問題包括： 

• 丟失或覆蓋更新。

• 未確認的相關(guān)性（臟讀）。

• 不一致的分析（非重復(fù)讀）。

• 幻像讀。 

下面讓我們稍花點時間來解釋一下這四類問題：
1、丟失更新
當兩個或多個事務(wù)選擇同一行，然后基于最初選定的值更新該行時，會發(fā)生丟失更新問題。每個事務(wù)都不知道其它事務(wù)的存在。最后的更新將重寫由其它事務(wù)所做的更新，這將導(dǎo)致數(shù)據(jù)丟失。


2、未確認的相關(guān)性（臟讀）
當?shù)诙€事務(wù)選擇其它事務(wù)正在更新的行時，會發(fā)生未確認的相關(guān)性問題。第二個事務(wù)正在讀取的數(shù)據(jù)還沒有確認并且可能由更新此行的事務(wù)所更改。

3、不一致的分析（非重復(fù)讀）
當?shù)诙€事務(wù)多次訪問同一行而且每次讀取不同的數(shù)據(jù)時，會發(fā)生不一致的分析問題。不一致的分析與未確認的相關(guān)性類似，因為其它事務(wù)也是正在更改第二個事務(wù)正在讀取的數(shù)據(jù)。然而，在不一致的分析中，第二個事務(wù)讀取的數(shù)據(jù)是由已進行了更改的事務(wù)提交的。而且，不一致的分析涉及多次（兩次或更多）讀取同一行，而且每次信息都由其它事務(wù)更改；因而該行被非重復(fù)讀取。

4、幻像讀
當對某行執(zhí)行插入或刪除操作，而該行屬于某個事務(wù)正在讀取的行的范圍時，會發(fā)生幻像讀問題。事務(wù)第一次讀的行范圍顯示出其中一行已不復(fù)存在于第二次讀或后續(xù)讀中，因為該行已被其它事務(wù)刪除。同樣，由于其它事務(wù)的插入操作，事務(wù)的第二次或后續(xù)讀顯示有一行已不存在于原始讀中。

　　上述四個問題都會引起數(shù)據(jù)的不一致性。我們把事務(wù)準備接受不一致數(shù)據(jù)的級別稱為隔離級別。隔離級別是一個事務(wù)必須與其它事務(wù)進行隔離的程度。較低的隔離級別可以增加并發(fā)，但代價是降低數(shù)據(jù)的正確性。相反，較高的隔離級別可以確保數(shù)據(jù)的正確性，但可能對并發(fā)產(chǎn)生負面影響。應(yīng)用程序要求的隔離級別確定了 SQL
Server 使用的鎖定行為。

　　SQL-92 定義了下列四種隔離級別，SQL Server 支持所有這些隔離級別：

• READ UNCOMMITTED---未提交讀（事務(wù)隔離的最低級別，僅可保證不讀取物理損壞的數(shù)據(jù)）。

• READ COMMITTED---提交讀（SQL Server 默認級別）。

• REPEATABLE READ---可重復(fù)讀。

• SERIALIZABLE---可串行讀（事務(wù)隔離的最高級別，事務(wù)之間完全隔離）。

下表(1)列出了四種隔離級別允許不同類型的行為。

為了再現(xiàn)以上四類問題，我們必須做一些準備工作：
1、請用下面的腳本創(chuàng)建測試用的表。

--創(chuàng)建測試用數(shù)據(jù)庫test
CREATE DATABASE test
GO
--創(chuàng)建測試用表
USE test
GO
CREATE TABLE 帳戶表
(
帳號 CHAR(4),
余額 INT
)
GO
INSERT 帳戶表
SELECT 'A',100
UNION ALL
SELECT 'B',200

2、請開啟兩個查詢分析器程序，意在開啟兩個連接，模擬兩個并行的事務(wù)。以下簡稱連接一和連接二。
測試正式開始：
（1）丟失更新的再現(xiàn)

先看下面這個例子：

--在第一個連接中執(zhí)行以下語句

BEGIN TRAN
UPDATE 帳戶表 SET 余額=101 WHERE 帳號='A' 
WAITFOR DELAY '00:00:10' --等待10秒
COMMIT TRAN

--接著馬上使用第二連接執(zhí)行下面的語句

BEGIN TRAN
UPDATE 帳戶表 SET 余額=102 WHERE 帳號='A' 
COMMIT TRAN

我們會發(fā)現(xiàn)第二個連接里面的事務(wù)不能立刻執(zhí)行，必須等待第一連接的事務(wù)完成之后才能執(zhí)行下去。
這樣就避免了“丟失更新”的問題，否則的話就會產(chǎn)生“丟失更新”的問題了。

丟失更新的問題是最為嚴重的一類問題，由表一可知，無論使用哪一種事務(wù)隔離級別，都不允許丟失更新的問題，因此該類問題無法再現(xiàn)。

（2）未確認的相關(guān)性（臟讀）的再現(xiàn)
由表1可知，當事務(wù)的隔離級別為未提交讀（READ UNCOMMITTED）的時候，允許臟讀。

--在第一個連接中執(zhí)行以下語句

BEGIN TRAN
UPDATE 帳戶表 SET 余額=103 WHERE 帳號='A' 
WAITFOR DELAY '00:00:10' --等待10秒
UPDATE 帳戶表 SET 余額=104 WHERE 帳號='A'
COMMIT TRAN

--接著馬上使用第二連接執(zhí)行下面的語句
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
SELECT 余額 FROM 帳戶表 WHERE 帳號='A' 
COMMIT TRAN

我們會發(fā)現(xiàn)第二個連接的語句會立即返回，結(jié)果是103,但遺憾的是它讀取的是臟數(shù)據(jù)。
如果我們把第二個連接的事務(wù)隔離級別設(shè)置為 READ COMMITTED、REPEATABLE READ 或者SERIALIZABLE，都可以避免“臟讀”的發(fā)生。

（3）不一致的分析（非重復(fù)讀）的再現(xiàn)
由表1可知，當事務(wù)的隔離級別為未提交讀（READ UNCOMMITTED）或者READ COMMITTED的時候，便可在現(xiàn)此問題。
請測試下面這個例子（假設(shè)帳號A的余額為100）：

--在第一個連接中執(zhí)行以下語句

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--或者 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
SELECT 余額 FROM 帳戶表 WHERE 帳號='A'
WAITFOR DELAY '00:00:10' --等待10秒
SELECT 余額 FROM 帳戶表 WHERE 帳號='A'
COMMIT TRAN

--接著馬上使用第二連接執(zhí)行下面的語句
BEGIN TRAN
UPDATE 帳戶表 SET 余額=10 WHERE 帳號='A'
COMMIT TRAN

我們會發(fā)現(xiàn)第一個連接中兩次返回帳號A的余額不一樣，第一次是100，第二次返回的是10，這是典型的“非重復(fù)讀”問題。
如果把連接一的事務(wù)隔離級別設(shè)置為REPEATABLE READ 或者SERIALIZABLE，可防止此類問題。


（3）不一致的分析（非重復(fù)讀）的再現(xiàn)
由表1可知，當事務(wù)的隔離級別為未提交讀（READ UNCOMMITTED）或者READ COMMITTED的時候，便可在現(xiàn)此問題。
先看下面這個例子（假設(shè)帳號A的余額為100）：

--在第一個連接中執(zhí)行以下語句

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--或者 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
SELECT 余額 FROM 帳戶表 WHERE 帳號='A'
WAITFOR DELAY '00:00:10' --等待10秒
SELECT 余額 FROM 帳戶表 WHERE 帳號='A'
COMMIT TRAN

--接著馬上使用第二連接執(zhí)行下面的語句
BEGIN TRAN
UPDATE 帳戶表 SET 余額=10 WHERE 帳號='A'
COMMIT TRAN

我們會發(fā)現(xiàn)第一個連接中兩次返回帳號A的余額不一樣，第一次是100，第二次返回的是10，這是典型的“非重復(fù)讀”問題。

如果把連接一的事務(wù)隔離級別設(shè)置為REPEATABLE READ 或者SERIALIZABLE，可防止此類問題。

（4）幻像讀的再現(xiàn)
由表1可知，當事務(wù)的隔離級別為READ UNCOMMITTED或者READ COMMITTED或者REPEATABLE READ的時候，便可再現(xiàn)此問題。
先看下面這個例子（假設(shè)帳號A的余額為100）：

--在第一個連接中執(zhí)行以下語句

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í)行下面的語句
BEGIN TRAN
INSERT INTO 帳戶表 VALUES('C','300')
COMMIT TRAN

我們會發(fā)現(xiàn)第一個連接中在同一個事務(wù)中，同樣的查詢語句兩次返回的結(jié)果集不一樣，第二次返回的結(jié)果集中多了一條帳號為C的帳號，這是典型的“幻像讀”問題。只有將連接一的事務(wù)隔離級別設(shè)置為SERIALIZABLE，才可防止此類問題。
　　總結(jié)：為了避免事務(wù)并發(fā)帶來的問題，可采用較高的事務(wù)隔離級別，但因此會降低事務(wù)的并行性；反過來如果追求高的并行性而使用較低的事務(wù)隔離級別，又容易帶來并發(fā)的問題。因此SQL Server采用默認隔離級別是相對比較低的“READ COMMITTED”。在實際應(yīng)用的時候，采用何種隔離級別視具體情況而定，也可以采用顯式“上鎖”的方法控制事務(wù)隔離級別，具體方法請留意筆者的相關(guān)文章。

關(guān)于“SQL Server中四類事務(wù)并發(fā)問題的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學(xué)到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。