Deadlock
criticalSummary
Two or more transactions each hold a lock the other needs, forming a cycle in the lock wait-for graph that no participant can escape on its own. The database breaks the cycle by aborting one transaction, surfacing a serialization-class error the application must catch and retry. Under sustained contention, naive immediate retries re-enter the same cycle and amplify it into a retry storm.
Description
A deadlock is a cycle in the lock wait-for graph. Transaction A holds a lock on row X and waits for row Y; transaction B holds row Y and waits for row X. Neither can make progress, and neither will release what it holds, so the wait is permanent until an outside actor intervenes. The database is that actor: it aborts one transaction in the cycle, rolls back its changes, and returns an error. Deadlock is a liveness failure, not a corruption one. Nothing is lost, but a unit of work that looked valid fails and must be replayed.
How the database breaks the cycle differs by engine, and the difference matters for tuning. PostgreSQL does not poll for deadlocks. A backend that blocks on a lock arms a timer of deadlock_timeout (default 1s); only if the wait outlives that timeout does the backend build the wait-for graph and look for a cycle. This lazy check keeps the common deadlock-free case free of overhead, at the cost of a one-second stall before a real deadlock is resolved. When the check finds a cycle, the backend that ran it aborts its own transaction with SQLSTATE 40P01 (ERROR: deadlock detected). Because the detecting backend is always the victim, PostgreSQL has no global "lowest cost" victim policy: the transaction that happened to close the cycle is the one that dies.
MySQL InnoDB instead maintains the wait-for graph continuously and detects a cycle immediately, with no timeout wait, while innodb_deadlock_detect is on (the default). It selects as victim the transaction that has changed the fewest rows (the smallest undo footprint), reported as ERROR 1213 (SQLSTATE 40001). On very high write concurrency the continuous detection itself becomes a bottleneck; some operators disable innodb_deadlock_detect and fall back to innodb_lock_wait_timeout, trading precise detection for a coarser timeout that also catches simple lock waits.
The retry that follows an abort is where a local failure becomes a systemic one. Each aborted transaction retries, competing for the same locks it just lost. Without backoff, the retried transactions re-form the same cycle, so throughput collapses while the deadlock rate climbs rather than falls. Retry amplifies contention unless it is spaced out and bounded.
The cycles themselves come from a small set of patterns: 1. Lock-order inversion: one code path locks (X, Y), another locks (Y, X). Under
concurrency A grabs X and B grabs Y, then each waits on the other. This is the classic
case and the only one consistent lock ordering fully prevents.
2. Gap and next-key locks: a range UPDATE or DELETE under InnoDB locks the gaps between
index entries; a concurrent INSERT into that range waits, and two such statements in
opposite ranges can deadlock even though they touch no common row.
3. Foreign-key cascades: ON DELETE CASCADE or an FK check acquires locks on parent or
child rows that another transaction already holds, forming a cycle the application
never wrote explicitly.
Characteristics
Triggers
- ·Concurrent transactions acquiring locks on the same rows in different orders
- ·InnoDB gap or next-key locking under range DELETE or UPDATE with concurrent INSERT into the range
- ·Foreign key cascade operations concurrent with direct updates to the same parent or child rows
- ·Application code that holds locks while calling external services, widening the window a cycle can form in
Detection Signals
Mitigation Strategies
Establish a canonical order for acquiring locks on multiple rows, for example sorting by primary key before locking. If every path acquires locks in the same order, a cycle cannot form. The cost is discipline that lives in application code and code review, and the limit is coverage: it prevents lock-order-inversion deadlocks but not gap-lock or foreign-key-cascade cycles, and it is unworkable when the lock set is discovered mid-transaction rather than known up front.
On a deadlock error, roll back and retry after a short randomized delay (roughly 100 to 500ms with jitter), bounded to 3 to 5 attempts before surfacing the error. Backoff spaces the contenders out so they stop reforming the same cycle. This does not reduce the deadlock rate, it absorbs it: cost moves into added tail latency, and the transaction must be safe to replay, since a retried write runs again from the start.
Shorter transactions hold locks for less time, shrinking the window a cycle can form in. Move external calls and computation outside the transaction, and acquire locks as late as possible. The cost is refactoring, and the limit is that a business operation genuinely needing several locks held together cannot shed them.
For work-queue access where any available row will do, SKIP LOCKED skips rows already locked by other workers instead of waiting, so competing workers never form a wait cycle. It changes semantics rather than adding cost: a skipped row is simply handled by someone else, so it fits "claim any pending row" but not "this specific row must be processed here", and it does nothing for multi-row transactional updates.
Recovery Steps
- 1.Read the database deadlock log to identify the exact statements and the rows or index ranges in the cycle
- 2.Determine which lock-ordering, gap-lock, or foreign-key-cascade pattern produced the cycle
- 3.Enforce consistent lock ordering on every code path that touches the contested resource
- 4.Add bounded retry with exponential backoff and jitter to the affected write paths, and confirm those transactions are safe to replay
- 5.Track deadlock frequency as a metric and alert on sustained rates above baseline
Estimated recovery time: Each individual deadlock resolves automatically: PostgreSQL within one deadlock_timeout, InnoDB effectively immediately. Application retry adds roughly 100 to 500ms per attempt. Bringing a sustained deadlock rate down is a code change on a release cycle, and the rate typically drops as soon as consistent lock ordering ships.
Affected Systems
Patterns
Technologies
Basis
Deadlock is a precisely defined concurrency failure with formal cycle-detection algorithms in every major relational engine; the PostgreSQL deadlock_timeout and self-abort behavior and the InnoDB continuous-detection and smallest-victim behavior are documented, and consistent lock ordering plus bounded retry are standard mitigations.
Sources & Claims
deadlock_timeout default is 1s
pendingofficial documentation · PostgreSQL runtime config (deadlock_timeout, default 1s)
transactions-isolation-red-team.md row 1
Detection is lazy and per-waiter: a blocked backend checks for a cycle only after waiting deadlock_timeout, not by global polling.
pendingofficial documentation · PostgreSQL docs: deadlock_timeout is "the amount of time to wait on a lock before checking whether there is a deadlock"
transactions-isolation-red-team.md row 2
PostgreSQL aborts the detecting backend itself; there is no global "lowest cost" victim policy.
pendingimplementation observed · Implementation-level: the backend running the check that finds itself in the cycle raises the error. User docs only promise "one of the transactions".
transactions-isolation-red-team.md row 3, flagged by the checklist as most deserving reviewer attention (implementation-level, not spelled out in the user manual)
PostgreSQL deadlock error is SQLSTATE 40P01 (deadlock_detected)
pendingofficial documentation · PostgreSQL error-codes appendix (40P01)
transactions-isolation-red-team.md row 4
InnoDB detects continuously (no timeout wait) while innodb_deadlock_detect is on (default).
pendingofficial documentation · MySQL docs: innodb_deadlock_detect (default ON)
transactions-isolation-red-team.md row 5
InnoDB victim is the transaction with the smallest undo footprint (fewest rows changed).
pendingofficial documentation · MySQL docs: InnoDB "rolls back the transaction ... that has the least number of inserted, updated, or deleted rows"
transactions-isolation-red-team.md row 6
InnoDB deadlock is ERROR 1213 / SQLSTATE 40001
pendingofficial documentation · MySQL error 1213 (ER_LOCK_DEADLOCK), SQLSTATE 40001
transactions-isolation-red-team.md row 7
Disabling innodb_deadlock_detect falls back to innodb_lock_wait_timeout
pendingofficial documentation · MySQL docs: with detection off, InnoDB relies on innodb_lock_wait_timeout
transactions-isolation-red-team.md row 8
SHOW ENGINE INNODB STATUS reports the last deadlock (LATEST DETECTED DEADLOCK); log_lock_waits logs PostgreSQL waits.
pendingofficial documentation · MySQL SHOW ENGINE INNODB STATUS; PostgreSQL log_lock_waits
transactions-isolation-red-team.md row 9
Run This Failure
Blast radius analysis for this failure mode within each scenario that carries it.
Related Architecture Knowledge
Inbound: affects this entity
Financial transaction workloads are vulnerable to deadlocks when concurrent transactions acquire locks on the same account or balance rows in different orders.
Full relationship →PostgreSQL detects deadlocks via cycle detection in the lock graph (runs every deadlock_timeout, default 1s) and aborts the cheapest transaction to resolve the cycle; applications must handle deadlock errors with retry logic.
Full relationship →Used In Architecture Scenarios
Write-Heavy Application
A durable background job execution platform where jobs are enqueued via API and executed by competing consumer worker pools. PostgreSQL is the durable job store, job definitions, retry state, scheduling metadata, and dead-letter records persist in PostgreSQL with ACID guarantees, surviving any worker or queue infrastructure failure. Redis tracks in-flight job state (which worker claimed which job, visibility timeout lease expiry) to enable fast lease checks without PostgreSQL queries on the hot path. Temporal provides workflow orchestration for multi-step jobs that require coordination across multiple execution stages, with built-in state machine semantics and durable activity execution. Kafka carries job completion events to downstream consumers (analytics, billing triggers, notification fan-out). Backpressure between the job enqueue rate and worker execution rate prevents runaway job accumulation when workers are degraded.
Marketplace Platform
An e-commerce order lifecycle platform handling cart, checkout, payment, fulfillment, and returns across a mixed read/write workload where product discovery is read-heavy, checkout is write-transactional, and fulfillment is event-driven. The saga pattern orchestrates multi-step checkout: reserve inventory → charge payment → confirm order → notify fulfillment. PostgreSQL owns order records and inventory with row-level locking; Redis holds session state and cart contents with sub-millisecond access; RabbitMQ delivers fulfillment notifications with dead-letter handling; Elasticsearch serves product search and order history with faceted navigation. CQRS separates the write command path from the read model: the order read model is denormalized for fast order history queries without joining across domain tables.
Financial Ledger
An electronic health record (EHR) architecture built around strict auditability, HIPAA compliance, and append-only correctness. Clinical records are mutable by design (amendments, addenda) but corrections must be explicitly attributed, not silently overwritten. Event sourcing provides a reconstructable audit log; PostgreSQL row-level security enforces patient-level access control at the database layer; Kafka streams HL7 FHIR events to downstream clinical systems. The architecture must support breach detection, access auditing, and state reconstruction at any historical point: not just current state retrieval.