Database Deadlocks: Causes, Detection, and Prevention

Two transactions can each hold a lock the other needs. Neither can continue, so the database detects the cycle and aborts one transaction. That is a database deadlock: a correctness mechanism working as designed, but a production signal that the access pattern needs attention.

This guide explains database deadlocks after optimistic vs pessimistic locking and before database replication. Read database transactions and ACID properties for the prerequisite guarantees, then use the Backend Interview Mastery series, Backend Developer Interview Guide, and Database tag for the full path.

Table of Contents

How a Deadlock Forms

Transaction A locks account 10, then requests account 20. Transaction B locks account 20, then requests account 10. The wait-for graph contains a cycle, so an engine selects a victim, rolls it back, and releases its locks.

flowchart TD
    A[Transaction A holds account 10] --> B[Waits for account 20]
    C[Transaction B holds account 20] --> D[Waits for account 10]
    B --> C
    D --> A
    A --> E[Database aborts one victim]
    C --> E

Deadlock is not the same as ordinary blocking. Blocking has a path to progress when the holder commits; a deadlock has no path until the database aborts work.

Reproduce the Cycle With Two Sessions

Use a disposable PostgreSQL database and open two SQL sessions. The example assumes two existing rows in accounts; do not run it against a live payment table just to observe an error.

CREATE TABLE accounts (
  account_id integer PRIMARY KEY,
  balance_cents integer NOT NULL
);

INSERT INTO accounts (account_id, balance_cents)
VALUES (10, 10000), (20, 10000);

Run this in Session A, then stop after the first SELECT:

BEGIN;
SELECT * FROM accounts WHERE account_id = 10 FOR UPDATE;
-- Now wait for Session B to lock account 20.
SELECT * FROM accounts WHERE account_id = 20 FOR UPDATE;
COMMIT;

Run this in Session B before Session A asks for account 20:

BEGIN;
SELECT * FROM accounts WHERE account_id = 20 FOR UPDATE;
-- Now Session A is waiting for this row.
SELECT * FROM accounts WHERE account_id = 10 FOR UPDATE;
COMMIT;

Each session owns one row and waits for the other. PostgreSQL detects the cycle and aborts one transaction with a deadlock error; the other can continue. Reverse the second session so both sessions lock account 10 and then account 20, and the cycle disappears. The production rule is therefore to sort resource IDs before locking them, not to rely on which request arrives first.

Deadlock vs Lock Wait vs Serialization Failure

Do not label every transient database error a deadlock. A lock timeout, a deadlock victim, and a Serializable retry each have different evidence and may have different error codes in PostgreSQL, MySQL, and SQL Server.

Detect and Recover Safely

Capture the database deadlock report, SQL statements, lock types, affected rows, and transaction duration. In PostgreSQL, enable useful lock-wait and deadlock logging for the environment, then correlate the server log with pg_stat_activity and pg_locks. In MySQL InnoDB, inspect SHOW ENGINE INNODB STATUS and the error log. In SQL Server, use the deadlock graph captured by Extended Events. These are diagnosis tools, not interchangeable SQL commands.

Retry only the transaction that was aborted, with a small bounded backoff and idempotent operation identity. A blind infinite retry turns contention into a retry storm. The retry must start a new transaction and repeat every read, because the aborted transaction has no usable partial state.

function isPostgresDeadlock(error: unknown): boolean {
  return (
    typeof error === "object" &&
    error !== null &&
    "code" in error &&
    (error as { code?: string }).code === "40P01"
  );
}

function sleep(milliseconds: number): Promise<void> {
  return new Promise(resolve => setTimeout(resolve, milliseconds));
}

async function runWithDeadlockRetry<T>(
  operationId: string,
  work: (operationId: string) => Promise<T>
) {
  const maxAttempts = 3;

  for (let attempt = 1; attempt <= maxAttempts; attempt += 1) {
    try {
      return await work(operationId); // work opens, commits, or rolls back one local transaction
    } catch (error) {
      if (!isPostgresDeadlock(error) || attempt === maxAttempts) throw error;

      await sleep(20 * attempt + Math.floor(Math.random() * 20));
      // operationId is stored under a unique constraint inside work, so replay is safe.
    }
  }

  throw new Error(`Unreachable retry state for ${operationId}`);
}

This example intentionally recognizes only PostgreSQL SQLSTATE 40P01; use the documented deadlock code for a different engine rather than matching an error message. The work function should write the operation ID in the same transaction as the business result, so a network retry cannot apply a transfer or reservation twice.

Prevent the Common Patterns

Always lock equivalent resources in the same order, keep transactions short, use indexes that narrow lock scope, and avoid holding a transaction open during network calls. Replace read-then-write counters with atomic conditional updates where possible. Test the exact two-session interleaving; a single-threaded test cannot expose a cycle.

Stable order must apply to every path. A transfer endpoint that locks source then destination by request order can deadlock with another transfer in the reverse direction; sort both account IDs first. Indexes help by reducing the rows examined and locked, but they do not replace a consistent application-level order. Avoid retrying an operation that has already sent an external side effect unless an idempotency key and reconciliation process make that replay safe.

Interview Questions

1. How do you prevent deadlocks?

I first impose a stable lock order across every code path, then shorten transactions and inspect plans so fewer rows are locked. I still handle a deadlock victim error with a bounded retry because prevention reduces risk but cannot prove an application will never encounter one.

2. Should an application retry every database error?

No. Retry only documented transient conflicts such as a deadlock victim or serialization failure, and only when the operation is idempotent. Validation failures, malformed SQL, and constraint violations need a different response.

3. How is a deadlock different from a lock timeout?

A deadlock is a proven cycle: no participant can proceed until the database aborts one. A lock timeout is a policy limit on waiting for a resource that may eventually become free. Investigate both, but a timeout can indicate a long transaction while a deadlock points directly to inconsistent resource acquisition or an interaction between lock modes.

4. Why can adding an index reduce deadlocks without eliminating them?

An index can narrow a predicate so the engine examines and locks fewer rows, reducing the chance that transactions overlap. It cannot make opposite lock ordering safe: two transactions can still deadlock while locking exactly two indexed rows in reverse order. Verify the query plan and then enforce a stable order in application code.

5. What must be true before automatically retrying a deadlock victim?

The retry must rerun the entire transaction, be bounded, and preserve exactly-once business intent with an idempotency key or unique operation record. It must not repeat an external payment, email, or message publication blindly. After the retry budget is exhausted, return a retryable outcome or queue controlled work and alert on the contention pattern.

Conclusion

Deadlocks are cycles in resource waiting. Fix the access order and transaction scope, preserve a safe retry path, and use deadlock reports as evidence instead of guessing.

References

1. Explicit Locking - PostgreSQL Documentation https://www.postgresql.org/docs/current/explicit-locking.html
2. Analyze and Prevent Deadlocks - Microsoft Learn https://learn.microsoft.com/en-us/azure/azure-sql/database/analyze-prevent-deadlocks

YouTube Videos

1. “SQL Server Deadlock: Causes, Detection & Solutions” https://www.youtube.com/watch?v=3EPl5a5KkOQ