EF+SQLSERVER控制并发下抢红包减余额（改进）

最近几年想必大家一听到哪里有抢红包可以抢，马上会拿起手机点去~~~~然后问题来了。。。

如何控制在同一时间保证数据库中扣减红包余额不会出错。之前我们的做法是直接锁程序，这样子带来的坏处就是等待时间太长，每当一个线程进去之后要经过以下几个过程。

过程分别是

1. 查表

2. 校验信息

3. 发送微信服务器

4. 等待反馈

5. 更新表

等这些过程结束之后才轮到下面这个过程。想必这样要等到花儿都谢了~

另外发送微信服务器这个过程时间在0s至9s时间不等。会产生大量的空闲时间，这里CPU会产生大量的空闲。而且这种情况也无法继续做负载均衡，如果有多个站点部署必定会产生数据库并发问题。

若在查表之前加锁更新后释放掉，虽然说不会产生数据库并发。但是在第二个线程进入查询的时候他会一直在等待，其耗时则与更锁程序差不多。

改进

这个想法源于分布式事务的设计，采用预扣红包余额的方式来保证无需等待微信服务器反馈，让下一个线程可继续执行相关任务。当微信服务器反馈回来时，才开始另外一个事务去更改交易状态。若反馈结果为FAIL则需要预扣的红包余额进行还原操作。

粗略写了模拟实际环境的测试代码，模拟抢红包动作

private void task(){    for (int i = 0; i < 50; i++)    {        string tradeNo = Qxun.Framework.Utility.CreateOrderNo.DateTimeAndNumber();        try        {            using (var trans = new TransactionScope())            {                using (var dbContext = new ActivityDbContext())                {                    //加锁                    var model = dbContext.Database.SqlQuery<Qxun.Activity.Contract.VIPPassRedBag013>(@"select * from VIPPassRedBag013 with(updlock) where ActivitySceneID=199").FirstOrDefault();                    var mode = dbContext.Database.SqlQuery<Qxun.Activity.Contract.VIPPassRedBag013Mode>(@"select * from VIPPassRedBag013Mode with(updlock) where ActivitySceneID=199").ToList();                    //模拟校验延迟                    Thread.Sleep(5);                    //得到领取红包的金额                    VIPPassRedBag013Mode currentMode = null;                    foreach (var modeItem in mode)                    {                        if (modeItem.RemainCount > 0)                        {                            currentMode = modeItem;                            break;                        }                    }                    //判断是否领完                    if (currentMode != null && model != null && model.RedBagBalance >= currentMode.Money)                    {                        VIPPassRedBag013Play currentPlayModel = new VIPPassRedBag013Play();//本次的参与记录对象                        currentPlayModel.VIPPassRedBag013ModeID = currentMode.ID;                        currentPlayModel.WeixinUserID = Thread.CurrentThread.ManagedThreadId;                        currentPlayModel.Money = Convert.ToInt32(currentMode.Money * 100);//要支付的金额（存入到表的）                        currentPlayModel.TradeNumber = tradeNo;                        currentPlayModel.Status = (int)TradeStatus.Trading;                        currentPlayModel.VIPPassRedBag013ModeID = currentMode.ID;                        currentPlayModel.ActivitySceneID = 199;                        dbContext.Insert<VIPPassRedBag013Play>(currentPlayModel);                        currentMode.RemainCount -= 1;                        dbContext.Update<VIPPassRedBag013Mode>(currentMode);                        model.RedBagBalance -= currentMode.Money;                        dbContext.Update<Qxun.Activity.Contract.VIPPassRedBag013>(model);                        trans.Complete();                    }                    else                    {                        trans.Complete();                    }                }            }        }        catch (Exception ex){}        //提交至微信                   string returnCode = "SUCCESS";        Random ran = new Random();        int time = ran.Next(100);        if (time <= 1)        {            returnCode = "FAIL";        }        //模拟网络延迟        Thread.Sleep(time * 100);        //设置重新尝试次数        bool retry = true;        int retryCount = 0;        do        {            Qxun.Activity.Contract.VIPPassRedBag013 model = null;            VIPPassRedBag013Play playModel = null;            VIPPassRedBag013Mode mode = null;            try            {                using (var trans = new TransactionScope())                {                    using (var dbContext = new ActivityDbContext())                    {                        //这里获取很容易异常                        model = dbContext.Database.SqlQuery<Qxun.Activity.Contract.VIPPassRedBag013>(@"select * from VIPPassRedBag013 with(updlock) where ActivitySceneID=199").FirstOrDefault();                        playModel = dbContext.Database.SqlQuery<Qxun.Activity.Contract.VIPPassRedBag013Play>(@"select * from VIPPassRedBag013Play with(updlock) where TradeNumber='" + tradeNo + "'").FirstOrDefault();                        mode = dbContext.Database.SqlQuery<Qxun.Activity.Contract.VIPPassRedBag013Mode>(@"select * from VIPPassRedBag013Mode with(updlock) where ID=" + playModel.VIPPassRedBag013ModeID).FirstOrDefault();                        if (returnCode == "SUCCESS")                        {                            playModel.Status = (int)TradeStatus.Success;                            playModel.Remark = "retry=" + retryCount + ",success;time=" + DateTime.Now.ToString();                            playModel.FinishTime = DateTime.Now;                            dbContext.Update<VIPPassRedBag013Play>(playModel);                            trans.Complete();                            retry = false;                        }                        else                        {                            model.RedBagBalance += mode.Money;                            dbContext.Update<Qxun.Activity.Contract.VIPPassRedBag013>(model);                            playModel.Status = (int)TradeStatus.Fail;                            playModel.Remark = "retry=" + retryCount + ",fail;time=" + DateTime.Now.ToString();                            playModel.FinishTime = DateTime.Now;                            dbContext.Update<VIPPassRedBag013Play>(playModel);                            mode.RemainCount += 1;                            dbContext.Update<VIPPassRedBag013Mode>(mode);                            trans.Complete();                            retry = false;                        }                    }                }            }            catch (Exception ex)            {                //如果之前的线程请求数据库时阻塞                //如果执行失败                retryCount++;                retry = true;            }            if (retryCount > 5)            {                break;            }        } while (retry);    }}

 
模拟100个人并发抢红包

public ActionResult Excute(){    for (int i = 0; i < 100; i++)    {        Thread thread = new Thread(new ThreadStart(task));        thread.Start();    }    return Content("完成！");}

 

上面代码还用了一个retry变量控制防止由于长等待产生的超时，好让每个订单都能够处理的到。但是实际上当线程数量为100-200时候，会有10至20个VIPPassRedBag013Play订单状态一直为Trading。当线程数量大于200的时候就变得及不稳定，目前一直没有找到是什么原因。希望有缘人指点一二。

为了解决这种现象，我在Global写了周期去查找10分钟前的VIPPassRedBag013Play，且订单状态为Trading的单子（都10分钟了还没有处理，那就是处理不到了）。得到订单号，去反查微信的红包交易记录。通过微信红包反馈的结果去更新数据库的交易状态。

public ActionResult Check(){    using (var dbContext = new ActivityDbContext())    {        //查询十分钟之前状态仍为交易中的订单        var playModel = dbContext.Database            .SqlQuery<Qxun.Activity.Contract.VIPPassRedBag013Play>(@"select * from VIPPassRedBag013Play with(nolock) where ActivitySceneID=199                            and[status] = 2 and DATEDIFF(MINUTE, CreateTime, GETDATE()) > 10").ToList();        if (playModel != null && playModel.Count > 0)        {            foreach (var item in playModel)            {                using (var trans = new TransactionScope())                {                    //提交至微信查询                           string returnCode = "SUCCESS";                    Random ran = new Random();                    int time = ran.Next(100);                    if (time <= 1)                    {                        returnCode = "FAIL";                    }                    //去查询微信红包的信息                    //模拟网络延迟                    Thread.Sleep(time * 100);                    if (returnCode == "SUCCESS")                    {                        item.Status = (int)TradeStatus.Success;                        item.Remark = "success;time=" + DateTime.Now.ToString();                        item.FinishTime = DateTime.Now;                        dbContext.Update<VIPPassRedBag013Play>(item);                        trans.Complete();                    }                    else                    {                        Qxun.Activity.Contract.VIPPassRedBag013 model = dbContext.Database                            .SqlQuery<Qxun.Activity.Contract.VIPPassRedBag013>(@"select * from VIPPassRedBag013 with(updlock) where ActivitySceneID=199")                            .FirstOrDefault();                        VIPPassRedBag013Mode mode = dbContext.Database                            .SqlQuery<Qxun.Activity.Contract.VIPPassRedBag013Mode>(@"select * from VIPPassRedBag013Mode with(updlock) where ID=" + item.VIPPassRedBag013ModeID).FirstOrDefault();                        model.RedBagBalance += item.Money;                        dbContext.Update<Qxun.Activity.Contract.VIPPassRedBag013>(model);                        item.Status = (int)TradeStatus.Fail;                        item.Remark = "fail;time=" + DateTime.Now.ToString();                        item.FinishTime = DateTime.Now;                        dbContext.Update<VIPPassRedBag013Play>(item);                        mode.RemainCount += 1;                        dbContext.Update<VIPPassRedBag013Mode>(mode);                        trans.Complete();                    }                }            }        }    }    return View();}

PS:经过这样改进，应该比之前的好多了。当然这样还是很远远不够的。希望各位路过的大神能够指点一二，甚是感谢！