遭遇数据库并发问题

最近碰到一个数据库并发的问题, 觉得是一个很有意义的经验教训, 备忘一下.
大致过程是这样的:
在一次迭代发布之后, 用户反馈了一个问题: 虚拟商品交易成功, 但是买家没有收到卡密.而在日志中跟踪该笔交易, 正好有一个超时情况, 日志中打印出了程序执行的完整路径, 也就是说整个交易完全没有问题. 当时百思不得其解. 怀疑数据库出了问题. 又过了一天, 用户反映了同样的问题, 对比一下本次发布的代码修改, 里面有一个是关于sql的优化, 比较修改前后的代码, 终于找到了问题的罪魁祸首:原来是在sql上出现了问题.
最开始正确的sql:

update table set status =#newStatus#, trade_id = #tradeId#, buyer_id = #buyer_id# where id in (
	select * from (
		select id from table 
		where item_id = #itemId# 
		and status = #initStatus#
		order by id 
	) where rownum <= #count#
) and status = #initStatus#

这句sql的意思是, 一个商品下面有多个虚拟物品(比如游戏币, 充值卡), 一个买家购买了该商品的多个虚拟物品, 此时应该将虚拟物品的状态设置为已购买状态, 因为这些虚拟物品会有多个人购买, 为了避免同一个虚拟物品被不同的人同时购买, 需要对要购买的虚拟物品排序, 然后从中获取要购买的数量, 最后更新状态.
修改后有问题的sql是这样:

update table set status =#soldStatus#, trade_id = #tradeId#, buyer_id = #buyer_id# where id in (
	select * from (
		select id from table 
		where item_id = #itemId# 
		and status = #initStatus#
		order by id 
	) where rownum <= #count#
)

当时咨询dba, 认为在里面已经判断了状态, 外层sql的状态判断显得多余, 建议去掉.

虽然整个sql操作过程也是在事务中执行. 但是依然无法避免并发的出现, 经过分析, 基本上可以还原出问题的场景:
因为更新状态实际上分为两步, 首先要查出数据, 接着再更新. 当两个人同时购买同一个商品时. 如果两人同时查出同一批数据, 一人先更新, 退出事务(此时这批数据的状态已经发生改变), 接着另外一个人再次更新, 如果此时更新没有判断这批数据当前的状态(也就是做乐观锁), 那么就会覆盖前更新者的数据, 导致前一个没有获得最终购买的虚拟物品.
而外层的状态判断相当于对执行的sql加了一个乐观锁, 如果查询得到的那批数据被另一个请求更新, 则在更新状态的数量将与请求的数量不一致, 导致该次更新失败.

评论

4 楼 macrochen 2010-06-28  

zyf30222 写道

有点不明白，我举个例子吧！
某个物品总共有5个，A想买5，B也想买5，这时候，并发的结果是什么呢？

我的猜测是：
A的确买了5，B没有买到，总共卖出也为5；（A的先处理）

想知道最后结果是什么呢？

A买了5个, 首先要找出这5个, 然后B也要买5个, 也从数据库中找5个, 因为这个动作是同时进行的, 所以A, B找的是相同的5个, 但是更新的时候A先更新, 这是可以看到5条更新记录, B后更新5条记录, 此时将覆盖A的更新记录, 如果有一个字段做锁在A更新的时候同时更新锁字段, B再次更新因为所字段已更新, 导致更新记录数为0， 此时就可以判断更新失败

3 楼 zyf30222 2010-06-28  

有点不明白，我举个例子吧！
某个物品总共有5个，A想买5，B也想买5，这时候，并发的结果是什么呢？

我的猜测是：
A的确买了5，B没有买到，总共卖出也为5；（A的先处理）

想知道最后结果是什么呢？

2 楼 macrochen 2010-06-26  

zyf30222 写道

业务上碰到这个怎么处理的，前台提示？
后台碰到这种情况，update后面，是否还有一个是否执行成功（更新的条数），但是这个条数不好确认吧。

前台不会察觉有问题, 但是用户会发现他的购买数量有问题
因为两次更新都是成功的, 因此对更新条数的检查也是没有问题的.

1 楼 zyf30222 2010-06-25  

业务上碰到这个怎么处理的，前台提示？
后台碰到这种情况，update后面，是否还有一个是否执行成功（更新的条数），但是这个条数不好确认吧。