防止超卖之三-mysql排他锁(for update)

思路:跟redis原子锁方式相似,区别在于,从表中查询库存时加排他锁(for update),每次消耗一件库存,num--,version++。

3次实验结果:20万商品库存消耗完,不多不少,生成20万笔订单。耗时分别为702秒、653秒、656秒。

性能:200000笔/670秒,性能上看,redis原子锁方式约为mysql排他锁的2.5倍。

商品表前-后

3次实验耗时

关键代码

public function createOrderByPessimisticLock(Request $request)
{
    DB::beginTransaction();
    try{
        $userId = $request->input('user_id');
        $goodsId = $request->input('goods_id');

        // 商品
        $goods = TestingGoods::where('id', $goodsId)->lockForUpdate()->first();
        if ($goods->num < 1){
            DB::rollBack();
            return $this->response->json(['status' => 'error', 'msg' => '库存不足,下单失败']);
        }
        $tableName = TestingGoods::$fullTableName;
        TestingGoods::whereId($goodsId)->whereVersion($goods->version)->update([
            'num' => $goods->num - 1,
            'version' => $goods->version + 1
        ]);

        // 订单入库
        $order = new TestingOrder();
        $order->user_id = $userId;
        $order->goods_id = $goodsId;
        $order->goods_num = 1;
        $order->save();

        DB::commit();

        return $this->response->json(['status' => 'success', 'msg' => '下单成功']);
    }catch (\Exception $e) {
        DB::rollBack();
        throw $e;
    }
}