Laravel失败任务引发PostgreSQL数据库死锁问题求助

Hey Brandon, let’s break down this deadlock issue you’re hitting after migrating your Zend Framework site to Laravel 7.x — I’ve dealt with similar queue-related Postgres deadlocks on Heroku before, so let’s walk through the fixes step by step.

Core Root Cause #

From what you’ve described, the deadlocks are happening because:

• Failed ImportFile tasks aren’t properly releasing row locks on the processes table (since you’re not calling the status update method on failure)
• When the queue retries the task, the old, failed task process may still hold the row lock (if the transaction wasn’t rolled back properly)
• Frontend polling queries for task status are getting blocked by these held locks, leading to timeouts and deadlocks when combined with new retry attempts

Fix 1: Ensure Failed Tasks Properly Release Locks & Clean Up Transactions #

The biggest mistake here is likely unhandled exceptions leaving transactions open and locks held. Laravel queue tasks don’t auto-wrap in transactions by default, so if you’re manually starting transactions or using model locks, you need to guarantee rollbacks on failure.

Bad Example (What You’re Probably Doing) #

public function handle() {
    // This lock gets held if an exception is thrown
    $process = Process::lockForUpdate()->find($this->processId);
    // Import logic that throws an exception
    $this->runImport($process);
    // Never reaches this if import fails
    $process->status = 'completed';
    $process->save();
}

Fixed Code (Auto-Rollback with Transaction Closure) #

Use Laravel’s DB::transaction() closure — it automatically rolls back on exceptions, releasing locks immediately:

public function handle() {
    DB::transaction(function() {
        $process = Process::lockForUpdate()->findOrFail($this->processId);
        // Run your import logic
        $this->runImport($process);
        // Only reachable if import succeeds
        $process->status = 'completed';
        $process->save();
    });
}

Manual Exception Handling (If You Need Custom Status Updates) #

If you want to mark tasks as failed before retrying, handle exceptions explicitly — don’t re-lock the row in the catch block (that will cause more deadlocks):

public function handle() {
    try {
        $process = Process::lockForUpdate()->findOrFail($this->processId);
        $this->runImport($process);
        $process->status = 'completed';
        $process->save();
    } catch (\Exception $e) {
        // Use a regular query to update status (no lock needed — transaction already rolled back)
        $process = Process::find($this->processId);
        if ($process) {
            $process->status = 'failed';
            $process->error = $e->getMessage();
            $process->save();
        }
        // Re-throw to trigger queue retry
        throw $e;
    }
}

Fix 2: Shorten Lock Hold Time (Critical for Reducing Deadlocks) #

Holding a row lock for the entire duration of your import is a recipe for deadlocks. Instead, only lock the row when you need to update its status — not during the import itself.

public function handle() {
    // Get the process without locking first (import doesn't need the lock)
    $process = Process::findOrFail($this->processId);
    // Run your import logic (long-running, no lock held)
    $importSucceeded = $this->runImport($process);
    // Lock ONLY when updating the status
    DB::transaction(function() use ($process, $importSucceeded) {
        $lockedProcess = Process::lockForUpdate()->find($process->id);
        $lockedProcess->status = $importSucceeded ? 'completed' : 'failed';
        $lockedProcess->save();
    });
}

This way, the lock is only held for milliseconds instead of the entire import runtime.

Fix 3: Optimize Frontend Polling Queries #

Your polling requests are probably triggering shared locks that clash with the task’s exclusive locks. Since polling only needs to show the latest committed status, you don’t need any locks here.

Bad Polling Query (If You’re Using Locks) #

// This will block if the row is locked by a task
public function getStatus(Request $request) {
    return Process::lockForShare()->find($request->processId);
}

Fixed Polling Query (No Locks Needed) #

Postgres’s default READ COMMITTED isolation level will return the latest committed data without blocking:

public function getStatus(Request $request) {
    return Process::find($request->processId);
}

If you absolutely need to avoid stale data (unlikely for polling), use SELECT ... FOR SHARE SKIP LOCKED to skip locked rows instead of blocking:

// Postgres-specific: skips locked rows instead of timing out
public function getStatus(Request $request) {
    return Process::sharedLock()->skipLocked()->find($request->processId);
}

Fix 4: Configure Postgres & Laravel for Less Deadlocks #

• Check Transaction Isolation: Ensure your config/database.php uses READ COMMITTED (Postgres default) for Postgres connections — avoid REPEATABLE READ or SERIALIZABLE which drastically increase deadlock risk.
• Enable Postgres Deadlock Logs: On Heroku, run heroku pg:settings:set log_lock_waits=on deadlock_timeout=1s -a your-app to log detailed deadlock info. This will show you exactly which queries are clashing.
• Queue Retry Delay: Adjust your queue retry delay (in config/queue.php) to give failed tasks time to release locks before retrying — a 30-second delay instead of immediate retries can prevent overlapping lock attempts.

Final Notes #

The key takeaway is that deadlocks almost always come from long-held locks and unhandled transaction state. By cleaning up your task exception handling, minimizing lock time, and optimizing polling, you should eliminate the processes table deadlocks entirely.

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