SQL-Engineering-Handbook

← Module Home

Lesson 04 — Deadlocks

Module 17: Transactions & Concurrency Control · Lesson 4 of 7

---

1. Introduction

A deadlock is two (or more) transactions each holding a lock the other needs, with neither able to proceed. Locking (Lesson 03) makes deadlocks possible; this lesson covers how they happen, how MySQL resolves them automatically, and — more importantly — how to design around them so they’re rare rather than routine.

2. Learning Objectives

• Explain the cyclic-wait condition that defines a deadlock
• Read MySQL’s SHOW ENGINE INNODB STATUS deadlock report
• State MySQL’s automatic deadlock resolution behavior
• Apply the primary engineering practice that prevents most deadlocks: consistent lock ordering

3. Why This Exists

Locking exists to make concurrent writes safe (Lesson 03), but locking two or more resources across multiple statements creates a new risk: if two transactions each need both resources but acquire them in opposite order, they can each end up holding one and waiting for the other — permanently, without intervention. A database must detect this and break the cycle, because neither transaction will ever release its lock on its own.

4. Business Motivation

A “transfer budget between departments” operation that locks the source department row, then the destination department row, will deadlock against a second transfer running in the opposite direction (source and destination swapped) if both transactions started at roughly the same time and each grabbed its first lock before either could grab its second. This isn’t a rare edge case in any system with symmetric operations between two resources — it’s a predictable consequence of not imposing a consistent lock order.

5. The Deadlock Scenario

-- Session A                                    -- Session B
START TRANSACTION;                              START TRANSACTION;
UPDATE departments SET dept_name = 'A1'         UPDATE departments SET dept_name = 'B1'
  WHERE dept_id = 4;   -- locks dept_id=4          WHERE dept_id = 7;   -- locks dept_id=7
                                                 
UPDATE departments SET dept_name = 'A2'         UPDATE departments SET dept_name = 'B2'
  WHERE dept_id = 7;   -- BLOCKS, waiting          WHERE dept_id = 4;   -- BLOCKS, waiting
  -- (Session B holds this lock)                   -- (Session A holds this lock)

-- Neither can proceed: A waits for the lock B holds on dept_id=7,
-- B waits for the lock A holds on dept_id=4. This is the cycle.
-- MySQL's deadlock detector identifies this and kills ONE of the
-- two transactions automatically (typically the one with less
-- rollback work), returning:
-- ERROR 1213 (40001): Deadlock found when trying to get lock;
-- try restarting transaction

The survivor’s transaction proceeds normally once the victim rolls back and releases its locks. The victim’s application code must catch this error and decide whether to retry.

6. Reading the Deadlock Report

SHOW ENGINE INNODB STATUS\G

Look at the LATEST DETECTED DEADLOCK section: it lists each transaction involved, the statement it was running, the lock(s) it held, and the lock it was waiting for — reading these two transaction blocks side by side directly shows you the cycle (what each held vs. what each wanted), which is the fastest way to identify exactly which two resources need lock-order discipline.

7. Engineering Notes

• Acquire locks in a consistent order across every code path. If any operation ever needs to lock both a department row and an employee row, always lock departments before employes (or always the reverse) — pick one order and enforce it everywhere. This single practice prevents the overwhelming majority of real-world deadlocks.
• Keep transactions short (Lesson 01, §8) — the longer a transaction holds its first lock before requesting its second, the wider the window for another transaction to grab the second lock first and create a cycle.
• Always implement retry logic for deadlock victims. A deadlock error is not “your query is wrong” — it’s an expected, occasional outcome of concurrent access that the application layer must be prepared to retry (typically with a brief backoff), not surface as an unhandled failure.
• Access rows in the same order a natural key or index would sort them, where feasible (e.g., always process emp_id values in ascending order within a multi-row operation) — this is a specific case of “consistent lock ordering.”

8. MySQL / PostgreSQL / SQL Server Notes

MySQL: Detects deadlocks quickly (in normal operation, near- immediately upon the cyclic wait forming) via InnoDB’s built-in deadlock detector, and automatically rolls back one transaction — typically the one InnoDB estimates has done less work, to minimize rollback cost. No configuration is required for basic detection; innodb_deadlock_detect can be disabled in very high-concurrency setups where the detector itself becomes a bottleneck, at the cost of then relying purely on lock wait timeout instead.

PostgreSQL: Also detects deadlocks automatically and aborts one transaction with a deadlock_detected error, conceptually similar, though implementation details (detection interval configurable via deadlock_timeout) differ from MySQL’s near-immediate detection.

SQL Server: Also has automatic deadlock detection, and additionally lets you set DEADLOCK_PRIORITY per session to influence which transaction SQL Server chooses as the victim — a level of control MySQL doesn’t expose the same way.

9. Common Mistakes

1. Treating every deadlock as a bug to eliminate entirely. In a system with real concurrent access to shared resources, occasional deadlocks are expected, not a sign something is fundamentally broken — the goal is minimizing frequency (via lock ordering, short transactions) and handling them gracefully (via retry), not assuming zero deadlocks is achievable or even meaningful as a goal.
2. No retry logic on deadlock errors — letting ERROR 1213 surface as an unhandled application error instead of catching it and retrying the transaction.
3. Inconsistent lock ordering across different features/code paths that happen to touch the same two tables — this is usually not visible from reading any single code path in isolation; it only shows up when you compare lock-acquisition order across all paths that touch the same resources.
4. Confusing a deadlock with a lock wait timeout (Lesson 03, §7) — they’re different errors with different causes: a lock wait timeout is one transaction waiting too long for a lock that will eventually free up (no cycle); a deadlock is a cycle that can never resolve on its own.

10. Edge Cases

• Deadlock involving gap locks: a next-key lock (Lesson 03, §6) can participate in a deadlock cycle just like a row lock — two transactions both inserting into overlapping ranges can deadlock on gap locks even though neither is updating the same existing row.
• Self-deadlock within one transaction is not possible in the cyclic-wait sense — a single session cannot deadlock against itself, though it can encounter a lock wait timeout if it (unusually) tries to lock something it already holds in a conflicting mode via a bug in application logic.
• A single statement affecting many rows can deadlock against another multi-row statement if they touch overlapping rows in different orders — this is the multi-row generalization of the two-row scenario in §5, and is exactly why bulk UPDATEs should process rows in a consistent, predictable order (e.g., ORDER BY the primary key) where the engine and query shape allow it.

11. Troubleshooting Guidance

• SHOW ENGINE INNODB STATUS\G — read the LATEST DETECTED DEADLOCK section first; it directly names the two transactions, what each held, and what each wanted.
• Enable innodb_print_all_deadlocks to log every deadlock (not just the latest) to the error log — invaluable for diagnosing a deadlock that recurs intermittently under production load but isn’t easily reproduced on demand.
• If deadlocks cluster around specific tables/rows, that’s a direct signal to audit every code path touching those tables for lock order consistency — the fix is almost always there, not in the isolation level or retry logic.

12. Scalability Considerations

Deadlock frequency generally rises with concurrent write volume against overlapping resources and with transaction length (more time holding a first lock before acquiring a second widens the collision window). This is illustrative reasoning: the practical response is not “avoid all multi-row transactions,” but rather enforce consistent lock ordering and keep transactions as short as the business operation allows, since both directly shrink the window in which a cycle can form.

13. Additional Dialect Notes

Oracle: Automatic deadlock detection with a comparable victim-selection approach; Oracle’s error is ORA-00060: deadlock detected while waiting for resource.

SQLite: Its coarser table/database-level locking model (Lesson 03) makes classic row-level cyclic deadlocks largely inapplicable in the same form — contention manifests more as SQLITE_BUSY from the single-writer constraint than as a multi-row lock cycle.

DuckDB: Not designed around the high-concurrency multi-writer patterns that produce classic deadlocks; not a primary concern for DuckDB’s typical analytical, single-process usage.

14. Interview Questions

1. “What is the single most effective code-level change to reduce deadlock frequency in a system with symmetric two-resource operations?” — Tests for “consistent lock acquisition order,” the highest-leverage practice covered in this lesson.
2. “Your application just received a MySQL deadlock error. What should it do?” — Tests for “catch it and retry the transaction,” not treating it as a fatal, unretryable error.
3. “What’s the difference between a deadlock and a lock wait timeout?” — Tests precise understanding: a deadlock is an unresolvable cycle, requiring one participant to be killed; a lock wait timeout is a transaction giving up on a lock that would eventually have become available with no cycle involved.

15. Summary

A deadlock is a cycle of transactions each waiting on a lock the next one holds. MySQL detects this automatically and kills one transaction to break the cycle, returning ERROR 1213. The primary engineering defense is consistent lock ordering across every code path touching the same resources, backed by short transactions and application-level retry logic for the (expected, not exceptional) deadlock errors that still occur under real concurrent load.

16. Practice Challenges

1. Reproduce the §5 deadlock scenario yourself using two sessions against departments dept_id 4 and 7. Observe which session receives ERROR 1213.
2. Rewrite the two transactions from Challenge 1 so that BOTH always lock the lower dept_id first, regardless of which “direction” the business operation is conceptually going. Confirm the deadlock no longer occurs.
3. Explain, in your own words, why “just retry on deadlock” is a correct, production-appropriate response, while “just increase innodb_lock_wait_timeout” is not a fix for deadlocks specifically (connect this back to Lesson 03, §7 on what a lock wait timeout actually is).

(Some challenges are scaffolded in this lesson’s .sql file. The cross-lesson problem set, with full solutions, is in PRACTICE_PROBLEMS.md and SOLUTIONS.sql.)

17. Further Reading

• MySQL 8.4 Reference Manual — InnoDB Deadlocks
• MySQL 8.4 Reference Manual — Deadlock Detection

---

Previous: Lesson 03 — Locking Mechanisms · Next: Lesson 05 — MVCC

This site is open source. Improve this page.