Deadlocks

Hook: Interviewers love deadlocks because they test whether you understand that SQL is not just about storing data — it is about coordinating many transactions at once.

Question: What is a deadlock in SQL?

Answer: A deadlock happens when two or more transactions each hold a lock the other one needs, so nobody can move forward. Think of it like two cars meeting head-on in a one-lane bridge: both are waiting, and neither can pass. Databases detect this cycle and abort one transaction so the others can continue.

Interview-Ready Answer: In SQL, a deadlock is a circular wait between transactions. For example, Transaction A locks row 1 and wants row 2, while Transaction B locks row 2 and wants row 1. The database detects that cycle and kills one transaction as the victim, so the other one can finish. In practice, I prevent deadlocks by locking rows in a consistent order, keeping transactions short, and retrying the failed transaction safely.

What a deadlock really is

A lock is the database saying, “this row or table is busy right now.” A deadlock is not just waiting; it is a cycle of waiting. Transaction A is waiting for something held by B, and B is waiting for something held by A. Because each one is holding the thing the other needs, the system cannot resolve it by waiting longer.

How it works under the hood

1. Transaction A reads or updates row 1 and acquires a lock on it.
2. Transaction B reads or updates row 2 and acquires a lock on it.
3. A then tries to lock row 2 and must wait, because B already holds it.
4. B then tries to lock row 1 and must wait, because A already holds it.
5. The database builds a wait-for graph — a simple map of “who is waiting for whom.”
6. If the graph contains a cycle, the engine picks a victim transaction, aborts it, and releases its locks.
7. The surviving transaction continues and finishes, which breaks the cycle.

The victim is usually chosen by cost: engines try to abort the transaction that is cheapest to roll back, or the one that has done the least work. The exact rule depends on the database engine, but the idea is always the same: sacrifice one transaction to let the rest complete.

Deadlock vs. similar problems

A deadlock is different from a timeout. In a timeout, a transaction may simply have waited too long. In a deadlock, the database knows waiting will never help, because the wait is circular.

When and why deadlocks happen

• Hot rows: many sessions update the same few rows, such as account balances or inventory.
• Different lock order: one code path updates rows in order 1→2, another updates 2→1.
• Broad predicates: a query locks more rows than expected because the filter is not selective enough.
• Foreign keys, triggers, and cascades: one statement can quietly touch extra tables and create a hidden lock chain.

How to prevent them

1. Lock rows in a consistent order. If every transaction touches accounts in ascending id order, you remove the cycle.
2. Keep transactions short. The longer a transaction holds locks, the bigger the chance of overlap.
3. Use good indexes. Better indexes reduce the number of rows scanned and locked, especially for update and delete statements.
4. Retry on deadlock. A deadlock is usually a transient concurrency issue, so a safe retry with backoff is normal.
5. Make writes idempotent. If you retry, the transaction should produce the same final result without double-charging or double-inserting.

Performance note: deadlock detection itself is usually fast; the real cost is wasted work from the aborted transaction and the extra retry. In OLTP systems, deadlocks often show up only under concurrency spikes, not during single-user testing.

Edge cases interviewers like

• Even a single UPDATE can deadlock if it touches multiple rows or tables indirectly through triggers or foreign keys.
• Two transactions can deadlock on different tables, not just the same table.
• A long report running under a stricter isolation level can hold locks long enough to increase deadlock risk.
• Indexes do not magically remove deadlocks; they mainly narrow the locked set and shorten lock duration.

Memory note: if you can explain the cycle, the wait-for graph, and the victim rollback, you understand the core of deadlocks.

Real-world story: checkout service under load

Imagine a checkout service in an e-commerce app. One transaction reserves inventory, another writes the order row, and a third updates the customer’s loyalty points. Under normal traffic everything looks fine, but during a flash sale, two requests can touch the same customer and inventory rows in different orders.

What goes wrong: request A locks the inventory row first and then waits on the customer row, while request B locks the customer row first and then waits on the inventory row. The database detects the deadlock, aborts one request, and the app sees an error such as “deadlock detected” or a vendor-specific lock error. Users may see a failed checkout, a retry spinner, or a duplicate attempt if the application retries without idempotency. In logs you often see a lock-wait message, a victim rollback, and a spike in latency at the same minute.

The lesson: the bug is rarely “the database is broken.” It is usually a code-path mismatch: two parts of the app touched the same rows in different orders. The fix is to make every path follow the same order and to retry the failed transaction safely.

Follow-up & Tricky Questions:

• How do you prevent deadlocks? Use a consistent lock order, keep transactions short, reduce the number of rows touched, and retry the whole transaction on a deadlock error.
• How does the database detect a deadlock? It tracks who is waiting on whom, usually as a wait-for graph, and looks for cycles. A cycle means the wait cannot resolve by itself.
• What should the application do after a deadlock? Roll back the failed transaction and retry it safely, usually with a small backoff. The retry must be idempotent so repeating it does not double-apply business changes.
• Can indexes eliminate deadlocks? No. Indexes usually reduce the amount of work and the number of rows locked, but two transactions can still deadlock if they lock the same rows in opposite order.
• Why does lock order matter so much? Because deadlocks are cycles. If every transaction acquires locks in the same order, you remove the cycle and the deadlock risk drops sharply.
• Does a deadlock mean the database is unhealthy? Not necessarily. A few deadlocks in a busy OLTP system are normal; frequent deadlocks usually point to a design problem or a hot spot.
• Tricky: will waiting longer fix a deadlock? No. Waiting longer only helps with ordinary contention. In a deadlock, waiting forever still cannot break the cycle, so the DB must abort a victim.
• Tricky: is a deadlock the same as a timeout? No. A timeout is about duration; a deadlock is about circular dependency. The fixes overlap, but the problems are different.
• Tricky: can a single statement deadlock? Yes, if it touches multiple rows or tables internally, or if triggers and foreign keys cause extra lock acquisitions.

Common Mistakes:

• Mistake: Saying deadlocks are just “slow queries.” Correction: Deadlocks are circular waits; speed may influence them, but the core issue is lock dependency.
• Mistake: Assuming the DB will resolve it automatically without impact. Correction: The database resolves it by killing one transaction, so the application must be ready to retry.
• Mistake: Updating rows in different orders across code paths. Correction: Standardize a single order everywhere, even across services.
• Mistake: Thinking indexes alone solve deadlocks. Correction: Indexes help, but lock order and transaction length are usually the bigger fixes.

Memory Hook: “Two people, one hallway, opposite doors.” Each person holds a door and waits for the other door to open. Nobody can move until one person steps back — that is the aborted victim.

Cheat Sheet:

• Deadlock = circular wait between transactions.
• DB detects a cycle in the wait-for graph.
• One transaction is rolled back as the victim.
• Prevent it with consistent lock order.
• Keep transactions short and index well.
• Always retry safely after deadlock.

Practice Tasks:

• Write two UPDATE statements that would be safe if run in the same order.
• List three reasons a production checkout system might hit deadlocks.
• Design a retry strategy for a transaction that can be safely run twice.