What causes a database deadlock, and how do you prevent it?

The Answer

Say this in the room. 45 seconds.

• A cycle in the waits-for graph: transaction 1 holds row A and wants row B, transaction 2 holds B and wants A. Identical in shape to a Java deadlock.
• The database resolves it for you. It detects the cycle, picks a victim, and rolls that transaction back — 40P01 on PostgreSQL, error 1213 on MySQL.
• That changes everything about how you handle it. The victim changed nothing and holds nothing, so retrying is safe — unlike a deadlocked Java thread, which cannot be rescued at all.
• Prevention is a consistent row order, exactly as in Java. ORDER BY id on a bulk update is a deadlock fix, not a cosmetic one.
• A missing index makes it far more likely, because a scan locks every row it examines, not the rows it matches. Two statements touching different rows can still collide.
• Keep transactions short, and never hold one open across a network call or user think-time.

Understand It

Everything below runs against a model of a lock manager, not a database. It holds exclusive row locks, builds the waits-for graph, and looks for a cycle — which is what InnoDB and PostgreSQL actually do. Transactions are scripted rather than threaded, so the interleaving is the code you read.

The cycle, and the database finding it

// Two transfers running at once, each locking its own account first.
// The order of these calls is the interleaving.
var db = new Db();
step(db, "T1", "acct:A");     // T1 holds A
step(db, "T2", "acct:B");     // T2 holds B
step(db, "T1", "acct:B");     // T1 waits for B
step(db, "T2", "acct:A");     // T2 would wait for A — and that closes the cycle

System.out.println();
System.out.println("  what the database did:");
db.log.forEach(line -> System.out.println("    " + line));

  T1   UPDATE acct:A    -> ok
  T2   UPDATE acct:B    -> ok
  T1   UPDATE acct:B    -> waits
  T2   UPDATE acct:A    -> DEADLOCK, this transaction is rolled back

  what the database did:
    T1 locks acct:A
    T2 locks acct:B
    T1 waits for acct:B held by T2
    cycle T2 -> T1 -> T2, victim T2

Nothing here is a bug you would catch in review. Each transaction updates two accounts and transfers between them. The orders are only wrong relative to each other, and in a real codebase they are two calls to the same method with the arguments swapped.

The difference from the Java version is the last line. A Java deadlock is permanent — the threads sit there until the process restarts, and no interrupt can free them. A database deadlock lasts as long as it takes the engine to notice: PostgreSQL checks after deadlock_timeout, one second by default; InnoDB maintains the waits-for graph continuously and usually fails the statement immediately.

Which victim gets killed is up to the engine. InnoDB prefers to roll back the transaction that has changed the fewest rows, on the reasonable theory that it is the cheapest to redo. You cannot rely on it being a particular one, which matters when you decide what to retry.

The fix is an order, not fewer locks

// Same two transfers. The only change: both lock the lower account id first.
var db = new Db();
System.out.println("  T1 handles A->B, T2 handles B->A, both take " + ordered("acct:B", "acct:A") + " in that order");
step(db, "T1", "acct:A");
step(db, "T2", "acct:A");     // T2 waits immediately, before holding anything
step(db, "T1", "acct:B");
db.commit("T1");
step(db, "T2", "acct:A");     // now free
step(db, "T2", "acct:B");
db.commit("T2");
System.out.println("  both transfers completed, no cycle was ever possible");

  T1 handles A->B, T2 handles B->A, both take [acct:A, acct:B] in that order
  T1   UPDATE acct:A    -> ok
  T2   UPDATE acct:A    -> waits
  T1   UPDATE acct:B    -> ok
  T1   COMMIT
  T2   UPDATE acct:A    -> ok
  T2   UPDATE acct:B    -> ok
  T2   COMMIT
  both transfers completed, no cycle was ever possible

Look at line three. T2 waits before it holds anything, which is the whole mechanism: a transaction that is not yet holding a lock cannot be part of a cycle. Waiting is fine. Waiting while holding something someone else wants is the problem.

The same amount of locking happens, for the same duration. The only change is agreeing on an order, and it makes the cycle structurally impossible rather than unlikely.

This is why ORDER BY id on a bulk UPDATE ... WHERE id IN (...) is a real fix. Two batch jobs updating overlapping id sets in whatever order the planner chose are the most common source of deadlocks in a system that has no obvious concurrent writers at all.

Why the victim can be retried, and a Java thread cannot

// The victim from the first example, retried. This is the difference that
// matters: the database rolled it back, so re-running it is safe.
var db = new Db();
step(db, "T1", "acct:A");
step(db, "T2", "acct:B");
step(db, "T1", "acct:B");
step(db, "T2", "acct:A");

System.out.println();
System.out.println("  T2 holds nothing and changed nothing — the rollback was atomic.");
db.commit("T1");
db.restart("T2");
step(db, "T2", "acct:B");
step(db, "T2", "acct:A");
db.commit("T2");

  T1   UPDATE acct:A    -> ok
  T2   UPDATE acct:B    -> ok
  T1   UPDATE acct:B    -> waits
  T2   UPDATE acct:A    -> DEADLOCK, this transaction is rolled back

  T2 holds nothing and changed nothing — the rollback was atomic.
  T1   COMMIT
  T2   BEGIN (retry)
  T2   UPDATE acct:B    -> ok
  T2   UPDATE acct:A    -> ok
  T2   COMMIT

Atomicity is what makes the retry correct. The victim was rolled back entirely — no partial write survives, no lock is still held, no half-finished state exists for the retry to trip over. Running it again is indistinguishable from having run it slightly later.

That is the single most useful thing to say about database deadlocks in an interview. It means a deadlock is an expected, retryable failure in a concurrent system, not an incident. A service that retries on 40P01 with backoff can run at a deadlock rate that would be alarming if nobody had thought about it, and users never see anything.

It is also why the two deadlocks want opposite responses. In Java you prevent, because you cannot recover. In a database you prevent and retry, because you can.

The missing index, which is where the surprising ones come from

var allRows = List.of("order:1", "order:2", "order:3", "order:4", "order:5");
var db = new Db();

// T1 updates one row by primary key. One row, one lock.
System.out.println("  T1: UPDATE orders SET ... WHERE id = 5   (uses the primary key)");
for (String row : db.rowsLockedBy("order:5", true, allRows)) step(db, "T1", row);

// T2 filters on a column with no index. The engine locks every row it EXAMINES,
// not the ones it matches — so a scan takes locks across the whole table.
System.out.println("  T2: UPDATE orders SET ... WHERE status = 'stale'   (no index on status)");
for (String row : db.rowsLockedBy("order:1", false, allRows))
    if (step(db, "T2", row) != Outcome.OK) break;

// T1's next statement wants a row T2 grabbed on its way past.
System.out.println("  T1: UPDATE orders SET ... WHERE id = 1");
step(db, "T1", "order:1");

System.out.println();
System.out.println("  Two statements touching different rows, and still a cycle:");
db.log.forEach(line -> System.out.println("    " + line));

  T1: UPDATE orders SET ... WHERE id = 5   (uses the primary key)
  T1   UPDATE order:5   -> ok
  T2: UPDATE orders SET ... WHERE status = 'stale'   (no index on status)
  T2   UPDATE order:1   -> ok
  T2   UPDATE order:2   -> ok
  T2   UPDATE order:3   -> ok
  T2   UPDATE order:4   -> ok
  T2   UPDATE order:5   -> waits
  T1: UPDATE orders SET ... WHERE id = 1
  T1   UPDATE order:1   -> DEADLOCK, this transaction is rolled back

  Two statements touching different rows, and still a cycle:
    T1 locks order:5
    T2 locks order:1
    T2 locks order:2
    T2 locks order:3
    T2 locks order:4
    T2 waits for order:5 held by T1
    cycle T1 -> T2 -> T1, victim T1

Read what these two statements actually do. T1 updates row 5. T2's WHERE status = 'stale' matches row 1. The rows they change do not overlap at all, and they deadlocked.

The cause is on the second line of T2's output: with no index on status, the engine cannot find matching rows without reading them, and a row it reads inside a write transaction is a row it locks. The scan took locks on 1, 2, 3 and 4 on its way to discovering they did not match.

This is why deadlocks so often appear after a table grows or a query plan changes, with no code change at all. It is also why the first thing to do with a deadlock report is run EXPLAIN on both statements: if either is doing a sequential scan, the index is the fix and the lock ordering is a distraction.

The model locks every row on a full scan. Real engines vary — PostgreSQL's MVCC means readers do not block, and it locks rows it actually updates, while InnoDB at repeatable read takes next-key locks on the index range it scans and releases non-matching rows at read committed. The direction is the same everywhere and the details are worth checking for your engine: the narrower the access path, the fewer rows get locked.

Reference

The correct implementation, the configuration, and the migration path. Copy from here.

Recognising it

// Spring maps all of them to one exception, which is what you catch.
catch (DeadlockLoserDataAccessException e) { /* safe to retry */ }

// Distinguish it from a lock WAIT timeout, which is a different problem:
//   MySQL 1205 / PostgreSQL 55P03 — no cycle, just a holder that is too slow.
// Retrying that one without fixing the slow holder makes things worse.

A deadlock and a lock wait timeout look identical from the application and want opposite responses. A deadlock says two transactions disagreed on order — retry. A timeout says someone held a lock too long — find out who, and shorten it.

Retrying, which is the point

@Retryable(
    retryFor = { DeadlockLoserDataAccessException.class, CannotAcquireLockException.class },
    maxAttempts = 3,
    backoff = @Backoff(delay = 50, multiplier = 2, random = true))   // jitter, or they collide again
@Transactional
public void transfer(long from, long to, BigDecimal amount) { ... }

Two rules. The retry must wrap the whole transaction — a retry inside it re-runs against a transaction that no longer exists. And the operation must be idempotent or re-derivable, which it usually is precisely because the rollback was atomic; the danger is side effects that were not rolled back, such as an email sent or a message published mid-transaction.

// Do not do this. The publish is not rolled back when the transaction is.
@Transactional
public void placeOrder(Order order) {
    repository.save(order);
    kafka.send("orders", order);      // a retry sends it twice
}

Preventing it

-- 1. A consistent order, everywhere. This is the fix that scales.
UPDATE account SET balance = balance - ? WHERE id = LEAST(?, ?);
UPDATE account SET balance = balance + ? WHERE id = GREATEST(?, ?);

-- 2. Bulk updates: make the order explicit rather than the planner's choice.
UPDATE orders SET status = 'archived'
WHERE id IN (SELECT id FROM orders WHERE created < ? ORDER BY id FOR UPDATE);

-- 3. Take the locks you need up front, in order, rather than discovering them.
SELECT * FROM account WHERE id IN (?, ?) ORDER BY id FOR UPDATE;

-- 4. Index whatever you filter on in a write. An unindexed WHERE in an UPDATE
--    locks rows it does not even change.
CREATE INDEX idx_orders_status ON orders (status);

// 5. Shortest transaction that is still correct. Everything else moves out.
public void process(Order order) {
    var enrichment = remoteService.fetch(order.id());   // OUTSIDE
    repository.saveWithEnrichment(order, enrichment);   // the transaction
}

The Java deadlock entry's rule applies unchanged: never hold a lock across a call you do not control. In a database that includes holding a transaction open while waiting for a user, a remote service, or a queue.

Investigating one you did not see

-- MySQL: the most recent deadlock, with both transactions and their locks.
SHOW ENGINE INNODB STATUS;                 -- read the LATEST DETECTED DEADLOCK section
SET GLOBAL innodb_print_all_deadlocks = ON;  -- log every one, not just the last

-- PostgreSQL: they are logged by default. Add the statement for context.
-- log_lock_waits = on, deadlock_timeout = 1s
SELECT * FROM pg_locks JOIN pg_stat_activity USING (pid) WHERE NOT granted;

Read the two statements in the report and ask three questions in order. Do they take rows in different orders? Does either do a sequential scan? Is either transaction longer than it needs to be? Nearly every deadlock is answered by one of those three.

Scenarios

Real situations, with the decision and the argument.

1. Deadlocks spike every night at 2am, and nobody is using the system.

A batch job, and probably two of them overlapping. Batch updates take many row locks in whatever order the query plan produced, so two jobs over overlapping id ranges deadlock readily even though neither is "concurrent" in the way people picture.

Add ORDER BY id to the bulk updates so both jobs take rows in the same order, and check whether the two jobs need to overlap at all — serialising them is often simpler and costs nothing at 2am. If they must overlap, partition the id space between them so they do not touch the same rows.

2. A deadlock appeared after a table grew, with no code change.

The plan changed. A query that used an index when the table was small switched to a sequential scan as it grew, and a scan inside a write transaction locks rows it examines rather than only the rows it changes — so a statement that used to touch one row now touches many.

EXPLAIN both statements from the deadlock report before touching lock ordering. If either shows a sequential scan, add the index; the ordering was never the problem. This is also the argument for alerting on plan changes rather than only on errors.

3. Someone wants to retry every failed transaction automatically.

Right for deadlocks, wrong as a blanket rule. A deadlock victim was rolled back atomically, so retrying is safe and is the correct response. A lock wait timeout is a different error with the same shape, and retrying it hammers a database that already has a slow lock holder.

Retry on the specific deadlock exception, with a small attempt limit and jittered backoff, and alert on the rate rather than treating it as invisible. A deadlock rate that is climbing is telling you about a real ordering problem even while the retries hide it from users.

4. Retrying is safe, except one transaction sends an email.

Then it is not safe, and the fix is not in the retry logic. The rollback undoes the database work and cannot undo the email, so a retried transaction sends two.

Move the side effect outside the transaction, or make it transactional — write it to an outbox table in the same transaction and let a separate process publish it. That is the general answer for anything with an external effect inside a retryable transaction, and it is worth raising as a design point rather than an implementation detail.

5. A team proposes serialising all writes through one worker to end deadlocks.

It does end them, and it ends throughput too. Every write now queues behind every other write regardless of whether they touch related rows, which trades a solvable ordering problem for a permanent capacity ceiling.

Worth taking seriously only when the contention is genuinely on one hot row — a counter, a sequence, a single inventory item — where the queue exists anyway and making it explicit is honest. For general writes across many rows, consistent ordering plus retries gets the same correctness at full concurrency.

Interviewer's Next Move

The question after the question. This is what the round is actually testing.

1. "What causes a database deadlock?" A cycle in the waits-for graph — two transactions each holding a row lock the other needs. Structurally identical to a Java deadlock; the difference is what happens next.

2. "What does the database do about it?" Detects the cycle, picks a victim, and rolls that transaction back with an error — 40P01 on PostgreSQL, 1213 on MySQL. The other transaction proceeds normally.

3. "So how is that different from a Java deadlock?" A Java deadlock is permanent — you cannot interrupt a thread blocked on a monitor, so the process restarts. A database deadlock resolves itself in under a second, and because the rollback is atomic the victim is safe to retry.

4. "How do you prevent them?" A consistent order on the rows every transaction touches, usually by primary key. ORDER BY id on a bulk update, or SELECT ... FOR UPDATE ... ORDER BY id to take the locks up front. A transaction that is not yet holding a lock cannot be in a cycle.

5. "Why would a missing index cause deadlocks?" Because a write statement locks the rows it examines, not just the rows it matches. Without an index the engine scans, so a statement that changes one row can hold locks across the table — and two statements touching different rows can still form a cycle.

6. "You get a deadlock exception in production. First thing you do?" Read the deadlock report — SHOW ENGINE INNODB STATUS or the PostgreSQL log — and EXPLAIN both statements. Ask whether they order rows differently, whether either does a sequential scan, and whether either transaction is longer than it needs to be.

7. "Is retrying always safe?" Safe with respect to the database, because the rollback was atomic. Not safe if the transaction had side effects that were not rolled back — an email, a published message, a call to a payment provider. Move those outside, or into an outbox.

8. "How is a lock wait timeout different?" No cycle. One transaction held a lock longer than the other was willing to wait. It looks the same to the application and wants the opposite response: find the slow holder rather than retrying into it.

9. "What if the deadlock is on one hot row?" Ordering does not help, because there is only one row to order. Either make the update a single statement so there is no window (SET count = count + 1), or accept the serialisation and make it explicit — a queue, or sharding the counter into buckets that are summed on read.

Code traps

Trap A — predict before you run:

@Transactional
public void transfer(long fromId, long toId, BigDecimal amount) {
    Account from = repo.findByIdForUpdate(fromId);
    Account to = repo.findByIdForUpdate(toId);
    from.debit(amount);
    to.credit(amount);
}

Trap B:

@Retryable(retryFor = DeadlockLoserDataAccessException.class, maxAttempts = 5)
@Transactional
public void archiveOrders(List<Long> ids) {
    for (Long id : ids) {
        jdbc.update("UPDATE orders SET archived = true WHERE id = ?", id);
    }
}

Trap C:

@Transactional
public void confirmBooking(long bookingId) {
    Booking b = repo.findByIdForUpdate(bookingId);
    paymentGateway.charge(b.getCardToken(), b.getAmount());   // 3 seconds
    b.setStatus(CONFIRMED);
}

Common wrong answers

Check Yourself

Q1. A Java deadlock and a database deadlock have the same shape. Why do they need opposite responses?

Q2. Two statements update rows that do not overlap, and they deadlock. How?

Q3. Your retry-on-deadlock works perfectly, except the transaction also publishes a message to Kafka. What breaks?

Practice