Database

Fixing Laravel SQLSTATE[40001]: 1213 Deadlock Found When Trying to Get Lock


When your Laravel application processes concurrent requests, webhook bursts, or parallel background jobs, MySQL or MariaDB may abort a query with:

Illuminate\Database\QueryException
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

A deadlock occurs when two database transactions hold locks that the other transaction needs in order to finish. To prevent both connections from hanging forever, InnoDB automatically detects the circular wait, rolls back one transaction immediately, and throws error 1213 so your application can retry it.


Quick Diagnostic Reference

Where the Error Appears Root Cause Solution
Concurrent checkout, wallet, or inventory updates Transactions lack automatic deadlock retry logic Pass attempts: 3 to DB::transaction() (Step 1)
Two endpoints or jobs updating the same parent/child tables Queries acquire row locks in opposite orders Standardize update and primary-key ordering (Step 2)
High-throughput jobs table with QUEUE_CONNECTION=database Multiple Supervisor workers competing for row locks on jobs Migrate queue driver to Redis (Step 3)
Unindexed WHERE clause inside an UPDATE or DELETE InnoDB locks every scanned row via gap locks instead of a single index record Add a selective index & inspect SHOW ENGINE INNODB STATUS (Step 4)

Step 1: Enable Automatic Retries in DB::transaction()

MySQL’s error message explicitly instructs the client to try restarting transaction. By default, calling DB::transaction() without a second argument runs the closure only once. Passing attempts: 3 (or 5) tells Laravel to automatically re-run the transaction if a deadlock occurs:

use Illuminate\Support\Facades\DB;

DB::transaction(function () use ($order, $inventory) {
    $order->update(['status' => 'paid']);
    $inventory->decrement('stock_count');
}, attempts: 3);

Adding Jitter Backoff for High-Concurrency Endpoints

If dozens of workers collide on the same hot row simultaneously, retrying at the exact same millisecond can trigger another collision. Wrap the transaction in Laravel’s retry() helper with a short sleep interval and a conditional check:

use Illuminate\Database\QueryException;
use Illuminate\Support\Facades\DB;

retry(3, function () use ($order, $inventory) {
    DB::transaction(function () use ($order, $inventory) {
        $order->update(['status' => 'paid']);
        $inventory->decrement('stock_count');
    });
}, sleepMilliseconds: 100, when: function (\Throwable $e) {
    return $e instanceof QueryException
        && str_contains($e->getMessage(), '1213 Deadlock found');
});

Step 2: Lock Rows in a Consistent Order

Most application-level deadlocks happen when two processes touch the same tables or rows in reverse sequence:

  • Worker A locks orders row 10, then tries to update inventories row 42.
  • Worker B locks inventories row 42, then tries to update orders row 10.

The Broken Pattern (Mismatched Lock Order & Bulk Updates):

// Process 1 updates parent then child
$order->update(['status' => 'confirmed']);
$order->items()->update(['reserved' => true]);

// Process 2 updates child (with $touches = ['order']) then parent
$orderItem->update(['quantity' => 2]);

The Fix:

  1. Standardize Table Order: Always update parent records before child records across every controller and job in your codebase.
  2. Sort IDs Before Bulk Updates: When updating multiple rows in a loop or batch, sort primary keys in ascending order first so concurrent workers never cross paths:
use App\Models\Inventory;
use Illuminate\Support\Facades\DB;

DB::transaction(function () use ($itemQuantities) {
    // Sort by primary key ID ascending so every worker locks rows in the exact same sequence
    ksort($itemQuantities);

    $inventories = Inventory::whereIn('id', array_keys($itemQuantities))
        ->orderBy('id')
        ->lockForUpdate()
        ->get();

    foreach ($inventories as $inventory) {
        $inventory->decrement('stock_count', $itemQuantities[$inventory->id]);
    }
}, attempts: 3);
  1. Audit $touches on Child Models: If a high-frequency child model (such as Comment or OrderItem) defines protected $touches = ['post'], every child insert or update also issues an UPDATE lock on the parent table. Remove $touches on heavily written child tables if real-time parent timestamp cascading is not strictly required.

Step 3: Move High-Concurrency Queues Off the database Driver

If your stack trace shows deadlocks on UPDATE jobs SET reserved_at = ... or DELETE FROM jobs WHERE id = ..., your Supervisor workers are colliding on the jobs table under QUEUE_CONNECTION=database.

When running more than 2–3 concurrent queue workers, switch your queue backend from MySQL to Redis, which handles atomic job popping in memory without InnoDB row locks:

QUEUE_CONNECTION=redis
REDIS_CLIENT=phpredis
REDIS_HOST=127.0.0.1
REDIS_PORT=6379

(If your container throws connection errors after switching, see Fixing Laravel Redis Connection Refused on Port 6379.)


Step 4: Inspect SHOW ENGINE INNODB STATUS & Index WHERE Columns

Laravel only logs the victim query that MySQL rolled back—it does not show the other query that held the conflicting lock. To see both sides of the most recent deadlock, open a MySQL shell and run:

SHOW ENGINE INNODB STATUS\G

Scroll to the LATEST DETECTED DEADLOCK section, which displays both TRANSACTION (1) and TRANSACTION (2), the exact SQL queries, and the index locks held.

Ensure Every UPDATE and DELETE Uses an Index

Under InnoDB’s default REPEATABLE READ isolation level, running an UPDATE or DELETE on a non-indexed column forces MySQL to lock every row it scans using gap locks. Always add a migration index on foreign keys and status columns used in write queries:

Schema::table('orders', function (Blueprint $table) {
    $table->index(['user_id', 'status']);
});

Prevention Checklist

  1. Pass attempts: 3 to all DB::transaction() calls that modify shared tables.
  2. Keep transactions fast and focused—never make external HTTP API calls (Http::post()) or send emails inside an open DB::transaction() block.
  3. Sort record IDs in ascending order before calling lockForUpdate() or updating multiple rows.
  4. Use Redis instead of QUEUE_CONNECTION=database when running multiple parallel Supervisor queue workers.