数据库原理补充：数据库并发控制软件设计案例.ppt

并发控制案例分析并发控制案例分析主讲：吕震宇说明实验环境：wVisual Studio.NET 2005wSQL Server 2005其它说明：wSQL Server 2000+VS.NET 2003 也可以w需要对代码做细微调整源代码及数据库见附件并发控制案例设某银行存款帐户数据如下表：w现在要求编写一程序，完成两项功能：存款与取款。每次操作完成后向明细表中插入一行记录并更新帐户余额。帐号0001户名张三序号金额帐户余额1￥1,000.00￥1,000.00 2￥-500.00￥500.00 3￥200.00￥700.00 4￥400.00￥1,100.00 5￥-700.00￥400.00 1、问题似乎很简单解决办法：w 读取最后一行记录的帐户余额数据w 根据存、取款金额计算出新的帐户余额w 将新的记录插入表中真的这么简单？w在不考虑并发问题的情况下是可行的w如果考虑并发，问题就多了序号序号金额金额帐户余额帐户余额5-700.00 400.00 到银行取钱单位发工资 银行读取帐户 余额400元 单位读取帐户 余额400元 单位发工资 2000元 更新帐户余额62000.00 2400.00 取款100元 更新帐户余额7-100.00 300.00 余额错误上述解决办法的并发问题2、让我来想一想问题所在：并发问题！解决办法：加锁！w在读最后一条记录时先加上锁。怎么加锁？加什么锁？w读之前加共享锁？w读之前加排它锁？读之前加共享锁？w显然不行！序号序号金额金额帐户余额帐户余额5-700.00 400.00 到银行取钱单位发工资 银行读取帐户 余额400元 单位读取帐户 余额400元 单位发工资 2000元 更新帐户余额62000.00 2400.00 取款100元 更新帐户余额7-100.00 300.00 余额错误ss读之前加排它锁，事务完成再释放？w三级封锁协议中没有定义读前加排它锁（暂且不管）w显然不行！序号序号金额金额帐户余额帐户余额5-700.00 400.00 到银行取钱单位发工资 银行读取帐户 余额400元 单位读取帐户 余额400元 单位发工资 2000元 更新帐户余额72000.00 2400.00 取款100元 更新帐户余额6-100.00 300.00 余额错误xx 等待 等待 等待x还不止这些如何读取帐户余额？SELECT TOP 1 帐户余额 FROM 帐户明细ORDER BY 序号 DESC存在的问题：w在并发场景下你怎么确定它一定是最后一行？w随着数据量增大越来越没效率（因为需要排序）3、看来问题真的不是这么简单 问题出在哪里呢？w从系统设计一开始我们就走错了！重新设计！帐号户名帐户余额帐号序号金额帐户余额0001张三￥400.00 00011￥1,000.00￥1,000.00 00012￥-500.00￥500.00 00013￥200.00￥700.00 00014￥400.00￥1,100.00 00015￥-700.00￥400.00 AccountAccountDetail冗余数据为什么引入冗余数据？w确保帐户余额在唯一的地方进行存储w避免了读取帐户余额时访问大量数据并排序新问题：w我们无法直接对数据库进行锁操作w必须通过合理的事务隔离级别完成并发控制ReadUnCommittedReadCommittedRepeatableReadSerializable4、着急吃不着热豆腐看来我们必须对各事务隔离级别逐一分析 ReadUnCommittedw显然不行w在这个事务隔离级别下连脏数据都可能读到，何况“脏”帐户余额数据。ReadCommittedw也不行w该隔离级别与二级封锁协议相对应。读数据前加共享锁，读完就释放。前面分析过，此处不再赘述。RepeatableReadw这个隔离级别比较迷惑人，需要仔细分析：wRepeatableRead对应第三级封锁协议：读前加共享锁，事务完成才释放。例：w假设事务1执行存钱操作，首先对帐户余额加S锁，然后修改数据。此时事务2要想改帐户余额，它必须先加X锁（自然加不上），所以无法完成操作。w这似乎避免了并发问题的发生，在一个事务执行时将另一个事务的修改请求暂时阻塞，直到事务完成。w但真的能满足应用程序的需要吗？序号序号金额金额帐户余额帐户余额5-700.00 400.00 到银行取钱单位发工资 银行读取帐户 余额400元 单位读取帐户 余额400元 单位发工资 2000元 更新帐户余额 XLock 等待 等待 等待 等待 等待 等待 取款100元 更新帐户余额 XLock 等待 等待 等待 等待 等待 等待 等待 等待 等待尚未提交的脏数据x帐号户名帐户余额0001张三￥400.00 ss6-100.00 300.00 x72000.00 2400.00 w可见RepeatableRead事务隔离级别容易造成死锁。w一旦出现死锁，DMBS不得不牺牲一个进程。w牺牲进程换来的是不会出现并发异常。Serializablew该事务隔离级别在执行时可以避免幻影读。w但对于本案例执行效果与RepeatableRead一样。5、绝处逢生似乎走到了绝路w连最高隔离级别都会在高度并发时因为死锁造成很大一部分事务执行失败原因分析w死锁的原因是因为读前加S锁，而写前要将S锁提升为X锁，由于S锁允许共享，导致X锁提升失败，产生死锁。解决办法w如果在读时就加上X锁，就可避免上述问题w从封锁协议角度这似乎不可能，但确完全可行！解决办法：w其实SQL Server允许在一条命令中同时完成读、写操作，这就为我们提供了入手点。w在更新帐户余额的同时读取帐户余额，就等同于在读数据前加X锁。命令如下：UPDATE Account SET newBalance=Balance=Balance+100WHERE AccountID=1w上面的命令对帐户余额增加100元（蓝色部分）w同时读取更新后的帐户余额到变量newBalance中w由于读取操作融入写操作中，实现了读时加X锁，避免因锁的提升造成死锁。完成存取款的操作可由下面的伪代码实现：amount=存取款的金额BEGIN TRANSACTIONTry UPDATE AccountSET newBalance=Balance=Balance+amountWHERE AccountID=1 INSERT INTO AccountDetail(AccountID,Amount,Balance)VALUES(1,amount,newBalance)COMMITCatch ROLLBACK此处甚至将事务隔离级别设置为ReadUnCommitted都可以改造结果：w通过上述改造，事务中只有写操作而没有了读操作w因此甚至将事务隔离级别设置为ReadUnCommitted都能确保成功执行w写前加X锁，避免了因提升S锁造成死锁的可能实验结果：w所有并行执行的事务全部成功w帐户余额全部正确w程序执行时间同串行执行各事务相同6、事情并没有结束 还有可优化的余地w网络带宽受到限制时，数据在网络上传输的时间往往比对数据进行读写操作的时间要长。一个典型的更新过程：w1、读前加锁 w2、帐户数据从网上传过来 w3、修改、插入新记录 w4、将改后的数据通过网络传回去 w5、数据库提交更新并解锁。如果网速很慢，资源锁定时间就很长。解决办法：w使用存储过程修改后的更新过程：w1、将存、取款用到的数据通过网络发给存储过程。w2、数据加锁、修改、解锁。w3、将结果通过网络回传。将网络延迟放到了事务之外，提高了事务效率。实验结果w由于在同一台机器上执行数据库与应用程序，实验结果表明存储过程的执行效率不如直接在应用程序中通过命令调用高。w如果能在一个带宽受到限制的网络上将数据库与应用程序分离，然后测试，相信会有令人满意的结果。（有待具体实验证实）