How do you prevent deadlocks with thousands of concurrent transactions?

Question

IT Interview Practice · Accepted Answer

**Acquire locks in a consistent global order, keep transactions short, lower isolation where it's safe, and retry the loser when a deadlock still happens.** A deadlock is a cycle: transaction A holds row 1 and waits for row 2, while B holds row 2 and waits for row 1 — neither can proceed, so the DB kills one.

## The cycle you must break

```text
   Tx A                              Tx B
   holds row 1  ──── waits ────▶ row 2 (held by B)
        ▲                              │
        │ waits                        │ holds
        └────────── row 1 ◀────────────┘
                (cycle = deadlock; DB aborts one victim)
```

## Strategies

- **Consistent lock ordering.** If every transaction always locks rows in the same order (e.g. ascending primary key), a cycle is impossible — A and B both grab row 1 first, so one simply waits. This is the single biggest lever. A classic bug: a money transfer that locks accounts in `(from, to)` order deadlocks with the reverse transfer; always lock by `min(id), max(id)`.
- **Short transactions.** Hold locks for as little time as possible — do validation, network calls, and reads **before** `BEGIN`, then lock, write, and `COMMIT` fast. Long transactions widen the window for cycles and lock waits.
- **Lower isolation where safe.** `SERIALIZABLE` takes more/range locks and deadlocks more; `READ COMMITTED` (Postgres default) locks less. Use the weakest isolation that still meets correctness.
- **Retry on deadlock.** Deadlocks are transient. Catch the deadlock error (Postgres `40P01`, MySQL `1213`) and retry the whole transaction with small backoff. Some contention is unavoidable at scale, so retry is a safety net, not a substitute for ordering.

## Interview perspective

The interviewer checks whether you know deadlocks are **prevented by design, not just caught**. Naming "retry" alone is a weak answer — the great answer leads with **consistent lock ordering** and **short transactions**, then adds retry for the residual. A common trap: assuming the app must detect deadlocks; in practice the **DB detects the cycle and aborts a victim** for you — your job is ordering plus a retry loop.

**Trade-offs:** strict ordering can be hard when access patterns are dynamic or span tables; lower isolation risks anomalies (non-repeatable reads) you must reason about. Retry loops must be **idempotent** and capped, or a hot conflict turns into a retry storm. Deadlock prevention is really lock-discipline plus a bounded retry, not a single silver bullet.

The cycle you must break

Strategies

Interview perspective

Related questions