剛在cnblogs看了呂震宇老師空間里關于數據庫事務處理的貼,剛好這兩天在搞javaWeb的數據庫表設計時也碰到類似問題,因此很仔細地看了遍后獲益頗多,特地引了過來:http://www.cnblogs.com/zhenyulu/articles/633486.html.
個人認為有些問題在不同的層面考慮都有不同解決辦法,當然至于效率就另作考慮了,通常這些事務都是交給數據庫處理,不用花多心思在應用程序上處理,當然在應用程序上通過方法定義實現就更靈活更有效率,也是個人比較推薦的...
本部分內容為《數據庫原理》課程中的一個課堂案例，幻燈片提供的動畫演示有助于理解并發控制的本質，本文內容為幻燈片的摘要。
1、下載本文所對應的幻燈片； 2、下載本文對應的VS2005代碼。
如果你對自己并發控制的能力很有自信的話，讀完“一、問題提出”后直接可以跳轉到“四、看來問題真不簡單”處閱讀。
本文最后給出了部分測試用代碼的簡單講解。
一、問題提出
設某銀行存款帳戶數據如下表：
現在要求編寫一程序，完成兩項功能：存款與取款。每次操作完成后向明細表中插入一行記錄并更新帳戶余額。
二、問題似乎很簡單
① 讀取最后一行記錄的帳戶余額數據
② 根據存、取款金額計算出新的帳戶余額
③ 將新的記錄插入表中
在不考慮并發問題的情況下是可行的
如果考慮并發，問題就多了（導致余額計算錯誤！請參考幻燈片與案例代碼）
三、讓我來想一想
既然存在并發問題，那么解決并發問題的最好辦法就是加鎖呀！動手試試～～
怎么加鎖？加什么鎖？
讀之前加共享鎖？不行！（參考幻燈片）
讀之前加排它鎖？還是不行！（參考幻燈片）
當然，問題還不止這些！如何讀取最后一行記錄？你會發現隨著明細記錄的增加越來越沒效率。
四、看來問題真的不是這么簡單
問題出在哪里那？從系統設計一開始我們就走錯了！重新設計！
確保帳戶余額在唯一的地方進行存儲
避免了讀取帳戶余額時訪問大量數據并排序
我們無法直接對數據庫進行鎖操作
必須通過合理的事務隔離級別完成并發控制（ReadUnCommitted、ReadCommitted、RepeatableRead、Serializable），哪一種好呢？
五、著急吃不著熱豆腐
看來我們必須對各事務隔離級別逐一分析
① ReadUnCommitted
顯然不行
在這個事務隔離級別下連臟數據都可能讀到，何況“臟”帳戶余額數據。
② ReadCommitted
也不行
該隔離級別與二級封鎖協議相對應。讀數據前加共享鎖，讀完就釋放。前面分析過，此處不再贅述。
③ RepeatableRead
這個隔離級別比較迷惑人，需要仔細分析：
RepeatableRead對應第三級封鎖協議：讀前加共享鎖，事務完成才釋放。
（過程參考幻燈片，結論：可以避免并發問題，但帶來了死鎖！）
④ Serializable
該事務隔離級別在執行時可以避免幻影讀。
但對于本案例執行效果與RepeatableRead一樣（效率低下，成功率低，還有討厭的死鎖！）。
似乎走到了絕路
經過重新設計后仍然無法讓人滿意的解決問題！連最高隔離級別都會在高度并發時因為死鎖造成很大一部分事務執行失敗！
六、絕處逢生
死鎖的原因是因為讀前加S鎖，而寫前要將S鎖提升為X鎖，由于S鎖允許共享，導致X鎖提升失敗，產生死鎖。
如果在讀時就加上X鎖，就可避免上述問題（從封鎖協議角度這似乎不可能，但確完全可行！）
其實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都能確保成功執行
寫前加X鎖，避免了因提升S鎖造成死鎖的可能
所有并行執行的事務全部成功
帳戶余額全部正確
程序執行時間同串行執行各事務相同
七、事情并沒有結束
還有可優化的余地：網絡帶寬受到限制時，數據在網絡上傳輸的時間往往比對數據進行讀寫操作的時間要長。
1、讀前加鎖
2、帳戶數據從網上傳過來
3、修改、插入新記錄
4、將改后的數據通過網絡傳回去
5、數據庫提交更新并解鎖。
如果網速很慢，資源鎖定時間就很長。
使用存儲過程，修改后的更新過程：
1、將存、取款用到的數據通過網絡發給存儲過程。
2、數據加鎖、修改、解鎖。
3、將結果通過網絡回傳。
將網絡延遲放到了事務之外，提高了事務效率。
由于在同一臺機器上執行數據庫與應用程序，實驗結果表明存儲過程的執行效率不如直接在應用程序中通過命令調用高。
如果能在一個帶寬受到限制的網絡上將數據庫與應用程序分離，然后測試，相信會有令人滿意的結果。（有待具體實驗證實）
八、思考
最近園子里面關于O/R Mapping討論得很激烈，想問大家一個問題，就是對于上述問題，O/R Mapping是否提供了解決辦法，允許在Mapping的同時更加精細的控制更新手段呢？
附：代碼分析
本文測試用代碼共有5個項目，分別是：
1、SimpleUpdate（最簡單的更新，在沒有并發時工作得很好）
2、SimpleUpdateInMultiThread（引入并發，10個線程同時工作，結果上面的更新策略出現了問題）
3、RepeatableReadUpdate（本文第五部分中，使用RepealableRead事務隔離級別的并發更新，隨沒有錯誤，但導致了死鎖）
4、AnotherMethod（本文最后給出的更新方式，高效且沒有死鎖）
5、UseStoredProcedure（使用存儲過程完成更新）創建存儲過程的代碼可以從DataBase目錄下找到。
首先在SQL Server 2005中建立一空數據庫DBApp，程序執行時會自動在此數據庫中創建所需要的表以及記錄。
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();
}
這段代碼沒有考慮任何并發問題，也沒有使用事務，僅僅是讀取最后一條記錄的余額數據，然后根據余額和存取錢金額算出最新余額，并將數據插入到明細記錄中。在沒有并發問題時，該程序可以很好的執行。調用該段代碼的主程序如下：
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次存取款操作，程序執行結果完全正確。
- 2、SimpleUpdateInMultiThread
在這段代碼中引入了并發操作，通過10個線程模擬10個人同時進行存取款操作，為了使得模擬真實有效，特意在兩條SQL命令執行之間隨機休息了一段時間，其它代碼同上沒有什么變化，結果會發現，帳戶余額計算多處出現錯誤。
......
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，程序經過漫長的執行后，你會發現盡管沒有出現任何余額計算錯誤，但10個線程中僅有一半左右執行成功，其它線程執行失敗，這是由于內部死鎖問題造成的。感興趣的話可以查看SQL Server中鎖的狀態。
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();
}
該段代碼實現了在更新的同時完成讀操作，避免了因鎖的提升帶來的并發問題。10個線程同時執行成功，并且執行時間與串行執行的時間幾乎相同，真正意義上實現了可串行化。
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
posted on 2009-01-07 18:19
圣克爾·光 閱讀(282)
評論(0) 編輯 收藏