SQL Server中四類事務並發問題的執行個體再現)

 

朱二（2006.3 轉載請註明作者）

　　本篇文章將用執行個體再現資料庫訪問中四類並發問題，希望能讓初學者能對事務的並行性有進一步的理解。
　　首先，讓我們先來瞭解一下並行問題以及交易隔離等級這兩個概念。
在資料庫中，假設如果沒有鎖定且多個使用者同時訪問一個資料庫，則當他們的事務同時使用相同的資料時可能會發生問題。並發問題包括： 

• 丟失或覆蓋更新。
• 未確認的相關性（髒讀）。
• 不一致的分析（非重複讀）。
• 幻像讀。 

下面讓我們稍花點時間來解釋一下這四類問題：
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”。在實際應用的時候，採用何種隔離等級視具體情況而定，也可以採用顯式“上鎖”的方法控制交易隔離等級，具體方法請留意筆者的相關文章。