SQL Server 2000＋ADO.NET實現並發控制.

 

1 並發一致性問題

常見並發並發一致性問題包括：丟失的修改、不可重複讀取、讀髒資料、幻影讀（幻影讀在一些資料中往往與不可重複讀取歸為一類）。

1.1 丟失修改

下面我們先來看一個例子，說明並行作業帶來的資料的不一致性問題。

考慮飛機訂票系統中的一個活動序列:

1. 甲售票點（甲事務）讀出某航班的機票餘額A,設A=16.
2. 乙售票點（乙事務）讀出同一航班的機票餘額A,也為16.
3. 甲售票點賣出一張機票,修改餘額A←A-1.所以A為15,把A寫回資料庫.
4. 乙售票點也賣出一張機票,修改餘額A←A-1.所以A為15,把A寫回資料庫.

結果明明賣出兩張機票，資料庫中機票餘額只減少1。

歸納起來就是：兩個事務T1和T2讀入同一資料並修改，T2提交的結果破壞了T1提交的結果，導致T1的修改被丟失。前文（2.1.4資料刪除與更新）中提到的問題及解決辦法往往是針對此類並發問題的。但仍然有幾類問題通過上面的方法解決不了，那就是：

1.2 不可重複讀取

不可重複讀取是指事務T1讀取資料後，事務T2執行更新操作，使T1無法再現前一次讀取結果。具體地講，不可重複讀取包括三種情況：

• 事務T1讀取某一資料後，事務T2對其做了修改，當事務1再次讀該資料時，得到與前一次不同的值。例如，T1讀取B=100進行運算，T2讀取同一資料B，對其進行修改後將B=200寫回資料庫。T1為了對讀取值校對重讀B，B已為200，與第一次讀取值不一致。
• 事務T1按一定條件從資料庫中讀取了某些資料記錄後，事務T2刪除了其中部分記錄，當T1再次按相同條件讀取資料時，發現某些記錄神密地消失了。
• 事務T1按一定條件從資料庫中讀取某些資料記錄後，事務T2插入了一些記錄，當T1再次按相同條件讀取資料時，發現多了一些記錄。（這也叫做幻影讀）

1.3 讀"髒"資料

讀"髒"資料是指事務T1修改某一資料，並將其寫回磁碟，事務T2讀取同一資料後，T1由於某種原因被撤消，這時T1已修改過的資料恢複原值，T2讀到的資料就與資料庫中的資料不一致，則T2讀到的資料就為"髒"資料，即不正確的資料。

產生上述三類資料不一致性的主要原因是並行作業破壞了事務的隔離性。並發控制就是要用正確的方式調度並行作業，使一個使用者事務的執行不受其它事務的幹擾，從而避免造成資料的不一致性。

2 並發一致性問題的解決辦法2.2.2.1 封鎖（Locking）

封鎖是實現並發控制的一個非常重要的技術。所謂封鎖就是事務T在對某個資料對象例如表、記錄等操作之前，先向系統發出請求，對其加鎖。加鎖後事務T就對該資料對象有了一定的控制，在事務T釋放它的鎖之前，其它的事務不能更新此資料對象。

基本的封鎖類型有兩種：排它鎖（Exclusive locks 簡記為X鎖）和共用鎖定（Share locks 簡記為S鎖）。

排它鎖又稱為寫鎖。若事務T對資料對象A加上X鎖，則只允許T讀取和修改A，其它任何事務都不能再對A加任何類型的鎖，直到T釋放A上的鎖。這就保證了其它事務在T釋放A上的鎖之前不能再讀取和修改A。

共用鎖定又稱為讀鎖。若事務T對資料對象A加上S鎖，則其它事務只能再對A加S鎖，而不能加X鎖，直到T釋放A上的S鎖。這就保證了其它事務可以讀A，但在T釋放A上的S鎖之前不能對A做任何修改。

2.2.2.2 封鎖協議

在運用X鎖和S鎖這兩種基本封鎖，對資料對象加鎖時，還需要約定一些規則，例如應何時申請X鎖或S鎖、持鎖時間、何時釋放等。我們稱這些規則為封鎖協議（Locking Protocol）。對封鎖方式規定不同的規則，就形成了各種不同的封鎖協議。下面介紹三級封鎖協議。三級封鎖協議分別在不同程度上解決了丟失的修改、不可重複讀取和讀"髒"資料等不一致性問題，為並行作業的正確調度提供一定的保證。下面只給出三級封鎖協議的定義，不再做過多探討。

• 1級封鎖協議

1級封鎖協議是：事務T在修改資料R之前必須先對其加X鎖，直到事務結束才釋放。事務結束包括正常結束（COMMIT）和非正常結束（ROLLBACK）。1級封鎖協議可防止丟失修改，並保證事務T是可恢複的。在1級封鎖協議中，如果僅僅是讀資料不對其進行修改，是不需要加鎖的，所以它不能保證可重複讀和不讀"髒"資料。

• 2級封鎖協議

2級封鎖協議是：1級封鎖協議加上事務T在讀取資料R之前必須先對其加S鎖，讀完後即可釋放S鎖。2級封鎖協議除防止了丟失修改，還可進一步防止讀"髒"資料。

• 3級封鎖協議

3級封鎖協議是：1級封鎖協議加上事務T在讀取資料R之前必須先對其加S鎖，直到事務結束才釋放。3級封鎖協議除防止了丟失修改和不讀'髒'資料外，還進一步防止了不可重複讀取。

2.3 交易隔離等級

儘管資料庫理論對並發一致性問題提供了完善的解決機制，但讓程式員自己去控制如何加鎖以及加鎖、解鎖的時機顯然是很困難的事情。索性絕大多數資料庫以及開發工具都提供了交易隔離等級，讓使用者以一種更輕鬆的方式處理並發一致性問題。常見的交易隔離等級包括：ReadUnCommitted、ReadCommitted、RepeatableRead和Serializable四種。不同的隔離等級下對資料庫的訪問方式以及資料庫的返回結果有可能是不同的。我們將通過幾個實驗深入瞭解交易隔離等級以及SQL Server在後台是如何將它們轉換成鎖的。

2.3.1 ReadUnCommitted與ReadCommitted

ReadUnCommitted是最低的隔離等級，這個層級的隔離允許讀入別人尚未提交的髒資料，除此之外，在這種交易隔離等級下還存在不可重複讀取的問題。

ReadCommitted是許多資料庫的預設層級，這個隔離等級上，不會出現讀取未提交的資料問題，但仍然無法避免不可重複讀取（包括幻影讀）的問題。當你的系統對並發控制的要求非常嚴格時，這種預設的隔離等級可能無法提供資料有效保護，但對於決大多數應用來講，這種隔離等級就夠用了。

我們使用下面的實驗來進行測試：

首先配置SQL Server 2000資料庫，附加DBApp資料庫。然後在Visual Studio .net中建立一管理主控台應用程式，添加必要的命名空間引用：

using System;using System.Data;using System.Data.SqlClient;using System.Configuration;

然後建立兩個資料庫連結，並分別採用不同的交易隔離等級：

   private static SqlConnection conn1;private static SqlConnection conn2;private static SqlTransaction tx1;private static SqlTransaction tx2;private static void Setup(){conn1 = new SqlConnection(connectionString);conn1.Open();tx1 = conn1.BeginTransaction(IsolationLevel.ReadUncommitted);conn2 = new SqlConnection(connectionString);conn2.Open();tx2 = conn2.BeginTransaction(IsolationLevel.ReadCommitted);}

其中事務1允許讀入未提交的資料，而事務2隻允許讀入已提交資料。

在主程式中，我們類比兩個人先後的不同操作，以產生並發一致性問題：

   public static void Main(){Setup();try{ReadUnCommittedDataByTransaction1();UnCommittedUpdateByTransaction2();ReadUnCommittedDataByTransaction1();tx2.Rollback();Console.WriteLine("\n-- Transaction 2 rollbacked!\n");ReadUnCommittedDataByTransaction1();tx1.Rollback();}catch{……}}

第一步，使用ReadUnCommittedDataByTransaction1方法利用事務1從資料庫中讀入id值為1的學生資訊。此時的資訊是資料庫的初始資訊。

第二步，調用UnCommittedUpdateByTransaction2方法，從第2個事務中發送一UPDATE命令更新資料庫，但尚未提交。

第三步，再次調用ReadUnCommittedDataByTransaction1，從事務1中讀取資料庫資料，你會發現由事務2發布的尚未提交的更新被事務1讀取出來（ReadUnCommitted）。

第四步，事務2放棄提交，復原事務tx2.Rollback();。

第五步，再次調用ReadUnCommittedDataByTransaction1();，讀取資料庫中的資料，此次是已經復原後的資料。

程式運行結果如下：

-- Read age from database:Age:20-- Run an uncommitted command:UPDATE student SET age=30 WHERE id=1-- Read age from database:Age:30-- Transaction 2 rollbacked!-- Read age from database:Age:20

關於ReadUnCommittedDataByTransaction1()與UnCommittedUpdateByTransaction2()的方法定義如下：

   private static void UnCommittedUpdateByTransaction2(){string command = "UPDATE student SET age=30 WHERE id=1";Console.WriteLine("\n-- Run an uncommitted command:\n{0}\n", command);SqlCommand cmd = new SqlCommand(command, conn2);cmd.Transaction = tx2;cmd.ExecuteNonQuery();}private static void ReadUnCommittedDataByTransaction1(){Console.WriteLine("-- Read age from database:");SqlCommand cmd = new SqlCommand("SELECT age FROM student WHERE id = 1", conn1);cmd.Transaction = tx1;try{int age = (int)cmd.ExecuteScalar();Console.WriteLine("Age:{0}", age);}catch(SqlException e){Console.WriteLine(e.Message);}}

從上面的實驗可以看出，在ReadUnCommitted隔離等級下，程式可能讀入未提交的資料，但此隔離等級對資料庫資源鎖定最少。

本實驗的完整代碼可以從"SampleCode\Chapter 2\Lab 2-6"下找到。

讓我們再來做一個實驗（這個實驗要求動作要快的，否則可能看不到預期效果）。首先修改上面代碼中的Setup()方法代碼，將

tx1 = conn1.BeginTransaction(IsolationLevel.ReadUncommitted);

改為：

tx1 = conn1.BeginTransaction(IsolationLevel.ReadCommitted);

再次運行代碼，你會發現程式執行到第三步就不動了，如果你有足夠的耐心等下去的話，你會看到"逾時時間已到。在操作完成之前逾時時間已過或伺服器未響應。"的一條提示，這條提示究竟是什麼意思呢？讓我們探察一下究竟發生了什麼：

第一步，在做這個實驗之前，先將SQL Server 2000的企業管理器開啟，然後再將SQL Server事件探察器開啟並處於探察狀態。

第二步，運行改動後的程式，程式執行到一半就暫停了。此時迅速切換到企業管理器介面，右擊"管理"下面的"當前活動"，選擇"重新整理"（整個過程應在大約15秒內完成即可， 2-8所示），我們便得到了資料庫當前進程的一個快照。

圖 2-8 使用企業管理器查看當前活動

我們發現此時進程出現了阻塞，被阻塞者是52號進程，而阻塞者是53號進程。也就是說53號進程的工作妨礙了52號進程繼續工作。（不同實驗時進程號可能各不相同）

第三步，為了進一步查明原因真相，我們切換到事件探察器視窗，看看這兩個進程都是幹什麼的。 2-9所示，事件探察器顯示了這兩個進程的詳細資料。我們可以看出，52號進程對應我們的事務1，53號進程對應我們的事務2。事務2執行了UPDATE命令，但尚未提交，此時事務1去讀尚未提交的資料便被阻塞住。我們可以看出52號進程是被阻塞者。

此時如果事務2完成提交，52號進程便可以停止等待，得到需要的結果。然而我們的程式沒有提交資料，因此52號進程就要無限等下去。所幸SQL Server 2000檢測到事務2的已耗用時間過長（這就是上面的錯誤提示"逾時時間已到。在操作完成之前逾時時間已過或伺服器未響應。"），所以將事務2復原以釋放佔用的資源。資源被釋放後，52號進程便得以執行。

圖 2-9 事件探察器探察阻塞命令

第四步，瞭解了上面發生的事情後，我們現在可以深入討論一下共用鎖定和排它鎖的使用方式了。重新回到企業管理器介面，讓我們查看一下兩個進程各佔用了什麼資源。從圖 2-10中我們可以看出，53號進程（事務2）在執行更新命令前對相應的鍵加上了排它鎖（X鎖），按照前文提到的1級封鎖協議，該排它鎖只有在事務2提交或復原後才釋放。現在52號進程（事務1）要去讀同一行資料，按照2級封鎖協議，它要首先對該行加共用鎖定，然而 該行資料已經被事務2加上了排它鎖，因此事務1隻能處於等待狀態，等待排它鎖被釋放。因此我們就看到了前面的"阻塞"問題。

圖 2-10 進程執行寫操作前首先加了排它鎖

圖 2-11 進程讀操作前要加共用鎖定，但被阻塞

當事務1的交易隔離等級是ReadUnCommitted時，讀資料是不加鎖的，因此排它鎖對ReadUnCommitted不起作用，進程也不會被阻塞，不過確讀到了"髒"資料。

2.3.2 RepeatableRead

RepeatableRead是指可重複讀，它的隔離等級要比ReadCommitted層級高。它允許某事務執行重複讀時資料保持不變，但是仍然無法解決幻影讀的問題。為了更深入的瞭解RepeatableRead所能解決的問題，我們還是使用下面的實驗來加以印證：

第一步，事務1與事務2同時設定為ReadCommitted，並同時開啟事務。

private static void Setup(){conn1 = new SqlConnection(connectionString);conn1.Open();tx1 = conn1.BeginTransaction(IsolationLevel.ReadCommitted);conn2 = new SqlConnection(connectionString);conn2.Open();tx2 = conn2.BeginTransaction(IsolationLevel.ReadCommitted);}

第二步，事務1讀取資料庫中資料。注意此時並沒有通過提交或復原的方式結束事務1，事務1仍然處於活動狀態。

private static int ReadAgeByTransaction1(){return (int)ExecuteScalar("SELECT age FROM student WHERE (id = 1)");}private static object ExecuteScalar(string command){Console.WriteLine("-- Execute command: {0}", command);SqlCommand cmd = new SqlCommand(command, conn1);cmd.Transaction = tx1;return cmd.ExecuteScalar();}

第三步，事務2修改年齡資料並提交修改。

private static void ModifyAgeByTransaction2(){string command = "UPDATE student SET age=30 WHERE id=1";Console.WriteLine("-- Modify age by transaction2, command:{0}", command);SqlCommand cmd = new SqlCommand(command, conn2);cmd.Transaction = tx2;try{cmd.ExecuteNonQuery();tx2.Commit();}catch(Exception e){Console.WriteLine(e.Message);tx2.Rollback();}}

第四步，事務1重複讀取年齡資料，此時會發現讀取出來的資料是修改過的資料，與上次讀取的資料不一樣了！顧名思義，不可重複讀取。主程式碼如下：

public static void Main(){Setup();try{int age1 = ReadAgeByTransaction1();ModifyAgeByTransaction2();int age2 = ReadAgeByTransaction1();Console.WriteLine("\nFirst Read: age={0}\nSecond Read: age={1}", age1, age2);}catch(Exception e){Console.WriteLine("Got an error! " + e.Message);}finally{CleanUp();}}

程式的運行結果如下：

-- Execute command: SELECT age FROM student WHERE (id = 1)-- Modify age by transaction2, command:UPDATE student SET age=30 WHERE id=1-- Execute command: SELECT age FROM student WHERE (id = 1)First Read: age=20Second Read: age=30

之所以出現了重複讀時讀取的資料與第一次讀取的不一樣，是因為事務1被設定成了ReadCommitted隔離類型，該隔離等級無法防止不可重複讀取的問題。要想在一個事務中兩次讀取資料完全相同就必須使用RepeatableRead交易隔離等級。

讓我們修改上面的Setup()方法中的代碼，將事務1的隔離等級設定為RepeatableRead：

tx1 = conn1.BeginTransaction(IsolationLevel.RepeatableRead);

再次運行該程式，你會發現程式執行到第二步就暫停了，如果等待一段時間後你就會看到"逾時時間已到。在操作完成之前逾時時間已過或伺服器未響應。"的錯誤提示，此時，重複讀的資料確和第一次讀完全一樣。程式執行結果如下：

-- Execute command: SELECT age FROM student WHERE (id = 1)-- Modify age by transaction2, command:UPDATE student SET age=30 WHERE id=1逾時時間已到。在操作完成之前逾時時間已過或伺服器未響應。-- Execute command: SELECT age FROM student WHERE (id = 1)First Read: age=20Second Read: age=20

為了探明原因，還是象上一個案例一樣，再次執行該程式，當出現暫停時迅速切換到企業管理器中查看當前活動的快照，並檢查阻塞進程中資料鎖定情況，你會發現 2-12和圖 2-13所示的內容：

圖 2-12 RepeatableRead在讀資料時加S鎖，直到事務結束才釋放

圖 2-13 修改資料要求加X鎖，但被阻塞

根據3級封鎖協議，事務T在讀取資料之前必須先對其加S鎖，直到事務結束才釋放。因此，事務1在第一次讀取資料時便對資料加上了共用鎖定，第一次資料讀取完成後事務並未結束，因此該共用鎖定並不會被釋放，此時事務2試圖修改該資料，按照2級封鎖協議，在寫之前要加排它鎖，但資料上的共用鎖定尚未被釋放，導致事務2不得不處於等待狀態。當事務2等待時間逾時後，SQL Server就強制將該交易回復。儘管事務2執行失敗，但保證了事務1實現了可重複讀層級的事務隔離。

RepeatableRead交易隔離等級允許事務內的重複讀操作，但是這並不能避免出現幻影讀的問題，如果您的程式中存在幻影讀的潛在問題的話，就必須採用最高的交易隔離等級：Serializable。

2.3.3 Serializable

Serializable隔離等級是最高的交易隔離等級，在此隔離等級下，不會出現讀髒資料、不可重複讀取和幻影讀的問題。在詳細說明為什麼之前首先讓我們看看什麼是幻影讀。

所謂幻影讀是指：事務1按一定條件從資料庫中讀取某些資料記錄後，事務2插入了一些符合事務1檢索條件的新記錄，當事務1再次按相同條件讀取資料時，發現多了一些記錄。讓我們通過以下案例來重現幻影讀的問題：

第一步，將事務1和事務2均設為RepeatableRead隔離等級，並同時開啟事務。

private static void Setup(){conn1 = new SqlConnection(connectionString);conn1.Open();tx1 = conn1.BeginTransaction(IsolationLevel.RepeatableRead);conn2 = new SqlConnection(connectionString);conn2.Open();tx2 = conn2.BeginTransaction(IsolationLevel.RepeatableRead);}

第二步，事務1讀取學號為1的學生的平均成績以及所學課程的門數。此時讀到學生1學了3門課程，平均成績為73.67。注意，此時事務1並未提交。

private static double ReadAverageMarksByTransaction1(){return (double)ExecuteScalar("SELECT AVG(mark) AS AvgMark FROM SC WHERE (id = 1)");}private static int ReadTotalCoursesByTransaction1(){return (int)ExecuteScalar("SELECT COUNT(*) AS num FROM SC WHERE (id = 1)");}private static object ExecuteScalar(string command){Console.WriteLine("-- Execute command: {0}", command);SqlCommand cmd = new SqlCommand(command, conn1);cmd.Transaction = tx1;return cmd.ExecuteScalar();}

第三步，事務2向資料庫插入一條新記錄，讓學號為1的同學再學1門課程，成績是80。然後提交修改到資料庫。

private static void InsertRecordByTransaction2(){string command = "INSERT INTO SC VALUES(1, 5, 80)";Console.WriteLine("-- Insert to table SC by transaction 2");Console.WriteLine("-- Command:{0}\n", command);SqlCommand cmd = new SqlCommand(command, conn2);cmd.Transaction = tx2;try{cmd.ExecuteNonQuery();tx2.Commit();}catch(Exception e){Console.WriteLine(e.Message);tx2.Rollback();}}

第四步，事務1再次讀取學號為1的學生的平均成績以及所學課程的門數。此時讀到確是4門課程，平均成績為75.25。與第一次讀取的不一樣！居然多出了一門課程，多出的這門課程就像幻影一樣出現在我們的面前。測試用主程式如下：

public static void Main(){Setup();try{Console.WriteLine(">>>> Step 1");double avg = ReadAverageMarksByTransaction1();int total = ReadTotalCoursesByTransaction1();Console.WriteLine("avg={0,5:F2}, total={1}\n", avg, total);Console.WriteLine(">>>> Step 2");InsertRecordByTransaction2();Console.WriteLine(">>>> Step 3");avg = ReadAverageMarksByTransaction1();total = ReadTotalCoursesByTransaction1();Console.WriteLine("avg={0,5:F2}, total={1}\n", avg, total);}catch(Exception e){Console.WriteLine("Got an error! " + e.Message);}finally{CleanUp();}}

程式執行結果如下：

>>>> Step 1-- Execute command: SELECT AVG(mark) AS AvgMark FROM SC WHERE (id = 1)-- Execute command: SELECT COUNT(*) AS num FROM SC WHERE (id = 1)avg=73.67, total=3>>>> Step 2-- Insert to table SC by transaction 2-- Command:INSERT INTO SC VALUES(1, 5, 80)>>>> Step 3-- Execute command: SELECT AVG(mark) AS AvgMark FROM SC WHERE (id = 1)-- Execute command: SELECT COUNT(*) AS num FROM SC WHERE (id = 1)avg=75.25, total=4

大家可以思考一下，為什麼RepeatableRead隔離模式並不能使得兩次讀取的平均值一樣呢？（可以從鎖的角度來解釋這一現象）。

仍然象前面的做法一樣，我們看看究竟發生了什麼事情。在探察之前，先將Setup方法中事務1的隔離等級設定為Serializable，再次運行程式，當發現程式運行暫停時，查看資料庫當前活動快照，你會發現 2-14和圖 2-15所示的鎖定問題：

圖 2-14 Serializable隔離模式對符合檢索條件的資料添加了RangeS-S鎖

圖 2-15 當試圖插入符合RangeIn條件的記錄時，只能處於等待狀態

我們可以看出，在Serializalbe隔離模式下，資料庫在檢索資料時，對所有滿足檢索條件的記錄均加上了RangeS-S共用鎖定。事務2試圖去插入一滿足RangeIn條件的記錄時，必須等待這些RangS-S鎖釋放，否則就只能處於等待狀態。在等待逾時後，事務2就會被SQL Server強制復原。

修改後的程式運行結果如下：

>>>> Step 1-- Execute command: SELECT AVG(mark) AS AvgMark FROM SC WHERE (id = 1)-- Execute command: SELECT COUNT(*) AS num FROM SC WHERE (id = 1)avg=73.67, total=3>>>> Step 2-- Insert to table SC by transaction 2-- Command:INSERT INTO SC VALUES(1, 5, 80)逾時時間已到。在操作完成之前逾時時間已過或伺服器未響應。>>>> Step 3-- Execute command: SELECT AVG(mark) AS AvgMark FROM SC WHERE (id = 1)-- Execute command: SELECT COUNT(*) AS num FROM SC WHERE (id = 1)avg=73.67, total=3

事務2的運行失敗確保了事務1不會出現幻影讀的問題。這裡應當注意的是，1、2、3級封鎖協議都不能保證有效解決幻影讀的問題。

2.3 建議

通過上面的幾個例子，我們更深入的瞭解了資料庫在解決並發一致性問題時所採取的措施。鎖機制屬於最底層的保證機制，但很難直接使用。我們可以通過不同的事務隔離模式來間接利用鎖定機制確保我們資料的完整一致性。在使用不同層級的隔離模式時，我們也應當注意以下一些問題：

• 一般情況下ReadCommitted隔離等級就足夠了。過高的隔離等級將會鎖定過多的資源，影響資料的共用效率。
• 你所選擇的隔離等級依賴於你的系統和商務邏輯。
• 盡量避免直接使用鎖，除非在萬不得已的情況下。
• 我們可以通過控制WHERE短語中的欄位實現不同的更新策略，防止出現丟失的修改問題。但不必要的更新策略可能造成SQL命令執行效率低下。所以要慎用時間戳記和過多的保護欄位作為更新依據。