Fixing MySQL Deadlocks in Laravel Queue Jobs

Everything works with one queue worker. Then you scale to eight workers under Supervisor, a busy hour hits, and Horizon (or failed_jobs) starts filling up with this:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock;
try restarting transaction (Connection: mysql, SQL: update `products` set `stock` = `stock` - 2 where `id` = 5)

The job retries, usually succeeds, and the error gets ignored until it starts happening every few minutes. This post is about finding which two queries actually collided and fixing the cause, instead of just turning up the retry count.

Read the deadlock InnoDB already recorded

The Laravel exception only shows the query that lost. The other half of the deadlock is in MySQL, and you can't fix it without seeing both. Run this as a user with the PROCESS privilege:

SHOW ENGINE INNODB STATUS\G

Scroll to the LATEST DETECTED DEADLOCK section. A trimmed version looks like this:

*** (1) TRANSACTION:
TRANSACTION 48211, ACTIVE 0 sec starting index read
update `products` set `stock` = `stock` - 1 where `id` = 9

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 61 page no 4 n bits 80 index PRIMARY of table `shop`.`products`
trx id 48211 lock_mode X locks rec but not gap

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 61 page no 4 n bits 80 index PRIMARY of table `shop`.`products`
trx id 48211 lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 48212, ACTIVE 0 sec starting index read
update `products` set `stock` = `stock` - 2 where `id` = 5

*** WE ROLL BACK TRANSACTION (2)

Three things to pull out of it:

• Both statements. These are only the statements each transaction was running when it got stuck. The lock it already held came from an earlier statement in the same transaction, so you'll have to match them back to your job code.
• The index name. index PRIMARY means row locks by primary key. A secondary index name, or a table you didn't expect, is a strong hint about the cause.
• The lock mode. locks rec but not gap is a plain row lock. locks gap before rec or bare lock_mode X (a next-key lock) means range locking, and insert intention means an INSERT is waiting on someone's gap lock. Those point to different causes, covered below.

The status output only keeps the most recent deadlock. On a busy server it gets overwritten before you look. Turn on full logging so every deadlock goes to the MySQL error log:

SET GLOBAL innodb_print_all_deadlocks = ON;

-- persist it across restarts (MySQL 8.0+)
SET PERSIST innodb_print_all_deadlocks = ON;

On RDS or Aurora you can't run SET GLOBAL for this. Set innodb_print_all_deadlocks to 1 in the DB parameter group and read it from the error log in the console.

Same rows, different order

This is the deadlock from the log above, and it's the most common one in queue jobs. Picture a job that reserves stock for an order:

public function handle(): void
{
    DB::transaction(function () {
        foreach ($this->order->items as $item) {
            Product::whereKey($item->product_id)
                ->decrement('stock', $item->quantity);
        }

        $this->order->update(['status' => 'reserved']);
    });
}

Order A has products 5 then 9. Order B has products 9 then 5. Two workers pick them up at the same moment:

Neither can move. InnoDB notices the cycle almost instantly, picks the transaction that's cheaper to roll back, and throws 1213 at it.

The fix is boring and it works: lock rows in a consistent order. I prefer locking everything up front with one sorted SELECT ... FOR UPDATE, because it also gives you a clean place to check stock before changing anything:

public function handle(): void
{
    DB::transaction(function () {
        $items = $this->order->items()->get();

        // Always lock in ascending id order so concurrent jobs queue up
        // behind each other instead of deadlocking.
        $products = Product::whereIn('id', $items->pluck('product_id')->unique())
            ->orderBy('id')
            ->lockForUpdate()
            ->get()
            ->keyBy('id');

        foreach ($items->sortBy('product_id') as $item) {
            $product = $products[$item->product_id];

            if ($product->stock < $item->quantity) {
                throw new InsufficientStockException($product, $item->quantity);
            }

            $product->decrement('stock', $item->quantity);
        }

        $this->order->update(['status' => 'reserved']);
    }, attempts: 3);
}

Now both workers try to lock product 5 first. One gets it, the other waits a few milliseconds, and nobody deadlocks. The attempts: 3 is still there as a safety net, which I'll get to.

The same rule applies across tables. If one job updates orders then users, and a different job updates users then orders, you have the same cycle with two tables instead of two rows. Pick an order (I go parent before child) and stick to it in every job and controller that touches both.

An UPDATE that locks far more rows than it changes

Sometimes the deadlock log shows two transactions that, as far as you can tell, touch completely different rows. Settling two separate payout batches, say:

DB::transaction(function () {
    Payout::where('batch_ref', $this->batchRef)
        ->where('status', 'pending')
        ->update(['status' => 'settled', 'settled_at' => now()]);

    PayoutBatch::where('ref', $this->batchRef)->update(['closed' => true]);
});

Batch 1001 and batch 1002 share no rows. They still deadlock. Check the plan:

EXPLAIN UPDATE payouts SET status = 'settled'
WHERE batch_ref = 'B-1001' AND status = 'pending';
-- type: ALL, key: NULL, rows: 184302

type: ALL with no key means a full scan, and under the default REPEATABLE READ isolation InnoDB puts a next-key lock on every row it examines, not only the ones that match. Both transactions are trying to lock most of the table, and they're doing it in whatever order the scan goes. In the deadlock log this shows up as bare lock_mode X on index PRIMARY for a query that filters on a different column.

Add the index the WHERE clause needs:

public function up(): void
{
    Schema::table('payouts', function (Blueprint $table) {
        $table->index(['batch_ref', 'status']);
    });
}

public function down(): void
{
    Schema::table('payouts', function (Blueprint $table) {
        $table->dropIndex(['batch_ref', 'status']);
    });
}

With the index, InnoDB locks only the matching index entries (plus the gaps around them), and the two batches stop overlapping. This fix also makes the job faster, so it's rarely a hard sell.

Insert races on a unique key

This one shows insert intention waiting in the deadlock log. The classic shape is "lock the row if it exists, otherwise create it":

DB::transaction(function () {
    $usage = DailyUsage::where('account_id', $this->accountId)
        ->where('date', $this->date)
        ->lockForUpdate()
        ->first();

    if (! $usage) {
        $usage = DailyUsage::create([
            'account_id' => $this->accountId,
            'date' => $this->date,
            'api_calls' => 0,
        ]);
    }

    $usage->increment('api_calls', $this->calls);
});

When the row doesn't exist yet, FOR UPDATE can't lock a row, so it locks the gap where the row would go. Gap locks don't conflict with each other, so two workers both get one. Then both try to INSERT into that gap, each insert waits on the other's gap lock, and you have a deadlock on a row that doesn't even exist yet.

Skip the read-then-write pattern and let the unique index do the work. With a unique index on (account_id, date), a single atomic upsert handles both cases:

DB::statement(
    'INSERT INTO daily_usages (account_id, date, api_calls, created_at, updated_at)
     VALUES (?, ?, ?, NOW(), NOW())
     ON DUPLICATE KEY UPDATE api_calls = api_calls + VALUES(api_calls), updated_at = NOW()',
    [$this->accountId, $this->date, $this->calls]
);

No surrounding transaction needed, and the increment is atomic. (MySQL 8.0.20 deprecated VALUES() in this clause in favour of a row alias, but it still works. Use AS new ... api_calls + new.api_calls if you're on 8.0.19+ and want to avoid the warning.)

If you only need "get or create" without the counter, firstOrCreate() in Laravel 10.20+ falls back to createOrFirst(), which attempts the INSERT and catches UniqueConstraintViolationException instead of locking a gap. That needs a real unique index to work. Without one, you get duplicate rows instead of deadlocks, which is worse.

Batch upsert() calls can still deadlock against each other if two jobs upsert overlapping keys in different orders. Same cure as before: sort the rows by the unique key before calling upsert().

Retrying is a safety net, so make it a safe one

Deadlocks can't be reduced to zero on a busy system. MySQL's own docs say applications should be ready to retry. Laravel gives you two layers for that.

Transaction-level retry

DB::transaction($callback, attempts: 3) catches concurrency errors (it checks for SQLSTATE 40001 and the deadlock message) and reruns the whole closure. This is the cheapest retry because the job doesn't go back to the queue.

The catch: it only retries the outermost transaction. If your job calls a service that opens its own DB::transaction(..., 5) inside another transaction, the inner attempts count is ignored and the exception bubbles up. MySQL rolled back the whole outer transaction anyway, so retrying just the inner part couldn't work. Put the attempts on the transaction the job opens.

Side effects run again

When the closure reruns, everything inside it runs again. A Stripe call, a Mail::send(), or a dispatch() inside the transaction will happen twice. Move them out, or tie them to the commit:

DB::transaction(function () {
    // ... locking and writes ...

    // Only queued if the transaction actually commits
    SendStockReservedEmail::dispatch($this->order)->afterCommit();
}, attempts: 3);

You can also set 'after_commit' => true on the queue connection in config/queue.php to make that the default for every dispatch.

Job-level retry

If all attempts fail, the exception reaches the worker, and normal job retries take over. Give the job some backoff so retries don't land at the exact same moment as the job they collided with:

public $tries = 5;

public function backoff(): array
{
    return [1, 3, 10];
}

When the jobs table itself deadlocks

If you use the database queue driver and the deadlock log mentions the jobs table, the fight is between workers trying to pop the next job, not your code. On MySQL 8.0.1+ and MariaDB 10.6+, Laravel pops jobs with FOR UPDATE SKIP LOCKED, so workers skip rows another worker holds. On MySQL 5.7 it can't, and with more than a handful of workers you'll see deadlocks on jobs regularly.

Upgrade MySQL or move the queue to Redis. I wouldn't try to tune around it.

A related trick for your own tables: if many jobs fight over the same few rows (all jobs for one account, say), sometimes the right fix is fewer concurrent jobs per key, not better locking. The WithoutOverlapping middleware does that with a cache lock:

use Illuminate\Queue\Middleware\WithoutOverlapping;

public function middleware(): array
{
    return [
        (new WithoutOverlapping($this->accountId))
            ->releaseAfter(5)
            ->expireAfter(120),
    ];
}

Jobs for different accounts still run in parallel. Jobs for the same account wait their turn. It needs a cache driver that supports atomic locks (Redis, Memcached, database, DynamoDB), and expireAfter matters: without it, a worker that dies mid-job can leave the lock held, and every later job for that account keeps getting released back to the queue.

What I check, in order

1. Turn on innodb_print_all_deadlocks and collect a few real deadlocks. Guessing from the Laravel exception alone wastes time.
2. Look at the lock mode. Plain row locks on PRIMARY usually means lock ordering. Next-key locks on a query that filters by a non-key column means a missing index. insert intention means a select-then-insert race.
3. Run EXPLAIN on every UPDATE and DELETE inside the transaction. Any type: ALL is a suspect.
4. Make the transaction smaller. HTTP calls, file uploads, and heavy computation don't belong between BEGIN and COMMIT. Every extra millisecond is time for another worker to collide.
5. Then add attempts and backoff, and check that nothing inside the closure has side effects.

If a deadlock still shows up once a day after all that, I leave it alone. A retried transaction that succeeds on the second try is normal behaviour for InnoDB under load, not a bug to chase.

Mayur Dabhi

Full Stack Developer with 5+ years of experience building scalable web applications with Laravel, React, and Node.js.