ORM扣库存导致的BUG总结

前一段时间,以前一个项目的积分兑换功能出现了库存超扣现象,排查问题时,一看更新时没有加锁,于是以为是锁的问题,加锁之后没有仔细推敲就汇报bug修复完成.

//初始代码
DB::transaction(function () use ($request, $user_model) {
 $goods_model = Goods::find($request->id);
 if ($goods_model->stock <= 0 || $goods_model->stock < $request->number) {
          throw new \Exception('库存不足');
         }
  //扣库存
 $goods_model->stock-=$request->number;
 $goods_model->save();
 ...
});

//第一次修复BUG,增加悲观锁 for update
DB::transaction(function () use ($request, $user_model) {
 $goods_model = Goods::lockForUpdate()->find($request->id);
 if ($goods_model->stock <= 0 || $goods_model->stock < $request->number) {
          throw new \Exception('库存不足');
         }
  //扣库存
 $goods_model->stock-=$request->number;
 $goods_model->save();
 ...
});

昨天接的报告,库存超扣BUG又出现了,立即查看Binlog 日志排查sql执行记录

看日志,同一时刻,有两个线程(386609669,370064926)更新库存,线程386609669 先将库存更新为2 ,线程370064926随后又将库存更新为9 ,并且响应时间760ms ,猜想是数据库响应问题,正考虑其它解决方案,队列或限流等.

看日志nginx,发现在抢购兑换时间内只有几十的并发,看了一下RDS的资源统计使用率不高,感觉悲观锁可以应付的来,不是性能问题.

继续看binlog 日志,发现库存被线程370064926更新为9之后,又被其它线程386850322更新为3,继续往后看发现同一时间内有好多线程在同时更新库存而且更新库存数量不相同.

百思不得其解,网上Google Mysql高并发扣库存方案,其中有一只乐观锁方案,提到在事物中更新库存时,要比对程序中的库存数量和mysql中的库存数量,突然间恍然大悟,仔细一看代码.果然,犯了一个相当愚蠢的错误.

DB::transaction(function () use ($request, $user_model) {
 $goods_model = Goods::lockForUpdate()->find($request->id);
 if ($goods_model->stock <= 0 || $goods_model->stock < $request->number) {
          throw new \Exception('库存不足');
         }
  //扣库存
 $goods_model->stock-=$request->number; 标注1
 $goods_model->save();
 
 ...
});

问题分析

标注1

更新使用的是程序模型中的库存数量 – 兑换数量

假设stock为 9 number 为 2
上述写法生成的sql 为 update goods set stock = 7 where id = ?
在高并发场景下这么写显然是非常错误的. 假设商品的初始库存为10,ABC三个用户同一时刻兑换商品,ABC读取到程序模型中的库存数量 $goods_model->stock 都是10 ,由于用了悲观锁for update(这不是重点,不用悲观锁也会出现这种情况) 更新语句变为阻塞执行,用户A将库存改为9,用户BC同样将库存改为9,最后库存剩余还是9,这显然与预期结果不服,正确的剩余库存应该为7

扣库存应该采用 Update goods set stock = stock -1 where id = ? 这种语句,更新时读取的是当前记录的stock的值.最后修正结果代码如下:

//最后修正
DB::transaction(function () use ($request, $user_model) {
 $goods_model = Goods::lockForUpdate()->find($request->id);
 if ($goods_model->stock <= 0 || $goods_model->stock < $request->number) {
          throw new \Exception('库存不足');
         }
  //扣库存 正确的操作 或者使用原生update
  $goods_model->decrement('stock');
 ...
});

decrement 方法生成的语句为update goods set stock = stock -1 where id = ?

总结

外包项目写多了忽略了程序在高并发场景下可能发生的情况.高并发场景要考虑进程线程之间数据一致性和以及程序和数据库(包含nosql) 的数据一致性问题
遇到问题要反复仔细验证,不要轻易下结论

疑问

使用错误的的写法用JMeter模拟并发兑换 20threads/1s 200threads/1s 都没有出现库存超扣现象,由于生产环境不允许压测,所以在本机测试 .猜测可能本机的配置高于服务器,处理能力高于服务器所有没有复现,以后验证成功回头再补充

本机(8核16G) 服务器(4核8G)而且跑了很多项目

解决方案引申

乐观锁

//乐观锁
    public function caseOne(Request $request)
    {
        $this->validate($request, [
            'number' => 'bail|required|integer|min:1',
            'goods_id' => [
                'bail', 'required', 'integer',
                Rule::exists('goods', 'id')->where(function ($query) use ($request) {
                    $query->where('stock', '>=', $request->number);
                }),
            ]
        ]);
        DB::transaction(function () use ($request) {
            $goods = Goods::find($request->goods_id);
            $result = DB::update('update goods set stock = stock - ? where id = ? and stock >= ?', [$request->number, $request->goods_id, $request->number]);
            if (!$result) {
                return response()->json(['message' => '失败']);
            }
            Orders::create([
                'goods_id' => $goods->id,
                'goods_name' => $goods->name,
                'number' => $request->number,
            ]);
        });

        return response()->json();
    }

悲观锁

//悲观锁
    public function caseTwo(Request $request)
    {
        $this->validate($request, [
            'number' => 'bail|required|integer|min:1',
            'goods_id' => [
                'bail', 'required', 'integer',
                Rule::exists('goods', 'id')->where(function ($query) use ($request) {
                    $query->where('stock', '>=', $request->number);
                }),
            ]
        ]);
        DB::transaction(function () use ($request) {
            $goods = Goods::lockForUpdate()->find($request->goods_id);
            $result = DB::update('update goods set stock = stock - ? where id = ? and stock >= ?', [$request->number, $request->goods_id, $request->number]);
            if (!$result) {
                return response()->json(['message' => '失败']);
            }
            Orders::create([
                'goods_id' => $goods->id,
                'goods_name' => $goods->name,
                'number' => $request->number,
            ]);
        });

        return response()->json();
    }

不用事务

//不用事务
    public function caseThree(Request $request)
    {
        $this->validate($request, [
            'number' => 'bail|required|integer|min:1',
            'goods_id' => [
                'bail', 'required', 'integer',
                Rule::exists('goods', 'id')->where(function ($query) use ($request) {
                    $query->where('stock', '>=', $request->number);
                }),
            ]
        ]);
        $goods = Goods::find($request->goods_id);
        $result = DB::update('update goods set stock = stock - ? where id = ? and stock >= ?', [$request->number, $request->goods_id, $request->number]);
        if (!$result) {
            return response()->json(['message' => '失败']);
        }
        Orders::create([
            'goods_id' => $goods->id,
            'goods_name' => $goods->name,
            'number' => $request->number,
        ]);
    }

redis库存

//redis库存
    public function caseFour(Request $request)
    {
        $this->validate($request, [
            'number' => 'bail|required|integer|min:1',
            'goods_id' => [
                'bail', 'required', 'integer',
                function ($attributes, $value, $fail) use ($request) {
                    $stock = Redis::get('goods_id_' . $value);
                    if (is_null($stock)) {
                        return $fail('商品不存在');
                    }
                    if ($stock < $request->number) {
                        return $fail('库存不足');
                    }
                }
            ]
        ]);
        $goods = Goods::find($request->goods_id);
        $result = DB::update('update goods set stock = stock - ? where id = ? and stock >= ?', [$request->number, $request->goods_id, $request->number]);
        if (!$result) {
            return response()->json(['message' => '失败']);
        }
        Orders::create([
            'goods_id' => $goods->id,
            'goods_name' => $goods->name,
            'number' => $request->number,
        ]);
        Redis::decr('goods_id_' . $request->goods_id);

        return response()->json();
    }

队列

public function caseFive(Request $request)
    {
        $this->validate($request, [
            'number' => 'bail|required|integer|min:1',
            'goods_id' => [
                'bail', 'required', 'integer',
                function ($attributes, $value, $fail) use ($request) {
                    $stock = Redis::get('goods_id_' . $value);
                    if (is_null($stock)) {
                        return $fail('商品不存在');
                    }
                    if ($stock < $request->number) {
                        return $fail('库存不足');
                    }
                }
            ]
        ]);
        $goods = Goods::find($request->goods_id);
        SecKill::dispatch($goods, $request->all());

        return response()->json();
    }

https://github.com/yangliuan/shop-demo 有待完善