Laravel中ProductAutoMapUpdater死锁问题解决求助

问题背景 #

ProductAutoMapUpdater任务支持管理员手动触发和后台定时执行，执行过程中在多表操作时频繁触发死锁错误，涉及的SQL操作包括：

• product_accessories表插入
• categories表更新
• products表更新

死锁错误日志 #

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL: insert into `product_accessories` (`accessory_id`, `product_id`, `created_at`, `updated_at`, `cross_sell`) values (54392d5a-565f-486b-b17c-4cf7e6dc53bd, 2d7c997e-7591-4e08-b12f-88ff9e3f359d, 2023-01-05 17:05:54, 2023-01-05 17:05:54, 1))

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL: update `categories` set `from_only_product_id` = bcfe0008-4374-420a-b62e-c67b3fc56c68, `categories`.`updated_at` = 2023-01-05 17:05:47 where `id` = 368)

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL: update `products` set `products`.`updated_at` = 2023-01-05 17:05:39 where `id` = f0cdfb07-23ad-4767-b1dd-420d5b1acff0)

原核心代码 #

App\Jobs\ProductAutoMapUpdater #

/**
 * 用于更新产品的可调用自动映射器类。
 *
 * @var array
 */
protected $autoMappers = [
    UpdateDefaultExtendedAttributes::class,
    UpdateCrossSellAccessories::class,
    UpdatePrimaryProductCategory::class,
    UpdateSecondaryCategories::class,
    UpdateRelatedProductAccessories::class,
    UpdateProductVouchers::class,
    UpdateRelatedProductMedia::class,
    UpdateRelatedProductDescription::class,
    UpdateRelatedProductAccessory::class,
    UpdateProductListedMarker::class,
    UpdateCategoryFromOnlyPrice::class,
    UpdateWarranty::class,
    UpdateYoutubeVideo::class,
];

/**
 * 待自动映射的产品。
 *
 * @var Product
 */
public $product;

/**
 * 创建新的任务实例。
 *
 * @return void
 */
public function __construct(Product $product)
{
    $this->delay = 5;

    $this->product = $product;
}

/**
 * 获取任务应通过的中间件。
 *
 * @return array
 */
public function middleware()
{
    return [
        (new WithoutOverlapping($this->product->id))->dontRelease(),
    ];
}

/**
 * 执行任务。
 *
 * @return void
 */
public function handle()
{
    try {
        DB::beginTransaction();

        foreach ($this->autoMappers as $class) {
            $obj = new $class;

            if (is_callable($obj)) {
                $obj($this->product->refreshWithScopes());
            }
        }

        DB::commit();
    } catch (DuplicateSlug $exception) {
        DB::rollback();

        Log::info($exception->getMessage());

        return;
    } catch (\Exception $exception) {
        DB::rollback();

        throw $exception;
    }
}

UpdateCrossSellAccessories类 #

/**
 * 自动映射产品。
 *
 * @param Product $product
 *
 * @return Product
 */
public static function autoMap(Product $product): Product
{
    $product->accessories()->wherePivot('cross_sell', true)->detach();

    return app(Pipeline::class)
        ->send($product)
        ->through([
            \App\Automation\CrossSellMapping\SearchByManufacturer::class,
            \App\Automation\CrossSellMapping\SearchByProductType::class,
            \App\Automation\CrossSellMapping\SearchByManufacturerAndProductType::class,
            \App\Automation\CrossSellMapping\SearchByManufacturerAndModel::class,
            \App\Automation\CrossSellMapping\SearchByManufacturerModelAndProductType::class,
            \App\Automation\CrossSellMapping\SearchByManufacturerAndAttribute::class,
            \App\Automation\CrossSellMapping\SearchByManufacturerProductTypeAndAttribute::class,
            \App\Automation\CrossSellMapping\SearchByProductTypeAndAttribute::class,
            \App\Automation\CrossSellMapping\SearchByAnyAttribute::class,
            \App\Automation\CrossSellMapping\SearchByExactMatch::class,
        ])->then(function ($product) {
            return $product;
        });
}

修复方案 #

死锁根源在于大事务范围、锁竞争顺序不一致和并发冲突未处理，以下是针对性修改：

1. 拆分大事务，缩小事务范围 #

原代码将所有映射器操作放入单个事务，锁持有时间过长且交叉操作易引发死锁。改为每个映射器独立事务：

public function handle()
{
    foreach ($this->autoMappers as $class) {
        try {
            DB::beginTransaction();
            
            $obj = new $class;
            if (is_callable($obj)) {
                $obj($this->product->refreshWithScopes());
            }
            
            DB::commit();
        } catch (DuplicateSlug $exception) {
            DB::rollback();
            Log::info($exception->getMessage());
            continue;
        } catch (\Exception $exception) {
            DB::rollback();
            Log::error("映射器{$class}执行失败: ".$exception->getMessage());
            throw $exception;
        }
    }
}

2. 统一锁获取顺序 #

确保所有事务操作表的顺序一致（如先products，再categories，最后关联表），同时主动锁定主记录避免冲突：

public static function autoMap(Product $product): Product
{
    // 先锁定当前产品，防止其他事务并发修改
    $product = $product->lockForUpdate();
    
    $product->accessories()->wherePivot('cross_sell', true)->detach();

    return app(Pipeline::class)
        ->send($product)
        ->through([
            // 原有管道类
        ])->then(function ($product) {
            return $product;
        });
}

3. 优化并发锁中间件 #

原WithoutOverlapping使用dontRelease()会导致异常时锁永久持有，改为自动释放和超时机制：

public function middleware()
{
    return [
        (new WithoutOverlapping($this->product->id))
            ->expireAfter(300) // 5分钟超时自动释放
            ->releaseAfterException(), // 异常时释放锁
    ];
}

4. 添加死锁自动重试 #

对易触发死锁的操作，添加指数退避重试机制：

// 在映射器或handle方法中封装重试逻辑
$maxRetries = 3;
$retryCount = 0;

do {
    try {
        DB::beginTransaction();
        // 执行具体操作（如插入、更新）
        DB::commit();
        break;
    } catch (\Illuminate\Database\QueryException $exception) {
        DB::rollback();
        // 仅处理死锁错误
        if ($exception->getCode() === '40001' && $retryCount < $maxRetries) {
            $retryCount++;
            usleep(100000 * $retryCount); // 每次重试等待时间递增
            continue;
        }
        throw $exception;
    }
} while ($retryCount < $maxRetries);

5. 减少不必要的锁持有 #

• 移除非必需的refreshWithScopes()，减少数据库交互次数
• 批量操作（如detach()）直接使用原生SQL，避免ORM产生的额外锁

内容的提问来源于stack exchange，提问作者Murlidhar Fichadia