Database Deadlocks Explained | Backend Study Lab

Quick answer

A database deadlock occurs when transactions form a cycle of waits: each holds a lock another needs, so none can progress. Database engines detect the cycle and abort one transaction as a victim so the others can continue.

Deadlocks are normal under concurrency, even in otherwise correct systems. Reduce them by keeping transactions short, acquiring resources in a consistent order, indexing locking predicates, and avoiding unnecessary lock scope. Then handle the engine’s documented deadlock error by rolling back and retrying the entire idempotent transaction with a small attempt limit and randomized backoff.

A two-row deadlock

Create two accounts:

CREATE TABLE account (
  id bigint PRIMARY KEY,
  balance_cents bigint NOT NULL
);

INSERT INTO account(id, balance_cents) VALUES (1, 10000), (2, 10000);

Transaction A:

BEGIN;
UPDATE account SET balance_cents = balance_cents - 100 WHERE id = 1;
-- waits before the next statement
UPDATE account SET balance_cents = balance_cents + 100 WHERE id = 2;

Transaction B:

BEGIN;
UPDATE account SET balance_cents = balance_cents - 200 WHERE id = 2;
UPDATE account SET balance_cents = balance_cents + 200 WHERE id = 1;

A holds the lock on row 1 and waits for row 2. B holds row 2 and waits for row 1:

Transaction A: holds account 1 -> waits for account 2
Transaction B: holds account 2 -> waits for account 1

The engine selects a victim, rolls it back, and returns an error. PostgreSQL uses SQLSTATE 40P01 for deadlock_detected. InnoDB detects deadlocks by default and rolls back one transaction. The surviving transaction is not evidence that the victim’s business operation completed.

The classic Coffman conditions explain why the cycle can exist: resources are mutually exclusive, transactions hold one resource while waiting for another, locks are not forcibly preempted, and the wait graph is circular. Database engines preserve the first three properties for correctness and break the circular wait by aborting a victim.

Deadlock versus lock timeout

A lock wait is not necessarily a deadlock. Transaction A may simply be doing useful work while B waits. If A commits, B proceeds.

A deadlock is a dependency cycle that cannot resolve without aborting something. Engines use a wait-for graph or equivalent detection. A lock timeout is a policy limit: a transaction waited too long, whether or not a cycle existed.

Handle the errors separately in observability. Frequent timeouts may indicate long transactions or overload. Frequent deadlocks usually point to inconsistent access order, wide lock ranges, missing indexes, or a contended workflow. Both can be transient retry candidates, but only after verifying the operation is safe to rerun.

Why deadlocks happen

Opposite lock order is the classic cause. One code path updates parent then child; another updates child then parent. Transfers lock account IDs in caller-provided order. Batch jobs process keys in different sequences.

Range and gap locks can make the graph less obvious. MySQL InnoDB locking reads may lock index records and gaps. A query that scans many records due to a missing or unselective index can acquire a much larger lock footprint than intended.

Foreign-key checks, unique-index inserts, triggers, cascading updates, and ORM flush order can introduce locks not visible in the service method. DDL can also contend with application transactions through table or metadata locks.

Long transactions increase the overlap window. Waiting for an HTTP provider, rendering a report, pausing for user input, or processing thousands of rows while holding locks makes a cycle more likely and consumes a pooled connection.

Prevent with deterministic ordering

If every transfer locks the lower account ID first, the two-row cycle disappears:

BEGIN;

SELECT id
FROM account
WHERE id IN (:from_id, :to_id)
ORDER BY id
FOR UPDATE;

UPDATE account
SET balance_cents = balance_cents - :amount
WHERE id = :from_id;

UPDATE account
SET balance_cents = balance_cents + :amount
WHERE id = :to_id;

COMMIT;

Every competing path must follow the same rule. Document a global order across resource types when workflows touch accounts, orders, and inventory—for example, tenant, aggregate type, then primary key.

Ordering reduces a known pattern but cannot prove no deadlock will ever occur. New queries, constraints, maintenance, and engine behavior can add edges. Keep retry handling.

Reduce lock scope and duration

Move validation that does not require locked state before the transaction. Precompute request data, but recheck any correctness condition inside the transaction.

Avoid network calls while holding locks. Use an outbox or post-commit worker for email, webhooks, and message publication. Break large maintenance operations into bounded batches with a stable order and checkpoints.

Add indexes that support UPDATE ... WHERE and SELECT ... FOR UPDATE predicates. Confirm execution plans on production-like data. A query intended to lock one tenant must not scan and lock far more rows.

Use the weakest lock that protects the invariant, but do not weaken correctness merely to suppress metrics. SKIP LOCKED is appropriate for competing queue workers that may take any available job; it is not appropriate when a business operation must process a specific locked row.

Isolation levels and deadlocks

Lowering isolation is not a universal deadlock fix. MySQL’s documentation notes that deadlocks can occur because of writes regardless of isolation level. Isolation changes read visibility and some lock behavior, but transactions still conflict over writes.

Stronger isolation may produce serialization failures in addition to deadlocks. Both indicate that the database prevented an unsafe interleaving. The error codes and retry rules differ, so maintain an explicit allowlist rather than retrying every database exception.

Autocommit single statements can deadlock too when one statement touches multiple rows or indexes. A short transaction lowers risk; it does not eliminate cycles.

Safe retry requirements

When the engine aborts a transaction, all its statements are rolled back. Start a new transaction and rerun the decision from fresh reads.

A retry is safe only when side effects outside the database are absent or protected. If the attempt sent an email before the deadlock, rolling back SQL does not unsend it. Put outbound work in a transactional outbox, or use a durable idempotency key at the external provider.

Use bounded exponential backoff with jitter. Immediate retries from many workers can collide again:

attempt 1: wait random 10-30 ms
attempt 2: wait random 20-70 ms
attempt 3: stop and surface a retryable failure

Keep the total within the request deadline. Background jobs can use longer schedules. Metrics should distinguish first-attempt success, recovered retry, exhausted retry, and non-retryable failure.

Spring and Java example

Spring translates database errors into its data-access exception hierarchy. The exact translated subtype depends on driver and configuration, so confirm it with an integration test. Keep retry outside the transactional proxy so each attempt opens a new transaction:

@Service
public class TransferService {
  private final TransferTransaction transaction;

  public TransferService(TransferTransaction transaction) {
    this.transaction = transaction;
  }

  public void transfer(long fromId, long toId, long cents) {
    for (int attempt = 1; attempt <= 3; attempt++) {
      try {
        transaction.run(fromId, toId, cents);
        return;
      } catch (DeadlockLoserDataAccessException ex) {
        if (attempt == 3) throw ex;
        sleepWithJitter(attempt);
      }
    }
  }
}

@Service
class TransferTransaction {
  private final JdbcTemplate jdbc;

  TransferTransaction(JdbcTemplate jdbc) {
    this.jdbc = jdbc;
  }

  @Transactional
  public void run(long fromId, long toId, long cents) {
    var ids = Stream.of(fromId, toId).sorted().toList();
    jdbc.queryForList(
        "select id from account where id in (?, ?) order by id for update",
        Long.class, ids.get(0), ids.get(1));
    jdbc.update(
        "update account set balance_cents = balance_cents - ? where id = ?",
        cents, fromId);
    jdbc.update(
        "update account set balance_cents = balance_cents + ? where id = ?",
        cents, toId);
  }
}

Do not place both methods on one object and call the transactional method through this; proxy-based interception may be bypassed. Production code should validate positive amounts, prevent overdrafts atomically, respect interruption, and inject a testable backoff policy.

Node.js example

With node-postgres, use one client per attempt and inspect PostgreSQL SQLSTATE:

const sleep = (ms) => new Promise((resolve) => setTimeout(resolve, ms));

export async function transfer(pool, fromId, toId, cents) {
  for (let attempt = 1; attempt <= 3; attempt += 1) {
    const client = await pool.connect();
    try {
      await client.query("BEGIN");
      const ids = [fromId, toId].sort((a, b) => a - b);
      await client.query(
        "select id from account where id = any($1) order by id for update",
        [ids]
      );
      await client.query(
        "update account set balance_cents = balance_cents - $1 where id = $2",
        [cents, fromId]
      );
      await client.query(
        "update account set balance_cents = balance_cents + $1 where id = $2",
        [cents, toId]
      );
      await client.query("COMMIT");
      return;
    } catch (error) {
      await client.query("ROLLBACK");
      if (error.code !== "40P01" || attempt === 3) throw error;
      const cap = 25 * 2 ** (attempt - 1);
      await sleep(Math.floor(Math.random() * cap));
    } finally {
      client.release();
    }
  }
}

Never reuse a client whose transaction is still aborted, and never mix statements from pool.query() into the transaction. Add an operation-level idempotency key if a caller may retry after an ambiguous network timeout.

Diagnosing production deadlocks

Log the database error code, transaction or trace identifier, operation name, attempt, affected entity IDs in non-sensitive form, and elapsed transaction time. Avoid logging full SQL parameters containing personal or secret data.

PostgreSQL can log lock waits and deadlock details through server configuration. pg_stat_activity and lock views help inspect current blockers, though the victim cycle may be gone by the time an operator queries it.

MySQL exposes the latest InnoDB deadlock through SHOW ENGINE INNODB STATUS; Performance Schema lock tables can help correlate waits. Enable broader deadlock logging carefully when needed.

Group incidents by query fingerprint and call path. A rising count may follow a traffic shift, missing index, deployment that reversed access order, or longer transaction duration. Fix the dominant graph instead of simply increasing retries.

Common mistakes

The first mistake is assuming a deadlock proves data corruption. The database aborts a victim to preserve correctness.

The second is retrying only the statement that failed. The whole transaction has been rolled back or invalidated.

The third is retrying every SQL error. Syntax errors, constraints, and authentication failures require different action.

The fourth is adding long random sleeps inside a transaction. Roll back and release locks before backoff.

The fifth is masking chronic contention with unlimited retries. Retries are a recovery layer, not capacity.

Practical checklist

Reproduce the conflicting paths with two real sessions.
Acquire shared resources in one documented order.
Index locking and update predicates.
Keep transactions short and free of remote calls.
Batch large operations with deterministic ordering.
Use SKIP LOCKED only for queue-like work.
Identify exact vendor deadlock and serialization error codes.
Roll back before sleeping or retrying.
Retry the whole idempotent transaction with a small bound and jitter.
Keep retries inside the caller deadline.
Track deadlocks, lock waits, retry success, exhaustion, and duration.
Capture engine diagnostics and query fingerprints.
Load test production-like contention.

Frequently asked questions

Can deadlocks be eliminated completely?

Simple workflows can remove known cycles through ordering, but robust applications still handle deadlock victims because schemas, queries, and concurrency evolve.

Should I increase the lock timeout?

Not as a first response. It may make requests slower without resolving a cycle. Diagnose lock duration and ordering.

Is a deadlock safe to retry?

Only if the complete operation can be rerun without duplicating external effects and the error is a documented transient category.

Does optimistic locking prevent deadlocks?

It can reduce explicit locking for some updates, but its SQL still acquires write/index locks and can participate in cycles.

Why add jitter?

Random delay prevents competing transactions from retrying in the same synchronized pattern and colliding repeatedly.

Sources and further reading

PostgreSQL, Explicit Locking and Deadlocks.
MySQL 8.4, Deadlocks in InnoDB.
Spring Framework, Transaction Management.
node-postgres, Transactions.

Read Retry Patterns Explained for backoff budgets, Idempotency in APIs Explained for safe repeated commands, and Database Isolation Levels Explained for concurrency anomalies. Database Transactions Explained covers transaction boundaries and rollback.