快钱支付与Sql Server的乐观锁和悲观锁

浏览数：16 / 时间：2015年06月12日

在实际的多用户并发访问的生产环境里边，我们经常要尽可能的保持数据的一致性。而其中最典型的例子就是我们从表里边读取数据，检查验证后对数据进行修改，然后写回到数据库中。在读取和写入的过程中，如果在多用户并发的环境里边，其他用户已经把你要修改的数据进行了修改是非常有可能发生的情况，这样就造成了数据的不一致性。

最近在做快钱支付的时候就碰到了这个问题，原来的代码如下：
1. 表Order的结构：
    OrderId   int 自增长
    Status   nvarchar(10) //未处理时的状态为"wait"

2. 相关SQL语句：
Select Status from order where OrderID= @OrderID

Update Order set Status = ‘Y‘ where OrderID=@OrderID

3.程式伪代码：
var status  = GetOrderStatus(orderid); //获取用户充值状态
if(status= "wait")//如果状态为未处理
   UpdateOrderStatus(orderid);//则更新状态为已处理
   //后台给用户充值的代码

按道理这样的代码是没有问题的，因为对同一个用户而已，并不存在并发的问题，也就不存在一次付款两次充值的问题。

然而快钱的处理方式是用户通过付款后，快钱要重新转到我们的网站来，我们在收到快钱支付成功的请求后，给用户充值，并将再次定向的页面返回给快钱，快钱再定向到支付成功的页面。
流程如下：用户--->GoToPay.aspx-->快钱-->AfterPay.aspx-->快钱-->AfterPayMessage.aspx。

由于快钱使用的是轮循的机制，会每隔一秒钟就访问AfterPay.aspx，因此会多次访问AfterPay.aspx，这时问题出来了：
var status  = GetOrderStatus(orderid); //获取用户充值状态
if(status= "wait")//如果状态为未处理
   UpdateOrderStatus(orderid);//则更新状态为已处理
   //后台给用户充值的代码，会充值两次

在程式还未对Status更新的时候,第二次请求已经到达，这时使用GetOrderStatus，得到的还是"wait"，因此会充值两次。

解决方案：
方式一：
使用常规的乐观锁方案
表Order里边加上一列TimeStamp 列，该列是varbinary(8)类型。但是在更新的时候这个值会自动增长。

Select Status,TimeStamp from order where OrderID= @OrderID

--　更新状态,但是要比较时间戳是否发生了变化.如果没有发生变化，影响行数为1,更新成功.如果发生变化,影响行数为0。
update Order
set Status="Y",
where OrderID=@OrderID and TimeStamp=@timestamp
set @rowcount=@@rowcount

程式的修改
var status = GetOrderStatus(orderid,out timestamp); //获取用户充值状态
if(status= "wait")//如果状态为未处理
int rowcount = UpdateOrderStatus(orderid,timestamp);
if(rowcount= 1) //状态未更新
充值
else //快钱第二次过来的时候，返回行数为0
return "已经充过值"
endif

方式二：
还是乐观锁方案：
由于表Order的Status本身就可以起到跟timestamp列一样的效果，修改如下：
update Order
set Status="Y",
where OrderID=@OrderID and Status="wait"
set @rowcount=@@rowcount

程式的修改
var status = GetOrderStatus(orderid); //获取用户充值状态
if(status= "wait")//如果状态为未处理
int rowcount = UpdateOrderStatus(orderid);
if(rowcount= 1) //状态未更新
充值
else //快钱第二次过来的时候，返回行数为0
return "已经充过值"
endif
此方案更为简单。

方案三：
使用悲观锁的方式，这次修改的SQL语句不是Update 而是Select
如下：
Select Status   from order   with (UPDLOCK)　where OrderID= @OrderID

程式完全不用修改：

//获取用户充值状态，快钱第二次过来的时候，如果第一次还未更新，则该订单行还处于锁定状态，因此会等待第一次更新完以将锁释放
var status  = GetOrderStatus(orderid);
if(status= "wait")//如果状态为未处理
   UpdateOrderStatus(orderid);//则更新状态为已处理
   //后台给用户充值的代码

这种方式最简单，程式完全不用修改，只需要在存储过程中加上with (UPDLOCK)即可。
缺点是对大量的并发性能会很差，而且会引起死锁。当然对于充值这种交易而言，还是可以比较适合的。