Laravel多Worker执行Job时lockForUpdate引发死锁问题求助

在Laravel Job中使用DB::transaction结合lockForUpdate对ProductQuantity表执行查询后更新或创建操作，代码如下：

DB::transaction(function () use ($data) {
    $record = ProductQuantity::lockForUpdate()
                    ->where([
                        'product_variant_id' => $data->id,
                        'inventory_id' => $data->inventory_id,
                        'outlet_id' => $data->outlet_id
                    ])
                    ->first();

                if ($record) {
                    $record->update([
                        'sumOfAvailableQuantity' => $data->sumOfAvailableQuantity,
                        'averageCostPrice' => $data->averageCostPrice,
                        'totalCostPrice' => $data->totalCostPrice,
                        'purchasedQuantity' => $data->purchasedQuantity,
                        'returnedQuantity' => $data->returnedQuantity,
                        'transferredQuantityTo' => $data->transferredQuantityTo,
                        'transferredQuantityFrom' => $data->transferredQuantityFrom,
                        'adjustedQuantity' => $data->adjustedQuantity,
                        'totalSoldQuantity' => $this->getTotalSoldQuantity($data)
                    ]);
                } else {
                    ProductQuantity::create([
                        'product_variant_id' => $data->id,
                        'inventory_id' => $data->inventory_id,
                        'outlet_id' => $data->outlet_id,
                        'sumOfAvailableQuantity' => $data->sumOfAvailableQuantity,
                        'averageCostPrice' => $data->averageCostPrice,
                        'totalCostPrice' => $data->totalCostPrice,
                        'purchasedQuantity' => $data->purchasedQuantity,
                        'returnedQuantity' => $data->returnedQuantity,
                        'transferredQuantityTo' => $data->transferredQuantityTo,
                        'transferredQuantityFrom' => $data->transferredQuantityFrom,
                        'adjustedQuantity' => $data->adjustedQuantity,
                        'product_id' => $data->product_id,
                        'company_id' => $data->company_id,
                        'totalSoldQuantity' => $this->getTotalSoldQuantity($data)
                    ]);
                    }
                });

当有3个以上Worker处理该Job时，出现死锁错误：

Exception: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL: select * from product_quantities where (product_variant_id = 140235 and inventory_id = 1117 and outlet_id = 1122) limit 1 for update

疑问：lockForUpdate本应让请求同一行锁的其他事务等待，为何还会出现死锁？该如何解决？

1. 间隙锁并发冲突：当lockForUpdate查询不存在的行时，InnoDB会生成间隙锁锁定查询条件对应的范围。多个Worker同时查询同一不存在的行时，都会获取该间隙锁；后续每个Worker尝试插入新行时，需要等待其他事务释放间隙锁，而所有事务都持有对方需要的锁资源，最终触发死锁。
2. 事务锁持有时间过长：如果getTotalSoldQuantity这类操作耗时较长，会拉长事务持有锁的时间，增加其他事务竞争锁资源的概率，间接提升死锁发生的可能性。

1. 使用updateOrCreate原子操作替代手动逻辑 #

Laravel的updateOrCreate底层会转化为INSERT ... ON DUPLICATE KEY UPDATE原子语句，避免了先查询后操作的两步式锁竞争，从根源降低死锁概率：

DB::transaction(function () use ($data) {
    ProductQuantity::updateOrCreate(
        // 查询条件（必须是唯一索引组合）
        [
            'product_variant_id' => $data->id,
            'inventory_id' => $data->inventory_id,
            'outlet_id' => $data->outlet_id
        ],
        // 更新/创建的字段
        [
            'sumOfAvailableQuantity' => $data->sumOfAvailableQuantity,
            'averageCostPrice' => $data->averageCostPrice,
            'totalCostPrice' => $data->totalCostPrice,
            'purchasedQuantity' => $data->purchasedQuantity,
            'returnedQuantity' => $data->returnedQuantity,
            'transferredQuantityTo' => $data->transferredQuantityTo,
            'transferredQuantityFrom' => $data->transferredQuantityFrom,
            'adjustedQuantity' => $data->adjustedQuantity,
            'product_id' => $data->product_id,
            'company_id' => $data->company_id,
            'totalSoldQuantity' => $this->getTotalSoldQuantity($data)
        ]
    );
});

注意：必须确保product_variant_id、inventory_id、outlet_id的组合是唯一索引，否则updateOrCreate无法正确触发更新逻辑。

2. 添加死锁重试机制 #

如果无法使用updateOrCreate，可以手动捕获死锁异常并重试事务：

$maxRetries = 3;
$attempts = 0;

do {
    try {
        DB::transaction(function () use ($data) {
            $record = ProductQuantity::lockForUpdate()
                        ->where([
                            'product_variant_id' => $data->id,
                            'inventory_id' => $data->inventory_id,
                            'outlet_id' => $data->outlet_id
                        ])
                        ->first();

            if ($record) {
                $record->update([
                    'sumOfAvailableQuantity' => $data->sumOfAvailableQuantity,
                    'averageCostPrice' => $data->averageCostPrice,
                    'totalCostPrice' => $data->totalCostPrice,
                    'purchasedQuantity' => $data->purchasedQuantity,
                    'returnedQuantity' => $data->returnedQuantity,
                    'transferredQuantityTo' => $data->transferredQuantityTo,
                    'transferredQuantityFrom' => $data->transferredQuantityFrom,
                    'adjustedQuantity' => $data->adjustedQuantity,
                    'totalSoldQuantity' => $this->getTotalSoldQuantity($data)
                ]);
            } else {
                ProductQuantity::create([
                    'product_variant_id' => $data->id,
                    'inventory_id' => $data->inventory_id,
                    'outlet_id' => $data->outlet_id,
                    'sumOfAvailableQuantity' => $data->sumOfAvailableQuantity,
                    'averageCostPrice' => $data->averageCostPrice,
                    'totalCostPrice' => $data->totalCostPrice,
                    'purchasedQuantity' => $data->purchasedQuantity,
                    'returnedQuantity' => $data->returnedQuantity,
                    'transferredQuantityTo' => $data->transferredQuantityTo,
                    'transferredQuantityFrom' => $data->transferredQuantityFrom,
                    'adjustedQuantity' => $data->adjustedQuantity,
                    'product_id' => $data->product_id,
                    'company_id' => $data->company_id,
                    'totalSoldQuantity' => $this->getTotalSoldQuantity($data)
                ]);
            }
        });
        break;
    } catch (\Illuminate\Database\QueryException $e) {
        $attempts++;
        // 仅针对死锁错误重试
        if ($attempts >= $maxRetries || $e->getCode() !== '40001') {
            throw $e;
        }
        // 重试前短暂等待，降低冲突概率
        usleep(100000);
    }
} while ($attempts < $maxRetries);

3. 优化事务与锁策略 #

• 缩小事务范围：将getTotalSoldQuantity这类非数据库操作移到事务外部，减少锁的持有时间。
• 调整事务隔离级别：如果业务允许，将事务隔离级别改为READ COMMITTED，InnoDB在此级别下不会使用间隙锁（除外键约束和唯一索引检查场景），能降低间隙锁导致的死锁概率：

DB::transaction(function () use ($data) {
    DB::statement('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
    // 后续业务逻辑
});

内容的提问来源于stack exchange，提问作者yousef ahmed