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

并发控制案例分析 主讲 吕震宇 说明 实验环境 VisualStudio NET2005SQLServer2005其它说明 SQLServer2000 VS NET2003也可以需要对代码做细微调整源代码及数据库见附件 并发控制案例 设某银行存款帐户数据如下表 现在要求编写一程序 完成两项功能 存款与取款 每次操作完成后向明细表中插入一行记录并更新帐户余额 1 问题似乎很简单 解决办法 读取最后一行记录的帐户余额数据 根据存 取款金额计算出新的帐户余额 将新的记录插入表中真的这么简单 在不考虑并发问题的情况下是可行的如果考虑并发 问题就多了 到银行取钱 单位发工资 银行读取帐户余额400元 单位读取帐户余额400元 单位发工资2000元更新帐户余额 取款100元更新帐户余额 余额错误 上述解决办法的并发问题 2 让我来想一想 问题所在 并发问题 解决办法 加锁 在读最后一条记录时先加上锁 怎么加锁 加什么锁 读之前加共享锁 读之前加排它锁 读之前加共享锁 显然不行 到银行取钱 单位发工资 银行读取帐户余额400元 单位读取帐户余额400元 单位发工资2000元更新帐户余额 取款100元更新帐户余额 余额错误 读之前加排它锁 事务完成再释放 三级封锁协议中没有定义读前加排它锁 暂且不管 显然不行 到银行取钱 单位发工资 银行读取帐户余额400元 单位读取帐户余额400元 单位发工资2000元更新帐户余额 取款100元更新帐户余额 余额错误 等待 等待 等待 还不止这些 如何读取帐户余额 SELECTTOP1帐户余额FROM帐户明细ORDERBY序号DESC存在的问题 在并发场景下你怎么确定它一定是最后一行 随着数据量增大越来越没效率 因为需要排序 3 看来问题真的不是这么简单 问题出在哪里呢 从系统设计一开始我们就走错了 重新设计 Account AccountDetail 冗余数据 为什么引入冗余数据 确保帐户余额在唯一的地方进行存储避免了读取帐户余额时访问大量数据并排序新问题 我们无法直接对数据库进行锁操作必须通过合理的事务隔离级别完成并发控制ReadUnCommittedReadCommittedRepeatableReadSerializable 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锁 就可避免上述问题从封锁协议角度这似乎不可能 但确完全可行 解决办法 其实SQLServer允许在一条命令中同时完成读 写操作 这就为我们提供了入手点 在更新帐户余额的同时读取帐户余额 就等同于在读数据前加X锁 命令如下 UPDATEAccountSET newBalance Balance Balance 100WHEREAccountID 1上面的命令对帐户余额增加100元 蓝色部分 同时读取更新后的帐户余额到变量 newBalance中由于读取操作融入写操作中 实现了读时加X锁 避免因锁的提升造成死锁 完成存取款的操作可由下面的伪代码实现 amount 存取款的金额BEGINTRANSACTIONTry UPDATEAccountSET newBalance Balance Balance amountWHEREAccountID 1INSERTINTOAccountDetail AccountID Amount Balance VALUES 1 amount newBalance COMMIT Catch ROLLBACK 此处甚至将事务隔离级别设置为ReadUnCommitted都可以 改造结果 通过上述改造 事务中只有写操作而没有了读操作因此甚至将事务隔离级别设置为ReadUnCommitted都能确保成功执行写前加X锁 避免了因提升S锁造成死锁的可能实验结果 所有并行执行的事务全部成功帐户余额全部正确程序执行时间同串行执行各事务相同 6 事情并没有结束 还有可优化的余地网络带宽受到限制时 数据在网络上传输的时间往往比对数据进行读写操作的时间要长 一个典型的更新过程 1 读前加锁2 帐户数据从网上传过来3 修改 插入新记录4 将改后的数据通过网络传回去5 数据库提交更新并解锁 如果网速很慢 资源锁定时间就很长 解决办法 使用存储过程修改后的更新过程 1 将存 取款用到的数据通过网络发给存储过程 2 数据加锁 修改 解锁 3 将结果通过网络回传