B端业务仓储系统扣减库存更新库存汇总出现mysql Deadlock found when trying to get lock死锁怎么办

在B端业务中由于业务流转繁琐，所处事物或者嵌套事务很长，经常由于程序员代码书写不规范或者经验不足等问题出现类似 Deadlock found when trying to get lock的报错，那该如何去避免呢？

{
  "message": "SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL: delete from `lie_stock_summary` where `id` in (30498))",
  "file": "/data/wwwroot/wms.ichunt.net/vendor/laravel/framework/src/Illuminate/Database/Connection.php",
  "line": 671,
  "code": "40001"
}

来看下面一段代码，注意红色代码区域

    /**
     * Notes:修改或删除库存信息  复核提交，移位完成，调拨完成 后触发
     * 原始库位扣减操作
     * User: sl
     * Date: 2023-04-11 17:31
     * @param $data
     * @param $type 1,复核完成   2调拨出库完成后扣减   3，源库位移位完成后
     * 移位不记录日志
     * @return bool
     **************二位数组*************************
     * [["id"=>"库存id"，"reduceTotalQty"=>"减少的库存总数量"]]
     *
     * [["id"=>"1"，"reduceTotalQty"=>"50"]]
     */
    public static function updateOrDelStock($operator,$type,$data=[])
    {
        \Log::channel("stockLock")->info("---------------修改库存信息------------------");
        \Log::channel("stockLock")->info(sprintf("操作人:%s",json_encode($operator,JSON_UNESCAPED_UNICODE)));
        \Log::channel("stockLock")->info(sprintf("参数:%s",json_encode($data)));
        $arr = [];
        foreach($data as $item){
            if(!isset($arr[$item["id"]])){
                $arr[$item["id"]] = 0;
            }
            $arr[$item["id"]] += $item["reduceTotalQty"];
        }
        $stockIds = array_keys($arr);
        $stockList = StockModel::getStockListByids($stockIds);
        $stockList = arrayChangeKeyByField($stockList,"id");
        try{
            self::startTransaction();
            foreach($stockList as $id=>$stockInfo){
                $reduceQty = $arr[$id] ?? 0;
                if($reduceQty <= 0){
                    continue;
                }
                if($stockInfo["total_qty"] < $reduceQty){
                    throw new InvalidRequestException(sprintf("库存id:%s,扣减库存失败,扣减数量不能大于库存总数量",$id));
                }

                if($stockInfo["useable_qty"] < $reduceQty){
                    throw new InvalidRequestException(sprintf("库存id:%s,扣减库存失败,扣减数量不能大于库存可用数量",$id));
                }

                $totalQty = $stockInfo["total_qty"] - $reduceQty;
                $useableQty = $stockInfo["useable_qty"] - $reduceQty;
                //开始扣减库存
                $update=[];
                $update["total_qty"] = $totalQty;
                $update["useable_qty"] = $useableQty;
                $update["update_uid"] = $operator["operator_id"] ?? 0;
                $update["update_name"] = $operator["operator_name"] ?? "";
                $update["update_time"] = time();
                $update["amount"] = \DB::raw("ROUND(purchase_prices*total_qty,2)");
                $update["standard_money_amount"] = \DB::raw("ROUND(standard_money_prices*total_qty,2)");
                $update["purchase_withoutamount"] = \DB::raw("ROUND(purchase_without_tax_price*total_qty,2)");
                StockModel::where("id",$id)->update($update);
//                if($totalQty ==  0  && $useableQty == 0){
//                    //进行库存汇总
//                    self::updateOrCreateStockSummary([$id]);
//                    //删除
//                    StockModel::where("id",$id)->delete();
//
//                }

                //进行库存汇总
                self::updateOrCreateStockSummary([$id]);
                //删除
                if($totalQty ==  0  && $useableQty == 0){
                    StockModel::where("id",$id)->delete();
                }

            }
            self::commitTransaction();
        }catch (\Throwable $e){
            self::rollBackTransaction();
            throw new InvalidRequestException($e->getMessage());
        }

    }

 

    /**
     * Notes:创建库存汇总
     * 根据库存  组织+仓库+商品编码   分组统计
     * User: sl
     * Date: 2023-04-15 14:29
     * @param $stockSummaryIds
     */
    public static function updateOrCreateStockSummary($stockIds=[])
    {
        //查询库存
        $stockList = StockModel::getStockListByids($stockIds);



        $arr = [];//分组 采购组织+仓库+商品编码=>[库存列表]二位数组
        foreach($stockList as $stock){
            //采购组织+仓库+商品编码 维度统计库存
            $key = sprintf("%s_@@@_%s_@@@_%s",$stock["company_id"],$stock["warehouse_id"],$stock["goods_sn"]);
            $arr[] = $key;
        }
        $arr = array_filter_unique($arr);
        $groupData = [];//分组     采购组织+仓库+商品编码=>[库存列表]二位数组
        foreach($arr as $groupStockeyword){
            $map = explode("_@@@_",$groupStockeyword);
            if(count($map) != 3){
                continue;
            }
            $stockListSearch = StockModel::getStockByGroupStockeyword($map[0],$map[1],$map[2]);
            //找出相同组织 仓库 商品编码的库存信息
            $groupData[$groupStockeyword] = $stockListSearch;
        }
        //统计所有库存现在的锁库数量
        $tmpStockLockQtyByStockIds = \Arr::pluck($groupData,"*.id");
        $stockLockQtyByStockIds = [];
        foreach($tmpStockLockQtyByStockIds as $itemStockIds){
            $stockLockQtyByStockIds += $itemStockIds;
        }
        $stockLockQtyByStockIds = array_filter_unique($stockLockQtyByStockIds);
        $stockLockQty = StockLockModel::getStockLockQtyByStockIds($stockLockQtyByStockIds);
        $stockLockQty = arrayChangeKeyByField($stockLockQty,"stock_id");

        $filterData = [];//组装需要插入或者更新的数据   采购组织+仓库+商品编码=>库存信息 一位数组
        foreach($groupData as $groupStockeyword=>$stockListVal){
            foreach($stockListVal as $stockInfo){
                if(!isset($filterData[$groupStockeyword])){
                    $filterData[$groupStockeyword] = [];
                }
                if(empty($filterData[$groupStockeyword])){
                    $filterData[$groupStockeyword]["company_id"] = $stockInfo["company_id"];
                    $filterData[$groupStockeyword]["company_name"] = $stockInfo["company_name"];
                    $filterData[$groupStockeyword]["warehouse_id"] = $stockInfo["warehouse_id"];
                    $filterData[$groupStockeyword]["goods_id"] = $stockInfo["goods_id"];
                    $filterData[$groupStockeyword]["goods_sn"] = $stockInfo["goods_sn"];
                    $filterData[$groupStockeyword]["goods_name"] = $stockInfo["goods_name"];
                    $filterData[$groupStockeyword]["brand_id"] = $stockInfo["brand_id"];
                    $filterData[$groupStockeyword]["brand_name"] = $stockInfo["brand_name"];
                    $filterData[$groupStockeyword]["goods_unit"] = $stockInfo["goods_unit"];
                    $filterData[$groupStockeyword]["total_qty"] = $stockInfo["total_qty"];//库存总数量
                    $filterData[$groupStockeyword]["useable_qty"] = $stockInfo["useable_qty"];//可用库存数量
                    $filterData[$groupStockeyword]["lock_qty"] = $stockLockQty[$stockInfo["id"]]["total_lock_qty"] ?? 0;
                    $filterData[$groupStockeyword]["pick_stock_qty"] = $stockInfo["pick_stock_qty"];//拣货数量
                    $filterData[$groupStockeyword]["freeze_stock_qty"] = $stockInfo["freeze_stock_qty"];//冻结数量
                    $filterData[$groupStockeyword]["standard_money_amount"] = $stockInfo["standard_money_amount"];//本位币总金额
                    $filterData[$groupStockeyword]["standard_money_currency"] = $stockInfo["standard_money_currency"];//本位币
                    $filterData[$groupStockeyword]["create_uid"] = getAdminUserId();
                    $filterData[$groupStockeyword]["create_name"] = getAdminUserName();
                    $filterData[$groupStockeyword]["create_time"] = time();
                }else{
                    $filterData[$groupStockeyword]["total_qty"] += $stockInfo["total_qty"];//库存总数量
                    $filterData[$groupStockeyword]["useable_qty"] += $stockInfo["useable_qty"];//可用库存数量
                    $filterData[$groupStockeyword]["lock_qty"] += $stockLockQty[$stockInfo["id"]]["total_lock_qty"] ?? 0;
                    $filterData[$groupStockeyword]["pick_stock_qty"] += $stockInfo["pick_stock_qty"];//拣货数量
                    $filterData[$groupStockeyword]["freeze_stock_qty"] += $stockInfo["freeze_stock_qty"];//冻结数量
                    $filterData[$groupStockeyword]["standard_money_amount"] += $stockInfo["standard_money_amount"];//本位币总金额
                }

            }
        }
        //准备插入或者更新数据
        foreach($filterData as $stockSummary){
            $updateData["company_id"] = $stockSummary["company_id"];
            $updateData["warehouse_id"] = $stockSummary["warehouse_id"];
            $updateData["goods_sn"] = $stockSummary["goods_sn"];
            $insertData = $stockSummary;
            \Arr::forget($insertData, 'company_id');
            \Arr::forget($insertData, 'warehouse_id');
            \Arr::forget($insertData, 'goods_sn');
            StockSummaryModel::updateOrCreate($updateData,$insertData);
            if($insertData["total_qty"] <= 0){
                StockSummaryModel::delStockSummary($updateData);
            }
        }

    }

上述红色代码区域在并发情况下经常会出现Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction数据库报错

优化后代码：

//准备插入或者更新数据
        foreach($filterData as $stockSummary){
            $updateData["company_id"] = $stockSummary["company_id"];
            $updateData["warehouse_id"] = $stockSummary["warehouse_id"];
            $updateData["goods_sn"] = $stockSummary["goods_sn"];
            $insertData = $stockSummary;
            \Arr::forget($insertData, 'company_id');
            \Arr::forget($insertData, 'warehouse_id');
            \Arr::forget($insertData, 'goods_sn');
            if($insertData["total_qty"] <= 0 && StockSummaryModel::existsStockSummary($updateData)){
                $stockSummaryIds = StockSummaryModel::getStockSummary($updateData);
                StockSummaryModel::delStockSummaryByIds($stockSummaryIds);
            }else{
                StockSummaryModel::updateOrCreate($updateData,$insertData);
            }

        }

 

 /**
     * Notes:修改或删除库存信息  复核提交，移位完成，调拨完成 后触发
     * 原始库位扣减操作
     * User: sl
     * Date: 2023-04-11 17:31
     * @param $data
     * @param $type 1,复核完成   2调拨出库完成后扣减   3，源库位移位完成后
     * 移位不记录日志
     * @return bool
     **************二位数组*************************
     * [["id"=>"库存id"，"reduceTotalQty"=>"减少的库存总数量"]]
     *
     * [["id"=>"1"，"reduceTotalQty"=>"50"]]
     */
    public static function updateOrDelStock($operator,$type,$data=[])
    {
        \Log::channel("stockLock")->info("---------------修改库存信息------------------");
        \Log::channel("stockLock")->info(sprintf("操作人:%s",json_encode($operator,JSON_UNESCAPED_UNICODE)));
        \Log::channel("stockLock")->info(sprintf("参数:%s",json_encode($data)));
        $arr = [];
        foreach($data as $item){
            if(!isset($arr[$item["id"]])){
                $arr[$item["id"]] = 0;
            }
            $arr[$item["id"]] += $item["reduceTotalQty"];
        }
        $stockIds = array_keys($arr);
        $stockList = StockModel::getStockListByids($stockIds);
        $stockList = arrayChangeKeyByField($stockList,"id");
        try{
            self::startTransaction();
            $isToStockSummaryIds = [];//需要重新更新或者删除的库存汇总的库存id
            $isToDelStockIds = [];//扣减库存后需要删除的库存
            foreach($stockList as $id=>$stockInfo){
                $reduceQty = $arr[$id] ?? 0;
                if($reduceQty <= 0){
                    continue;
                }
                if($stockInfo["total_qty"] < $reduceQty){
                    throw new InvalidRequestException(sprintf("库存id:%s,扣减库存失败,扣减数量不能大于库存总数量",$id));
                }

                if($stockInfo["useable_qty"] < $reduceQty){
                    throw new InvalidRequestException(sprintf("库存id:%s,扣减库存失败,扣减数量不能大于库存可用数量",$id));
                }

                $totalQty = $stockInfo["total_qty"] - $reduceQty;
                $useableQty = $stockInfo["useable_qty"] - $reduceQty;
                //开始扣减库存
                $update=[];
                $update["total_qty"] = \DB::raw("total_qty-{$reduceQty}");
                $update["useable_qty"] = \DB::raw("useable_qty-{$reduceQty}");;
                $update["update_uid"] = $operator["operator_id"] ?? 0;
                $update["update_name"] = $operator["operator_name"] ?? "";
                $update["update_time"] = time();
                $update["amount"] = \DB::raw("ROUND(purchase_prices*total_qty,2)");
                $update["standard_money_amount"] = \DB::raw("ROUND(standard_money_prices*total_qty,2)");
                $update["purchase_withoutamount"] = \DB::raw("ROUND(purchase_without_tax_price*total_qty,2)");
                $bk = StockModel::where("id",$id)->where("total_qty",$stockInfo["total_qty"])->where("useable_qty",$stockInfo["useable_qty"])->update($update);
                if(!$bk){
                    throw new InvalidRequestException(sprintf("库存id:%s,扣减库存失败:可能存在相同库存数据重复扣减或者并发扣减情况",$id));
                }

                //进行库存汇总
                array_push($isToStockSummaryIds,$id);
                //删除
                if($totalQty ==  0  && $useableQty == 0){
                    array_push($isToDelStockIds,$id);
                }

            }
            self::updateOrCreateStockSummary($isToStockSummaryIds);
            if(!empty($isToDelStockIds)){
                StockModel::delStockByIds($isToDelStockIds);
            }
            self::commitTransaction();
        }catch (\Throwable $e){
            \Log::channel("stockLock")->info(json_encode(ErrMsg::getExceptionInfo($e)));
            self::rollBackTransaction();
            throw new InvalidRequestException($e->getMessage());
        }

    }

优化代码思路

1：explain 查看sql，更新代码的where条件是否有索引或者是否用的主键id更新，统一更改为用主键更新或者删除更稳妥

2，更新或者删除是否在循环中存在互斥条件