Deadlock investigation

Hook: Deadlock investigation is like finding two shoppers stuck in a doorway, each holding the other’s arm — interviewers love it because it tests whether you can debug a real production outage, not just write a query.

Question: How do you investigate a deadlock in SQL?

Answer: A deadlock happens when two transactions each hold a lock the other needs, so neither can move. To investigate it, I first confirm the victim error, then inspect the database’s lock and activity views or deadlock logs to rebuild the wait chain and identify the exact statements and resources involved. After that, I look for the root cause: inconsistent lock order, long transactions, missing indexes, or hidden locks from foreign keys or triggers.

Interview-Ready Answer: I’d start by confirming the deadlock error code and then reconstructing the wait chain from lock and activity metadata. In PostgreSQL, for example, I’d use pg_stat_activity, pg_locks, and pg_blocking_pids() to find the blocked session, the blocker, and the exact statements holding the locks. Once I see the cycle, I’d fix the code so every path takes locks in the same order, keep the transaction shorter, and add retry logic because one transaction will always be rolled back to break the deadlock.

What a deadlock really is

Detailed Explanation: A deadlock is a circular wait. Transaction A holds Lock 1 and needs Lock 2, while Transaction B holds Lock 2 and needs Lock 1. The database cannot let either continue, so the lock manager detects the cycle in the wait-for graph (a graph that shows which session is waiting on which other session) and aborts one transaction, called the victim, to break the cycle.

In PostgreSQL, deadlock checking happens after deadlock_timeout (default about 1 second). In SQL Server, the lock monitor checks for deadlocks and the engine reports error 1205; in PostgreSQL it is usually 40P01. The key interview point is that the database is not randomly failing — it is protecting forward progress by killing one participant.

How to investigate it under the hood

1. Confirm it was really a deadlock. A deadlock error is different from a simple long wait. Check the error code, the database log, and the victim transaction. If you only see slow query, it may just be blocking, not a cycle.
2. Find the blocked session and its blocker. In PostgreSQL, the live activity view tells you who is waiting, and pg_blocking_pids(pid) tells you who is blocking that session. This is usually the fastest way to start.
3. Rebuild the wait-for graph. A graph is just a map of dependencies: session A waits on B, B waits on C, and so on. A deadlock appears when the arrows loop back to the start. For a simple two-session deadlock, the graph is just A ↔ B.
4. Inspect the exact locks. Look at pg_locks for row locks, table locks, transaction ID locks, and whether a lock is granted or still waiting. Row locks often show as tuple or transaction waits; table-level locks show on the relation itself. The important question is not which query was slow, but which resource was contested.
5. Match the SQL text to the lock order. Deadlocks usually come from the same two statements executed in opposite order. For example, one code path updates accounts then transfers, while another updates transfers then accounts. That mismatch is the real bug.
6. Check hidden lock sources. Foreign keys, triggers, cascades, and secondary index updates can lock more than you expect. A simple UPDATE may also read parent rows to verify constraints, which can expand the lock footprint and create an unexpected cycle.
7. Fix the root cause, then add retry handling. The database will still occasionally choose a victim if two sessions race again. Your app should retry the whole transaction for deadlock-safe operations, usually with a small backoff and jitter, because the first attempt lost its work.

Deadlock vs related problems

Why deadlocks happen so often in real systems

Deadlocks usually show up under load, not in unit tests, because concurrent requests interleave in unlucky ways. The cost of detection is small — roughly proportional to the size of the wait graph, which is usually tiny compared with the cost of rolling back a transaction — but the business cost can be large because the victim loses all work in that transaction and may retry. That is why teams often see a spike in latency or checkout failures before they notice the deadlock log line.

How to reason about the fix

The safest fix is consistent lock ordering: every code path locks rows in the same order, such as ascending primary key. Other helpful fixes are shorter transactions, better indexes so the database touches fewer rows, and batching work so you do not hold locks while calling external services. If you are allowed to change behavior, FOR UPDATE SKIP LOCKED or NOWAIT can reduce waiting, but they change semantics, so use them only when your application can handle skipped rows or immediate failure.

Memory to keep: think same doors, same order. If every transaction grabs locks in the same order, there is no circle, so there is no deadlock.

Real-World Story: Imagine a checkout service for an online store. One transaction reserves inventory, another writes the order record, and a third updates loyalty points. Under peak traffic, two requests hit the same product at the same time: request A locks the orders row first, then wants inventory; request B locks inventory first, then wants orders.

At low traffic, you never notice. At 8 p.m. during a flash sale, the database starts aborting one side with deadlock errors, and the app shows intermittent failed checkouts even though the database is healthy. Logs might show deadlock detected, SQLSTATE 40P01, or 1205, and metrics will show a small spike in retry counts and p95 latency.

The bug is usually not the database is broken. It is that one code path updated tables in a different order than another path, or a trigger or foreign-key check quietly added another lock. The fix is to standardize the lock order, keep the transaction small, and retry the aborted request safely so the customer almost never sees the failure.

Follow-up & Tricky Questions:

• How do you tell a deadlock from normal blocking? A deadlock has a cycle, so one transaction is chosen as the victim and aborted. Normal blocking has a single waiter and a single holder, but no cycle.
• Which SQL views or logs do you check first? In PostgreSQL I start with pg_stat_activity, pg_locks, and pg_blocking_pids(), then I verify the server log for the deadlock report. In SQL Server, I would look at the deadlock graph from Extended Events and the 1205 error.
• Why do foreign keys and triggers matter? They can add hidden reads and locks that are not obvious from the top-level statement. That means two simple updates can still deadlock if the database must also lock parent rows or run extra code.
• How do you fix recurring deadlocks in application code? I make every code path lock rows in the same order, reduce transaction length, and avoid doing non-database work while holding locks. Then I add retry logic with a small backoff because one transaction will still be aborted occasionally under concurrency.
• What is the difference between lock_timeout and deadlock detection? A lock timeout is just a stopwatch: if you wait too long, the statement fails even if there is no cycle. Deadlock detection is cycle-based and exists to break circular waits quickly.
• Does a deadlock mean the database is unhealthy? No. A deadlock is often a sign of normal concurrency under load plus inconsistent access order. The system is working by detecting and resolving it, but your code still needs to be improved.
• Does adding retries solve the root cause? No. Retries hide the user-facing failure, but they do not remove the cycle. You still need lock order, shorter transactions, or less contention.
• Does FOR UPDATE always prevent deadlocks? No. It can even make deadlocks more visible because it forces earlier locking. It helps only when every code path takes the same locks in the same order.
• Can a deadlock involve more than two sessions? Yes. The cycle can be three or more sessions, such as A waiting on B, B waiting on C, and C waiting on A. Two-session deadlocks are common, but the investigation technique is the same: reconstruct the wait-for graph.

Common Mistakes:

• Mistake: Looking only at the victim query. Correction: The blocker and the lock order are usually the real root cause, so always inspect both sides of the wait chain.
• Mistake: Treating every slow query as a deadlock. Correction: Blocking without a cycle is not a deadlock; use logs and lock views to prove the cycle before changing code.
• Mistake: Raising the timeout and calling it fixed. Correction: A timeout only changes how long the session waits; it does not remove the circular dependency.
• Mistake: Adding retries without changing lock order. Correction: Retries reduce user-visible failures, but the same deadlock can keep happening under load unless the access pattern is made consistent.

Memory Hook: Two people, two doors, same order. If everyone grabs the same door first, nobody gets stuck in the hallway.

Cheat Sheet:

• Deadlock = circular wait.
• Victim gets rolled back to break the cycle.
• Check pg_stat_activity, pg_locks, and pg_blocking_pids() for live evidence.
• Logs matter because the deadlock may disappear before you inspect it.
• Fix the lock order, shorten the transaction, and add retries with backoff.
• Beware of triggers, foreign keys, and hidden locks.

Practice Tasks:

• Write a query that shows every blocked session and its blocker in your database.
• Take two update statements in your app and rewrite them so they lock rows in a single consistent order.
• Review one production table with foreign keys or triggers and list the extra locks it may take.