|
本部分內容為《數據庫原理》課程中的一個課堂案例，幻燈片提供的動畫演示有助于理解并發(fā)控制的本質，本文內容為幻燈片的摘要。 1、下載本文所對應的幻燈片； 2、下載本文對應的VS2005代碼。 如果你對自己并發(fā)控制的能力很有自信的話，讀完“一、問題提出”后直接可以跳轉到“四、看來問題真不簡單”處閱讀。 本文最后給出了部分測試用代碼的簡單講解。
一、問題提出設某銀行存款帳戶數據如下表： 現在要求編寫一程序，完成兩項功能：存款與取款。每次操作完成后向明細表中插入一行記錄并更新帳戶余額。
二、問題似乎很簡單
① 讀取最后一行記錄的帳戶余額數據 ② 根據存、取款金額計算出新的帳戶余額 ③ 將新的記錄插入表中
在不考慮并發(fā)問題的情況下是可行的 如果考慮并發(fā)，問題就多了（導致余額計算錯誤！請參考幻燈片與案例代碼）
三、讓我來想一想既然存在并發(fā)問題，那么解決并發(fā)問題的最好辦法就是加鎖呀！動手試試～～ 怎么加鎖？加什么鎖？ 讀之前加共享鎖？不行?。▍⒖蓟脽羝?/p> 讀之前加排它鎖？還是不行?。▍⒖蓟脽羝?/p> 當然，問題還不止這些！如何讀取最后一行記錄？你會發(fā)現隨著明細記錄的增加越來越沒效率。
四、看來問題真的不是這么簡單問題出在哪里那？從系統(tǒng)設計一開始我們就走錯了！重新設計！
確保帳戶余額在唯一的地方進行存儲 避免了讀取帳戶余額時訪問大量數據并排序
我們無法直接對數據庫進行鎖操作 必須通過合理的事務隔離級別完成并發(fā)控制（ReadUnCommitted、ReadCommitted、RepeatableRead、Serializable），哪一種好呢？
五、著急吃不著熱豆腐看來我們必須對各事務隔離級別逐一分析 ① ReadUnCommitted 顯然不行 在這個事務隔離級別下連臟數據都可能讀到，何況“臟”帳戶余額數據。 ② ReadCommitted 也不行 該隔離級別與二級封鎖協議相對應。讀數據前加共享鎖，讀完就釋放。前面分析過，此處不再贅述。 ③ RepeatableRead 這個隔離級別比較迷惑人，需要仔細分析： RepeatableRead對應第三級封鎖協議：讀前加共享鎖，事務完成才釋放。 （過程參考幻燈片，結論：可以避免并發(fā)問題，但帶來了死鎖?。?/p> ④ Serializable 該事務隔離級別在執(zhí)行時可以避免幻影讀。 但對于本案例執(zhí)行效果與RepeatableRead一樣（效率低下，成功率低，還有討厭的死鎖！）。 似乎走到了絕路 經過重新設計后仍然無法讓人滿意的解決問題！連最高隔離級別都會在高度并發(fā)時因為死鎖造成很大一部分事務執(zhí)行失??！
六、絕處逢生
死鎖的原因是因為讀前加S鎖，而寫前要將S鎖提升為X鎖，由于S鎖允許共享，導致X鎖提升失敗，產生死鎖。
如果在讀時就加上X鎖，就可避免上述問題（從封鎖協議角度這似乎不可能，但確完全可行?。?/p> 其實SQL Server允許在一條命令中同時完成讀、寫操作，這就為我們提供了入手點。 在更新帳戶余額的同時讀取帳戶余額，就等同于在讀數據前加X鎖。命令如下： UPDATE Account SET @newBalance = Balance = Balance + 100 WHERE AccountID = 1 上面的命令對帳戶余額增加100元（粗體部分） 同時讀取更新后的帳戶余額到變量@newBalance中 由于讀取操作融入寫操作中，實現了讀時加X鎖，避免因鎖的提升造成死鎖。 完成存取款的操作可由下面的偽代碼實現： @amount = 存取款的金額
BEGIN TRANSACTION
Try
{
UPDATE Account
SET @newBalance = Balance = Balance + @amount
WHERE AccountID = 1
INSERT INTO AccountDetail (AccountID, Amount, Balance)
VALUES (1, @amount, @newBalance)
COMMIT
}
Catch
{
ROLLBACK
}
通過上述改造，事務中只有寫操作而沒有了讀操作 因此甚至將事務隔離級別設置為ReadUnCommitted都能確保成功執(zhí)行 寫前加X鎖，避免了因提升S鎖造成死鎖的可能
所有并行執(zhí)行的事務全部成功 帳戶余額全部正確 程序執(zhí)行時間同串行執(zhí)行各事務相同
七、事情并沒有結束還有可優(yōu)化的余地：網絡帶寬受到限制時，數據在網絡上傳輸的時間往往比對數據進行讀寫操作的時間要長。
1、讀前加鎖 2、帳戶數據從網上傳過來 3、修改、插入新記錄 4、將改后的數據通過網絡傳回去 5、數據庫提交更新并解鎖。 如果網速很慢，資源鎖定時間就很長。
使用存儲過程，修改后的更新過程： 1、將存、取款用到的數據通過網絡發(fā)給存儲過程。 2、數據加鎖、修改、解鎖。 3、將結果通過網絡回傳。 將網絡延遲放到了事務之外，提高了事務效率。
由于在同一臺機器上執(zhí)行數據庫與應用程序，實驗結果表明存儲過程的執(zhí)行效率不如直接在應用程序中通過命令調用高。 如果能在一個帶寬受到限制的網絡上將數據庫與應用程序分離，然后測試，相信會有令人滿意的結果。（有待具體實驗證實）
八、思考最近園子里面關于O/R Mapping討論得很激烈，想問大家一個問題，就是對于上述問題，O/R Mapping是否提供了解決辦法，允許在Mapping的同時更加精細的控制更新手段呢？
附：代碼分析本文測試用代碼共有5個項目，分別是： 1、SimpleUpdate（最簡單的更新，在沒有并發(fā)時工作得很好） 2、SimpleUpdateInMultiThread（引入并發(fā)，10個線程同時工作，結果上面的更新策略出現了問題） 3、RepeatableReadUpdate（本文第五部分中，使用RepealableRead事務隔離級別的并發(fā)更新，隨沒有錯誤，但導致了死鎖） 4、AnotherMethod（本文最后給出的更新方式，高效且沒有死鎖） 5、UseStoredProcedure（使用存儲過程完成更新）創(chuàng)建存儲過程的代碼可以從DataBase目錄下找到。
首先在SQL Server 2005中建立一空數據庫DBApp，程序執(zhí)行時會自動在此數據庫中創(chuàng)建所需要的表以及記錄。
public void Operation(double amount)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings.Get("ConnectionString"));
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
conn.Open();
cmd.CommandText = "SELECT TOP 1 Balance FROM AccountDetail WHERE AccountID = 1 ORDER BY AccountDetailID DESC";
double oldBalance = Convert.ToDouble(cmd.ExecuteScalar());
double newBalance = oldBalance + amount;
cmd.CommandText = "INSERT INTO AccountDetail (AccountID, Amount, Balance) VALUES (1, " +
amount.ToString() + ", " + newBalance.ToString() + ")";
cmd.ExecuteNonQuery();
conn.Close();
}
這段代碼沒有考慮任何并發(fā)問題，也沒有使用事務，僅僅是讀取最后一條記錄的余額數據，然后根據余額和存取錢金額算出最新余額，并將數據插入到明細記錄中。在沒有并發(fā)問題時，該程序可以很好的執(zhí)行。調用該段代碼的主程序如下： public static void Main()
{
double[] amounts = {-100, 2000, -500, 300, 150, -800, -50, 100, -400, 200};
Account account = new Account();
foreach(double amount in amounts)
{
account.Operation(amount);
}
}
該程序模擬了10次存取款操作，程序執(zhí)行結果完全正確。
在這段代碼中引入了并發(fā)操作，通過10個線程模擬10個人同時進行存取款操作，為了使得模擬真實有效，特意在兩條SQL命令執(zhí)行之間隨機休息了一段時間，其它代碼同上沒有什么變化，結果會發(fā)現，帳戶余額計算多處出現錯誤。 ......
public static void Main()
{
double[] amounts = {-100, 2000, -500, 300, 150, -800, -50, 100, -400, 200};
ManualResetEvent[] doneEvents = new ManualResetEvent[amounts.Length];
Account[] accountArray = new Account[amounts.Length];
for(int i=0; i<amounts.Length; i++)
{
doneEvents[i] = new ManualResetEvent(false);
accountArray[i] = new Account(amounts[i], doneEvents[i]);
ThreadPool.QueueUserWorkItem(new WaitCallback(accountArray[i].ThreadPoolCallback), i);
}
WaitHandle.WaitAll(doneEvents);
ShowResult();
}
......
public void Operation()
{
......
double newBalance = oldBalance + amount;
//為了表示隨機性，先隨機休息一段時間。
Thread.Sleep(rand.Next(500));
cmd.CommandText = "INSERT INTO AccountDetail (AccountID, Amount, Balance) VALUES (1, " +
amount.ToString() + ", " + newBalance.ToString() + ")";
......
}
該段代碼引入了事務，并將事務隔離級別設置為RepeatableRead，程序經過漫長的執(zhí)行后，你會發(fā)現盡管沒有出現任何余額計算錯誤，但10個線程中僅有一半左右執(zhí)行成功，其它線程執(zhí)行失敗，這是由于內部死鎖問題造成的。感興趣的話可以查看SQL Server中鎖的狀態(tài)。 public void Operation()
{
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings.Get("ConnectionString"));
SqlCommand cmd1 = new SqlCommand();
SqlCommand cmd2 = new SqlCommand();
SqlCommand cmd3 = new SqlCommand();
cmd1.Connection = conn;
cmd2.Connection = conn;
cmd3.Connection = conn;
conn.Open();
SqlTransaction tx = conn.BeginTransaction(IsolationLevel.RepeatableRead);
try
{
cmd1.CommandText = "SELECT Balance FROM Account WHERE AccountID = 1";
cmd1.Transaction = tx;
double oldBalance = double.Parse(cmd1.ExecuteScalar().ToString());
double newBalance = oldBalance + amount;
//為了表示隨機性，先隨機休息一段時間。
Thread.Sleep(rand.Next(500));
cmd2.CommandText = "INSERT INTO AccountDetail (AccountID, Amount, Balance) VALUES (1, " +
amount.ToString() + ", " + newBalance.ToString() + ")";
cmd2.Transaction = tx;
cmd2.ExecuteNonQuery();
cmd3.CommandText = "UPDATE Account SET Balance = " + newBalance.ToString() + " WHERE AccountID=1";
cmd3.Transaction = tx;
cmd3.ExecuteNonQuery();
tx.Commit();
}
catch
{
tx.Rollback();
throw new Exception("Transaction Error!");
}
conn.Close();
}
該段代碼實現了在更新的同時完成讀操作，避免了因鎖的提升帶來的并發(fā)問題。10個線程同時執(zhí)行成功，并且執(zhí)行時間與串行執(zhí)行的時間幾乎相同，真正意義上實現了可串行化。 public void Operation()
{
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings.Get("ConnectionString"));
SqlCommand cmd1 = new SqlCommand();
SqlCommand cmd2 = new SqlCommand();
cmd1.Connection = conn;
cmd2.Connection = conn;
conn.Open();
SqlTransaction tx = conn.BeginTransaction(IsolationLevel.ReadUnCommitted);
try
{
cmd1.CommandText = "UPDATE Account SET @newBalance = Balance = Balance +" + this.amount.ToString() +
" WHERE AccountID = 1";
SqlParameter param = new SqlParameter("@newBalance", SqlDbType.Money, 8);
param.Direction = ParameterDirection.Output;
cmd1.Parameters.Add(param);
cmd1.Transaction = tx;
cmd1.ExecuteNonQuery();
double newBalance = Convert.ToDouble(cmd1.Parameters["@newBalance"].Value);
//為了表示隨機性，先隨機休息一段時間。
//Thread.Sleep(rand.Next(500));
cmd2.CommandText = "INSERT INTO AccountDetail (AccountID, Amount, Balance) VALUES (1, " +
amount.ToString() + ", " + newBalance.ToString() + ")";
cmd2.Transaction = tx;
cmd2.ExecuteNonQuery();
tx.Commit();
}
catch
{
tx.Rollback();
throw new Exception("Transaction Error!");
}
conn.Close();
}
該段代碼使用存儲過程實現。存儲過程如下，利用了SQL Server 2005中提供的Try...Catch結構配合事務也可以很好的完成上述任務。 CREATE PROCEDURE [dbo].[Operation] -- Add the parameters for the stored procedure here @amount money, @successed char(1) output AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @newBalance money BEGIN TRY BEGIN TRANSACTION UPDATE Account SET @newBalance = Balance = Balance + @amount WHERE AccountID = 1 INSERT INTO AccountDetail(AccountID, Amount, Balance) VALUES (1, @amount, @newBalance) COMMIT TRANSACTION SET @successed = ‘T‘ END TRY BEGIN CATCH ROLLBACK TRANSACTION SET @successed = ‘F‘ END CATCH END |
|
|
