SQL Server中四類事務并發問題的示例分析

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

SQL Server中四類事務并發問題的實例再現

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

• 丟失或覆蓋更新。

• 未確認的相關性（臟讀）。

• 不一致的分析（非重復讀）。

• 幻像讀。 

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


2、未確認的相關性（臟讀）
當第二個事務選擇其它事務正在更新的行時，會發生未確認的相關性問題。第二個事務正在讀取的數據還沒有確認并且可能由更新此行的事務所更改。

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

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

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

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

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

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

• REPEATABLE READ---可重復讀。

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

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

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

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

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

先看下面這個例子：

--在第一個連接中執行以下語句

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

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

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

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

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

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

--在第一個連接中執行以下語句

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

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

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

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

--在第一個連接中執行以下語句

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

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

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


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

--在第一個連接中執行以下語句

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

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

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

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

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

--在第一個連接中執行以下語句

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

--接著馬上使用第二連接執行下面的語句
BEGIN TRAN
INSERT INTO 帳戶表 VALUES('C','300')
COMMIT TRAN

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

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