浅谈 SQL 中的锁(二)余额问题的处理

上次模拟了 SQL 中并发执行更新余额的语句,出现余额负数的问题:

http://bibaoke.com/post/23

现在说说它的解决方法。

事务要正确地执行,就需要【隔离性】这个基本要素。更新余额的语句之所以会偏离期望的结果,是由于查询并没有互斥,也就是没有相互隔离:

--检查用户的余额是否足够
if(exists(select * from balance 
          where id = 1 and value >= 1))


这个查询语句在默认的情况下,会对查询的数据放置共享锁,当并发的时候,两个共享锁并不互斥,所以两个并发的操作都得到余额足够的结果。

那么,要解决这个问题,就需要两个条件:

一、检查余额是否足够的语句要互斥。

二、互斥要持续到完成余额的更新。

于是,我把 SQL 修改成这样:

--开始事务
begin transaction

--检查用户的余额是否足够
if(exists(select * from balance(updlock) 
          where id = 1 and value >= 1))
begin
	--延长处理时间
	waitfor delay '0:00:10'

	--更新余额
	update balance set value = value - 1
end

--提交事务
commit transaction

--查看处理结果
select * from balance


对应上面的条件,语句做了两处修改:

一、在 select * from balance 后面添加了表提示 (updlock),指定对查询的数据放置更新锁。在并发的时候两个更新锁是会互斥的。

二、在整个更新语句的范围指定使用同一个事务,使放置的更新锁持续到整个操作结束。也就是在并发的时候,对同一个用户的余额更新,是串行执行的,只有当一个操作结束,另一个操作才会开始。

这样就杜绝了余额负数的问题。

在这个例子中,我并没有更改 SQL Server 的默认隔离级别 read committed。SQL Server 还有两个更高的隔离级别 repeatable read 和 serializable,什么时候才用到呢?

下次再写。

广州必简信息科技有限公司 版权所有 粤ICP备15047625号-2