db subsequent and synchronization(transfer)
本部分内容为《数据库原理》课程中的一个课堂案例，幻灯片提供的动画演示有助于理解并发控制的本质，本文内容为幻灯片的摘要。
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，程序执行时会自动在此数据库中创建所需要的表以及记录。
- 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();
}
这段代码没有考虑任何并发问题，也没有使用事务，仅仅是读取最后一条记录的余额数据，然后根据余额和存取钱金额算出最新余额，并将数据插入到明细记录中。在没有并发问题时，该程序可以很好的执行。调用该段代码的主程序如下：
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() + ")";
......
}
- 3、RepeatableReadUpdate
该段代码引入了事务，并将事务隔离级别设置为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();
}
- 4、AnotherMethod
该段代码实现了在更新的同时完成读操作，避免了因锁的提升带来的并发问题。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();
}
- 5、UseStoredProcedure
该段代码使用存储过程实现。存储过程如下，利用了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
