0

Deadlock — two transactions each waiting for the other to release a lock

Intermediate5 min read·eng-15-012
sqlinterview

Concept

Deadlock — two (or more) transactions each holding a lock that the other needs, resulting in a permanent standstill where neither can proceed.

Classic deadlock scenario:

  • Transaction A locks row 1, then tries to lock row 2.
  • Transaction B locks row 2, then tries to lock row 1.
  • A waits for B to release row 2. B waits for A to release row 1. Neither can proceed — deadlock.

How databases handle deadlocks: Automatically. The database detects the circular wait, picks a "victim" transaction (usually the one that's easier to rollback), rolls it back, and lets the other proceed. The victim transaction receives an error.

MySQL error: SQLSTATE[40001]: Deadlock found when trying to get lock; try restarting transaction.

Common deadlock causes:

  • Inconsistent lock ordering: Transaction A locks users then orders. Transaction B locks orders then users.
  • Long-running transactions holding locks while doing other work.
  • Missing indexes: Full table scans lock more rows than necessary.
  • High concurrency on the same rows.

How to prevent deadlocks:

  1. Consistent lock ordering: Always lock tables/rows in the same order across all transactions.
  2. Keep transactions short: Acquire locks, do the work, commit fast. Don't make HTTP calls inside a transaction.
  3. Use indexes: Narrow the lock scope. Without an index, MySQL may lock the whole table range.
  4. Optimistic locking: Use a version column instead of row locks. Retry on conflict.

Laravel's retry: DB::transaction($fn, $attempts) — if the transaction fails with a deadlock, it automatically retries up to $attempts times.

Code Example

php
<?php
// DEADLOCK SCENARIO — two transactions locking rows in opposite order
// This code won't actually deadlock in sequence — needs concurrent execution

// Transaction A (Request 1):
\DB::transaction(function () {
    \DB::table('accounts')->where('id', 1)->lockForUpdate()->first(); // lock row 1
    usleep(100000); // simulate work — row 2 might be locked by B!
    \DB::table('accounts')->where('id', 2)->lockForUpdate()->first(); // WAITS for B to release row 2
});

// Transaction B (Request 2, concurrent):
\DB::transaction(function () {
    \DB::table('accounts')->where('id', 2)->lockForUpdate()->first(); // lock row 2
    usleep(100000); // simulate work — row 1 might be locked by A!
    \DB::table('accounts')->where('id', 1)->lockForUpdate()->first(); // WAITS for A to release row 1
    // DEADLOCK → DB rolls back one of them
});

// PREVENTION: consistent lock ordering
function transferMoney(int $fromId, int $toId, float $amount): void
{
    // Always lock lower ID first to ensure consistent ordering!
    [$first, $second] = $fromId < $toId ? [$fromId, $toId] : [$toId, $fromId];

    \DB::transaction(function () use ($first, $second, $fromId, $amount) {
        \DB::table('accounts')->where('id', $first)->lockForUpdate()->first();  // always lower ID first
        \DB::table('accounts')->where('id', $second)->lockForUpdate()->first(); // then higher ID
        // No deadlock: both Transaction A and B will lock in the same order
        \DB::table('accounts')->where('id', $fromId)->decrement('balance', $amount);
        \DB::table('accounts')->where('id', ($fromId === $first ? $second : $first))->increment('balance', $amount);
    });
}

// Laravel's automatic retry on deadlock
$order = \DB::transaction(function () {
    return Order::create(['user_id' => 1, 'total' => 99.99]);
}, attempts: 3); // if deadlock → retry up to 3 times automatically

// Handling deadlock manually
try {
    \DB::beginTransaction();
    // ... operations
    \DB::commit();
} catch (\Illuminate\Database\QueryException $e) {
    \DB::rollBack();
    if ($e->errorInfo[1] === 1213) { // MySQL deadlock error code
        // Retry once
        \DB::transaction(fn() => /* same operations */);
    }
    throw $e;
}

// Optimistic locking — avoid row locks entirely
class Account extends \Illuminate\Database\Eloquent\Model
{
    // Add 'version' column to accounts table
    protected static function booted(): void
    {
        static::saving(function (self $model) {
            if ($model->isDirty('balance')) {
                $updated = static::where('id', $model->id)
                                 ->where('version', $model->version)
                                 ->increment('balance', $model->balance - $model->getOriginal('balance'), ['version' => $model->version + 1]);
                if (!$updated) throw new \RuntimeException('Concurrent modification detected — retry');
            }
        });
    }
}
PreviousIsolation level — how much one transaction sees another's uncommitted changesVocabulary — Database & SQLNextN+1 problem — executing N extra queries for N rows instead of one joinVocabulary — Database & SQL