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