剛在cnblogs看了呂震宇老師空間里關(guān)于數(shù)據(jù)庫事務(wù)處理的貼,剛好這兩天在搞javaWeb的數(shù)據(jù)庫表設(shè)計(jì)時(shí)也碰到類似問題,因此很仔細(xì)地看了遍后獲益頗多,特地引了過來:http://www.cnblogs.com/zhenyulu/articles/633486.html.
         個(gè)人認(rèn)為有些問題在不同的層面考慮都有不同解決辦法,當(dāng)然至于效率就另作考慮了,通常這些事務(wù)都是交給數(shù)據(jù)庫處理,不用花多心思在應(yīng)用程序上處理,當(dāng)然在應(yīng)用程序上通過方法定義實(shí)現(xiàn)就更靈活更有效率,也是個(gè)人比較推薦的...

        本部分內(nèi)容為《數(shù)據(jù)庫原理》課程中的一個(gè)課堂案例，幻燈片提供的動(dòng)畫演示有助于理解并發(fā)控制的本質(zhì)，本文內(nèi)容為幻燈片的摘要。

1、下載本文所對(duì)應(yīng)的幻燈片； 2、下載本文對(duì)應(yīng)的VS2005代碼。

如果你對(duì)自己并發(fā)控制的能力很有自信的話，讀完“一、問題提出”后直接可以跳轉(zhuǎn)到“四、看來問題真不簡單”處閱讀。

本文最后給出了部分測(cè)試用代碼的簡單講解。

　

一、問題提出

設(shè)某銀行存款帳戶數(shù)據(jù)如下表：

現(xiàn)在要求編寫一程序，完成兩項(xiàng)功能：存款與取款。每次操作完成后向明細(xì)表中插入一行記錄并更新帳戶余額。

　

二、問題似乎很簡單

• 解決辦法：

① 讀取最后一行記錄的帳戶余額數(shù)據(jù)

② 根據(jù)存、取款金額計(jì)算出新的帳戶余額

③ 將新的記錄插入表中

• 真的這么簡單？

在不考慮并發(fā)問題的情況下是可行的

如果考慮并發(fā)，問題就多了（導(dǎo)致余額計(jì)算錯(cuò)誤！請(qǐng)參考幻燈片與案例代碼）

　

三、讓我來想一想

既然存在并發(fā)問題，那么解決并發(fā)問題的最好辦法就是加鎖呀！動(dòng)手試試～～

怎么加鎖？加什么鎖？

讀之前加共享鎖？不行?。▍⒖蓟脽羝?/p>

讀之前加排它鎖？還是不行?。▍⒖蓟脽羝?/p>

當(dāng)然，問題還不止這些！如何讀取最后一行記錄？你會(huì)發(fā)現(xiàn)隨著明細(xì)記錄的增加越來越?jīng)]效率。

　

四、看來問題真的不是這么簡單

問題出在哪里那？從系統(tǒng)設(shè)計(jì)一開始我們就走錯(cuò)了！重新設(shè)計(jì)！

 

• 為什么引入冗余數(shù)據(jù)？

確保帳戶余額在唯一的地方進(jìn)行存儲(chǔ)

避免了讀取帳戶余額時(shí)訪問大量數(shù)據(jù)并排序

• 新的問題：

我們無法直接對(duì)數(shù)據(jù)庫進(jìn)行鎖操作

必須通過合理的事務(wù)隔離級(jí)別完成并發(fā)控制（ReadUnCommitted、ReadCommitted、RepeatableRead、Serializable），哪一種好呢？

　

五、著急吃不著熱豆腐

看來我們必須對(duì)各事務(wù)隔離級(jí)別逐一分析

① ReadUnCommitted

顯然不行

在這個(gè)事務(wù)隔離級(jí)別下連臟數(shù)據(jù)都可能讀到，何況“臟”帳戶余額數(shù)據(jù)。

② ReadCommitted

也不行

該隔離級(jí)別與二級(jí)封鎖協(xié)議相對(duì)應(yīng)。讀數(shù)據(jù)前加共享鎖，讀完就釋放。前面分析過，此處不再贅述。

③ RepeatableRead

這個(gè)隔離級(jí)別比較迷惑人，需要仔細(xì)分析：

RepeatableRead對(duì)應(yīng)第三級(jí)封鎖協(xié)議：讀前加共享鎖，事務(wù)完成才釋放。

（過程參考幻燈片，結(jié)論：可以避免并發(fā)問題，但帶來了死鎖?。?/p>

④ Serializable

該事務(wù)隔離級(jí)別在執(zhí)行時(shí)可以避免幻影讀。

但對(duì)于本案例執(zhí)行效果與RepeatableRead一樣（效率低下，成功率低，還有討厭的死鎖?。?/p>

似乎走到了絕路

經(jīng)過重新設(shè)計(jì)后仍然無法讓人滿意的解決問題！連最高隔離級(jí)別都會(huì)在高度并發(fā)時(shí)因?yàn)樗梨i造成很大一部分事務(wù)執(zhí)行失??！

　

六、絕處逢生

• 原因分析

死鎖的原因是因?yàn)樽x前加S鎖，而寫前要將S鎖提升為X鎖，由于S鎖允許共享，導(dǎo)致X鎖提升失敗，產(chǎn)生死鎖。

• 解決辦法

如果在讀時(shí)就加上X鎖，就可避免上述問題（從封鎖協(xié)議角度這似乎不可能，但確完全可行！）

其實(shí)SQL Server允許在一條命令中同時(shí)完成讀、寫操作，這就為我們提供了入手點(diǎn)。

在更新帳戶余額的同時(shí)讀取帳戶余額，就等同于在讀數(shù)據(jù)前加X鎖。命令如下：

UPDATE Account
SET @newBalance = Balance = Balance + 100
WHERE AccountID = 1

上面的命令對(duì)帳戶余額增加100元（粗體部分）

同時(shí)讀取更新后的帳戶余額到變量@newBalance中

由于讀取操作融入寫操作中，實(shí)現(xiàn)了讀時(shí)加X鎖，避免因鎖的提升造成死鎖。

完成存取款的操作可由下面的偽代碼實(shí)現(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
}

• 改造結(jié)果：

通過上述改造，事務(wù)中只有寫操作而沒有了讀操作

因此甚至將事務(wù)隔離級(jí)別設(shè)置為ReadUnCommitted都能確保成功執(zhí)行

寫前加X鎖，避免了因提升S鎖造成死鎖的可能

• 實(shí)驗(yàn)結(jié)果：

所有并行執(zhí)行的事務(wù)全部成功

帳戶余額全部正確

程序執(zhí)行時(shí)間同串行執(zhí)行各事務(wù)相同

　

七、事情并沒有結(jié)束

還有可優(yōu)化的余地：網(wǎng)絡(luò)帶寬受到限制時(shí)，數(shù)據(jù)在網(wǎng)絡(luò)上傳輸?shù)臅r(shí)間往往比對(duì)數(shù)據(jù)進(jìn)行讀寫操作的時(shí)間要長。

• 一個(gè)典型的更新過程：

1、讀前加鎖

2、帳戶數(shù)據(jù)從網(wǎng)上傳過來

3、修改、插入新記錄

4、將改后的數(shù)據(jù)通過網(wǎng)絡(luò)傳回去

5、數(shù)據(jù)庫提交更新并解鎖。

如果網(wǎng)速很慢，資源鎖定時(shí)間就很長。

• 解決辦法：

使用存儲(chǔ)過程，修改后的更新過程：

1、將存、取款用到的數(shù)據(jù)通過網(wǎng)絡(luò)發(fā)給存儲(chǔ)過程。

2、數(shù)據(jù)加鎖、修改、解鎖。

3、將結(jié)果通過網(wǎng)絡(luò)回傳。

將網(wǎng)絡(luò)延遲放到了事務(wù)之外，提高了事務(wù)效率。

• 實(shí)驗(yàn)結(jié)果

由于在同一臺(tái)機(jī)器上執(zhí)行數(shù)據(jù)庫與應(yīng)用程序，實(shí)驗(yàn)結(jié)果表明存儲(chǔ)過程的執(zhí)行效率不如直接在應(yīng)用程序中通過命令調(diào)用高。

如果能在一個(gè)帶寬受到限制的網(wǎng)絡(luò)上將數(shù)據(jù)庫與應(yīng)用程序分離，然后測(cè)試，相信會(huì)有令人滿意的結(jié)果。（有待具體實(shí)驗(yàn)證實(shí)）

　

八、思考

最近園子里面關(guān)于O/R Mapping討論得很激烈，想問大家一個(gè)問題，就是對(duì)于上述問題，O/R Mapping是否提供了解決辦法，允許在Mapping的同時(shí)更加精細(xì)的控制更新手段呢？

---

　

附：代碼分析

本文測(cè)試用代碼共有5個(gè)項(xiàng)目，分別是：

1、SimpleUpdate（最簡單的更新，在沒有并發(fā)時(shí)工作得很好）

2、SimpleUpdateInMultiThread（引入并發(fā)，10個(gè)線程同時(shí)工作，結(jié)果上面的更新策略出現(xiàn)了問題）

3、RepeatableReadUpdate（本文第五部分中，使用RepealableRead事務(wù)隔離級(jí)別的并發(fā)更新，隨沒有錯(cuò)誤，但導(dǎo)致了死鎖）

4、AnotherMethod（本文最后給出的更新方式，高效且沒有死鎖）

5、UseStoredProcedure（使用存儲(chǔ)過程完成更新）創(chuàng)建存儲(chǔ)過程的代碼可以從DataBase目錄下找到。

• 準(zhǔn)備工作

首先在SQL Server 2005中建立一空數(shù)據(jù)庫DBApp，程序執(zhí)行時(shí)會(huì)自動(dòng)在此數(shù)據(jù)庫中創(chuàng)建所需要的表以及記錄。

• 1、SimpleUpdate

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ā)問題，也沒有使用事務(wù)，僅僅是讀取最后一條記錄的余額數(shù)據(jù)，然后根據(jù)余額和存取錢金額算出最新余額，并將數(shù)據(jù)插入到明細(xì)記錄中。在沒有并發(fā)問題時(shí)，該程序可以很好的執(zhí)行。調(diào)用該段代碼的主程序如下：

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í)行結(jié)果完全正確。

• 2、SimpleUpdateInMultiThread

在這段代碼中引入了并發(fā)操作，通過10個(gè)線程模擬10個(gè)人同時(shí)進(jìn)行存取款操作，為了使得模擬真實(shí)有效，特意在兩條SQL命令執(zhí)行之間隨機(jī)休息了一段時(shí)間，其它代碼同上沒有什么變化，結(jié)果會(huì)發(fā)現(xiàn)，帳戶余額計(jì)算多處出現(xiàn)錯(cuò)誤。

......
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;
//為了表示隨機(jī)性，先隨機(jī)休息一段時(shí)間。
Thread.Sleep(rand.Next(500));
cmd.CommandText = "INSERT INTO AccountDetail (AccountID, Amount, Balance) VALUES (1, " +
amount.ToString() + ", " + newBalance.ToString() + ")";
......
}

• 3、RepeatableReadUpdate

該段代碼引入了事務(wù)，并將事務(wù)隔離級(jí)別設(shè)置為RepeatableRead，程序經(jīng)過漫長的執(zhí)行后，你會(huì)發(fā)現(xiàn)盡管沒有出現(xiàn)任何余額計(jì)算錯(cuò)誤，但10個(gè)線程中僅有一半左右執(zhí)行成功，其它線程執(zhí)行失敗，這是由于內(nèi)部死鎖問題造成的。感興趣的話可以查看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;
//為了表示隨機(jī)性，先隨機(jī)休息一段時(shí)間。
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();
}

• 4、AnotherMethod

該段代碼實(shí)現(xiàn)了在更新的同時(shí)完成讀操作，避免了因鎖的提升帶來的并發(fā)問題。10個(gè)線程同時(shí)執(zhí)行成功，并且執(zhí)行時(shí)間與串行執(zhí)行的時(shí)間幾乎相同，真正意義上實(shí)現(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);
//為了表示隨機(jī)性，先隨機(jī)休息一段時(shí)間。
//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();
}

• 5、UseStoredProcedure

該段代碼使用存儲(chǔ)過程實(shí)現(xiàn)。存儲(chǔ)過程如下，利用了SQL Server 2005中提供的Try...Catch結(jié)構(gòu)配合事務(wù)也可以很好的完成上述任務(wù)。

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