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

,并发控制案例分析,主讲：吕震宇,说明,实验环境： Visual Studio .NET 2005 SQL Server 2005 其它说明： SQL Server 2000 + VS.NET 2003 也可以 需要对代码做细微调整 源代码及数据库见附件,并发控制案例,设某银行存款帐户数据如下表： 现在要求编写一程序，完成两项功能：存款与取款。每次操作完成后向明细表中插入一行记录并更新帐户余额。,1、问题似乎很简单,解决办法： 读取最后一行记录的帐户余额数据 根据存、取款金额计算出新的帐户余额 将新的记录插入表中 真的这么简单？ 在不考虑并发问题的情况下是可行的 如果考虑并发，问题就多了,到银行取钱,单位发工资, 银行读取帐户 余额400元, 单位读取帐户 余额400元, 单位发工资 2000元 更新帐户余额, 取款100元 更新帐户余额,余额错误,上述解决办法的并发问题,2、让我来想一想,问题所在：并发问题！ 解决办法：加锁！ 在读最后一条记录时先加上锁。 怎么加锁？加什么锁？ 读之前加共享锁？ 读之前加排它锁？,读之前加共享锁？ 显然不行！,到银行取钱,单位发工资, 银行读取帐户 余额400元, 单位读取帐户 余额400元, 单位发工资 2000元 更新帐户余额, 取款100元 更新帐户余额,余额错误,读之前加排它锁，事务完成再释放？ 三级封锁协议中没有定义读前加排它锁（暂且不管） 显然不行！,到银行取钱,单位发工资, 银行读取帐户 余额400元, 单位读取帐户 余额400元, 单位发工资 2000元 更新帐户余额, 取款100元 更新帐户余额,余额错误, 等待 等待 等待,还不止这些,如何读取帐户余额？ SELECT TOP 1 帐户余额 FROM 帐户明细 ORDER BY 序号 DESC 存在的问题： 在并发场景下你怎么确定它一定是最后一行？ 随着数据量增大越来越没效率（因为需要排序）,3、看来问题真的不是这么简单,问题出在哪里呢？ 从系统设计一开始我们就走错了！重新设计！,Account,AccountDetail,冗余数据,为什么引入冗余数据？ 确保帐户余额在唯一的地方进行存储 避免了读取帐户余额时访问大量数据并排序 新问题： 我们无法直接对数据库进行锁操作 必须通过合理的事务隔离级别完成并发控制 ReadUnCommitted ReadCommitted RepeatableRead Serializable,4、着急吃不着热豆腐,看来我们必须对各事务隔离级别逐一分析 ReadUnCommitted 显然不行 在这个事务隔离级别下连脏数据都可能读到，何况“脏”帐户余额数据。 ReadCommitted 也不行 该隔离级别与二级封锁协议相对应。读数据前加共享锁，读完就释放。前面分析过，此处不再赘述。, RepeatableRead 这个隔离级别比较迷惑人，需要仔细分析： RepeatableRead对应第三级封锁协议：读前加共享锁，事务完成才释放。 例： 假设事务1执行存钱操作，首先对帐户余额加S锁，然后修改数据。此时事务2要想改帐户余额，它必须先加X锁（自然加不上），所以无法完成操作。 这似乎避免了并发问题的发生，在一个事务执行时将另一个事务的修改请求暂时阻塞，直到事务完成。 但真的能满足应用程序的需要吗？,到银行取钱,单位发工资, 银行读取帐户 余额400元, 单位读取帐户 余额400元, 单位发工资 2000元 更新帐户余额 XLock 等待 等待 等待 等待 等待 等待, 取款100元 更新帐户余额 XLock 等待 等待 等待 等待 等待 等待 等待 等待 等待,尚未提交的脏数据,可见RepeatableRead事务隔离级别容易造成死锁。 一旦出现死锁，DMBS不得不牺牲一个进程。 牺牲进程换来的是不会出现并发异常。 Serializable 该事务隔离级别在执行时可以避免幻影读。 但对于本案例执行效果与RepeatableRead一样。,5、绝处逢生,似乎走到了绝路 连最高隔离级别都会在高度并发时因为死锁造成很大一部分事务执行失败 原因分析 死锁的原因是因为读前加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都可以,改造结果： 通过上述改造，事务中只有写操作而没有了读操作 因此甚至将事务隔离级别设置为ReadUnCommitted都能确保成功执行 写前加X锁，避免了因提升S锁造成死锁的可能 实验结果： 所有并行执行的事务全部成功 帐户余额全部正确 程序执行时间同串行执行各事务相同,6、事情并没有结束,还有可优化的余地 网络带宽受到限制时，数据在网络上传输的时间往往比对数据进行读写操作的时间要长。 一个典型的更新过程： 1、读前加锁 2、帐户数据从网上传过来 3、修改、插入新记录 4、将改后的数据通过网络传回去 5、数据库提交更新并解锁。 如果网速很慢，资源锁定时间就很长。,解决办法： 使用存储过程 修改后的更新过程： 1、将存、取款用到的数据通过网络发给存储过程。 2、数据加锁、修改、解锁。 3、将结果通过网络