What is a deadlock and how do you prevent it? | Interview Question

Q: What is a deadlock and how do you prevent it?

**Target Audience:** Developers who have seen `deadlock detected` in a log and want to understand it from zero; candidates who need the precise detection and prevention story for an interview. **Difficulty:** Hard **Engines:** PostgreSQL 16.15 (two real `psql` sessions for the deadlock itself, the verifier for the plan and `pg_locks` examples) and SQLite 3.45 (sql.js 1.10.2 for the runnable examples, Python sqlite3 3.45.1 for the two-connection check). MySQL and SQL Server facts are cited, not run. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result, plan and error message below was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First Two people are cooking in a small kitchen with one knife and one cutting board. Asha grabs the knife and reaches for the board. At the s

The problem

Question presented to candidate: "Two of our payment workers sometimes fail with a 'deadlock detected' error. What is a deadlock, how does the database find and break one, and what would you change in the code so it stops happening?"

What a strong answer should cover:

Definition: two or more transactions each hold a lock the other needs, so every one of them waits forever; a cycle in the waits-for graph
The difference from ordinary blocking: a lock wait ends when the holder commits, a deadlock never ends on its own
Detection: PostgreSQL runs its deadlock check after deadlock_timeout (default 1s) and aborts one transaction with SQLSTATE 40P01; InnoDB detects immediately by default (error 1213); SQL Server picks a victim with error 1205
Prevention: lock rows in one consistent order (sort the keys, ORDER BY id FOR UPDATE), keep transactions short, take the strongest lock first, touch fewer rows (good indexes)
Handling: deadlocks can never be ruled out completely, so the application retries the whole transaction on 40P01; lock_timeout bounds ordinary waits
Diagnosis: the error DETAIL, the server log, pg_locks and pg_blocking_pids()

Clarifying questions expected:

Which database engine, and do we have the full error text or server log for one occurrence?
Do the two workers touch the same rows in a different order (for example a transfer A to B and a transfer B to A)?

Code / implementation expected: Yes — reproduce the deadlock with two sessions, then fix it by locking rows in a consistent order.

Schema used by the examples:

deadlock

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who have seen deadlock detected in a log and want to understand it from zero; candidates who need the precise detection and prevention story for an interview. Difficulty: Hard Engines: PostgreSQL 16.15 (two real psql sessions for the deadlock itself, the verifier for the plan and pg_locks examples) and SQLite 3.45 (sql.js 1.10.2 for the runnable examples, Python sqlite3 3.45.1 for the two-connection check). MySQL and SQL Server facts are cited, not run.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result, plan and error message below was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

Two people are cooking in a small ki

The solution is hidden so you can try first

16 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLPick one lock order for every transfer: lowest account id first

SQLFind the cycle in a waits-for graph, the way a deadlock detector does

PostgreSQL: ORDER BY ... FOR UPDATE locks rows in sorted order (LockRows above Sort)

EXPLAIN (COSTS OFF)
SELECT id, balance
FROM accounts
WHERE id IN (2, 1)
ORDER BY id
FOR UPDATE;

PostgreSQL: the locks one UPDATE holds until COMMIT, from pg_locks

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SELECT locktype, relation::regclass AS relation, mode, granted
FROM pg_locks
WHERE pid = pg_backend_pid()
  AND locktype IN ('relation', 'transactionid')
  AND (relation IS NULL OR relation::regclass::text LIKE 'accounts%')
ORDER BY locktype, relation::regclass::text;
ROLLBACK;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.