在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，更新或者删除是否在循环中存在互斥条件