What a deadlock looks like in Laravel
Postgres deadlock
text
ERROR: deadlock detected
DETAIL: Process 5234 waits for ShareLock on transaction 99812; blocked by process 5221.
Process 5221 waits for ShareLock on transaction 99815; blocked by process 5234.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,42) in relation "orders"Surfaces in Laravel as
php
Illuminate\Database\QueryException
SQLSTATE[40P01]: Deadlock detected: 7 ERROR: deadlock detected
SQL: update "orders" set "status" = ? where "id" = ? and "user_id" = ?The SQLSTATE codes to care about
| Code | Meaning | Retry? |
|---|---|---|
| 40001 | Serialization failure (MySQL/Postgres) | Yes |
| 40P01 | Deadlock (Postgres) | Yes |
| 1205 | Lock wait timeout (MySQL) | Yes |
| 1213 | Deadlock (MySQL) | Yes |
Retry with exponential backoff
Laravel's DB::transaction has a built-in retry count, but the retry happens immediately. For heavily contended rows, add jittered backoff:
php
use Illuminate\Database\QueryException;
use Illuminate\Support\Facades\DB;
function withRetry(callable $tx, int $maxAttempts = 3)
{
for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
try {
return DB::transaction($tx);
} catch (QueryException $e) {
$code = $e->errorInfo[0] ?? null;
$deadlock = in_array($code, ['40001', '40P01']);
if (!$deadlock || $attempt === $maxAttempts) {
throw $e;
}
// Jittered exponential backoff — 50ms, 100ms, 200ms + random
usleep((2 ** $attempt) * 50000 + rand(0, 25000));
}
}
}Prevention patterns
1. Consistent lock order
php
// Bad — different paths lock in different orders
DB::transaction(function () {
$user = User::lockForUpdate()->find($userId);
$account = Account::lockForUpdate()->find($accountId);
});
DB::transaction(function () {
$account = Account::lockForUpdate()->find($accountId);
$user = User::lockForUpdate()->find($userId);
});
// Good — always lock in the same order (alphabetical, ID order, whatever)
DB::transaction(function () use ($userId, $accountId) {
$user = User::lockForUpdate()->find($userId);
$account = Account::lockForUpdate()->find($accountId);
});2. Atomic updates over read-modify-write
php
// Bad — window between SELECT and UPDATE where two workers can conflict
$user = User::find($id);
$user->balance += 10;
$user->save();
// Good — atomic, no lock window
User::where('id', $id)->update(['balance' => DB::raw('balance + 10')]);
// Or with increment
User::where('id', $id)->increment('balance', 10);3. SKIP LOCKED for queue-like patterns
php
// Claim a pending job atomically — multiple workers won't collide
$job = DB::transaction(function () {
$row = Job::where('status', 'pending')
->orderBy('created_at')
->limit(1)
->lockForUpdate(skipLocked: true)
->first();
if ($row) {
$row->update(['status' => 'claimed', 'claimed_at' => now()]);
}
return $row;
});4. Keep transactions short
No external HTTP calls, no slow computations, no user-facing IO inside DB::transaction. A transaction that holds locks for 500ms while waiting on Stripe is an incident waiting to happen.
Monitoring deadlock rate
Aggregate QueryExceptions by SQLSTATE code over time. A sudden jump in 40P01 (Postgres) or 1213 (MySQL) correlates with:
- A recent deploy introducing a new lock-ordering bug
- Traffic spike on a hot row (flash sale on a single product)
- A long-running background job holding locks while user requests pile up
- Stale query plan picking a table scan instead of an index (bigger lock footprint)
THE EASY WAY
NightOwl groups exceptions by fingerprint — deadlocks surface automatically
QueryException with SQLSTATE 40P01 groups into one issue regardless of which query triggered it. Track count over time, drill in to see which requests hit it most, get alerted on rate spikes.
bash
composer require nightowl/agent
php artisan nightowl:installFrom $5/month flat. Data in your PostgreSQL.