本部分内容为《数据库原理》课程中的一个课堂案例，幻灯片提供的动画演示有助于理解并发控制的本质，本文内容为幻灯片的摘要。
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
相关推荐
本案例分析将深入探讨三个经典系统——图书管理系统、人事管理系统和学生管理系统的数据库设计，帮助我们理解如何构建高效、稳定的数据库解决方案。 首先，图书管理系统。在图书管理中，数据库设计通常包括书籍信息...
### 缓存与数据库一致性保证 #### 一、引言 在现代软件系统尤其是高并发系统中，缓存技术被广泛应用于提升系统性能和响应速度。然而，缓存的引入也带来了一系列挑战，其中之一就是如何确保缓存与数据库之间数据的...
并发测试主要目的是检测在多用户同时访问数据库时可能出现的问题，如数据一致性错误、死锁、性能瓶颈等。以下是对并发测试的一些详细说明： **一、并发测试的必要性** 1. **多线程访问**：当服务程序采用多线程...
7. **安全性与并发控制**：确保数据安全性和一致性是数据库管理系统的基石。这涉及到权限控制、事务隔离级别、死锁检测和预防等技术。 8. **测试与调试**：在案例分析中，进行充分的单元测试、集成测试和压力测试是...
MySQL的InnoDB引擎支持ACID（原子性、一致性、隔离性、持久性）特性，通过MVCC（多版本并发控制）确保数据的一致性。 5. SQL优化：通过分析SQL执行计划，避免全表扫描，合理利用索引，减少JOIN操作，优化子查询等...
3. **锁机制**：数据库中的锁用于处理并发操作，确保数据一致性。教程可能讲解了乐观锁、悲观锁、行级锁、页级锁和表级锁的应用场景，以及死锁的预防和解决策略。 4. **CPU管理**：CPU资源是数据库性能的重要因素。...
7. **事务处理**：在数据库操作中，事务用于确保数据的一致性和完整性。理解事务的ACID属性（原子性、一致性、隔离性、持久性）和如何在C#中使用TransactionScope是重要的。 8. **并发控制**：在多用户环境中，...
2015年的数据库系统工程师案例分析真题及答案，是对于这个专业领域的一次深度考核，旨在检验候选人在实际问题解决中的理论知识与实践经验。 **数据库基础** 数据库系统的基础知识包括数据模型（如关系型模型、网络...
综上所述，SQL数据库并发控制的研究是为了在保证数据一致性和完整性的同时，优化数据库性能，满足日益增长的并发数据访问需求。随着信息技术的不断进步，未来对并发控制的要求将越来越高，这对于数据库管理者和系统...
从基础的SQL语法，到深入的数据库设计和优化，再到实际案例的分析，都能帮助我们更好地理解和运用数据库技术，解决各种数据管理问题。通过不断学习和实践，我们能够成为更优秀的数据库开发者，为企业和项目提供稳定...
总的来说，"SQL数据库课程设计案例精品"这门课程将带你深入探索SQL的世界，通过案例分析和实践操作，提升你在数据库设计和管理方面的专业技能。无论是对于软件开发者、数据分析师还是系统管理员，掌握SQL都将是一项...
8. **事务处理**：了解如何使用C#处理数据库事务，确保数据的一致性和完整性，例如，使用TransactionScope类来管理事务。 9. **错误处理和异常处理**：学习如何使用try-catch语句捕获和处理可能出现的数据库操作...
本案例实战主要探讨如何解决在高并发场景下，采用多级缓存架构实现数据一致性的问题。在这个主题中，我们将深入理解多级缓存的概念、作用以及一致性策略。 首先，我们来解释一下什么是多级缓存。多级缓存是指在应用...
在并发环境下，事务的ACID（原子性、一致性、隔离性和持久性）特性是必须遵循的原则。杨昭的案例可能会展示如何在数据库操作中实现这些特性，确保数据的正确性。 最后，安全性是数据库管理的重要方面。这部分可能...
这些都是确保数据库数据一致性、完整性和可靠性的关键。例如，了解如何使用BEGIN、COMMIT、ROLLBACK来管理事务，以及隔离级别对并发性能的影响。 此外，数据库性能优化也是实验的重要部分，这可能涉及到索引策略的...
同时，它也向数据库管理员和开发人员强调了数据类型一致性的重要性，以及在数据库设计时要考虑到字符集和排序规则对性能的影响。 在云数据库管理中，除了索引优化，还有其他多种经典案例，例如SQL优化、锁竞争、...
1. **事务处理**：SQLite支持ACID（原子性、一致性、隔离性和持久性）事务，确保数据在任何情况下都能保持一致性和完整性。 2. **并发控制**：尽管SQLite是单进程数据库，但通过页锁定和行版本控制机制，它能够有效...
这涉及到ACID（原子性、一致性、隔离性和持久性）属性的理解和实践，比如如何确保在并发环境中数据的一致性。 4. 安全与权限管理：实验可能包含设置用户权限，限制对敏感数据的访问，以及如何使用备份和恢复策略来...
模拟试题将涵盖选择题、填空题、简答题和案例分析等多种形式，全方位考察对数据库原理的理解和应用能力。同时，详细的解答部分将帮助学生检查自己的理解，澄清可能存在的疑惑，进一步提升对数据库原理的掌握。