⚡THE SHORT ANSWER
A database Deadlock occurs when two concurrent transactions mutually block each other in a cyclic dependency: Transaction A locks Row 1 and attempts to lock Row 2, while Transaction B concurrently locks Row 2 and attempts to lock Row 1. Neither transaction can proceed. The database kernel's background deadlock detector traverses the active transaction Wait-For Graph, identifies the cycle, abruptly terminates one transaction (the Deadlock Victim with SQLSTATE 40P01 / Error 1213), and rolls back its changes. In poorly designed applications, if the client immediately retries the transaction without delay, both transactions repeatedly collide in a Thundering Retry Livelock. Production database architectures eliminate deadlocks using two pillars:
Deterministic Lock Ordering: all code paths must acquire row/table locks in identical ascending numerical order (e.g. ORDER BY id ASC FOR UPDATE), completely breaking circular wait cycles, and
Full Jitter Exponential Backoff Retries for unavoidable optimistic concurrency conflicts.
Engineering Handbook & Failure Dynamics
6-Dimensional Architecture Breakdown⚙️1. Underlying Mechanism
Execution🎯2. Appropriate Use Context
Scope⚠️3. Production Failure Modes
P0 Risk📡4. Diagnostic Signals & Telemetry
Telemetry🛡️5. Prevention & Safeguards
Safeguards⚖️6. Architectural Trade-offs
Trade-offCase Study (TinyCTO In-Field Example)
A banking application executed peer-to-peer money transfers with UPDATE accounts SET balance = balance - 100 WHERE id = from_id followed by WHERE id = to_id. When User 1 sent 50 to User 2 while User 2 simultaneously sent 20 to User 1, both transactions locked their source account and deadlocked on the destination account, failing 12% of peak transfers. The engineering team refactored the transfer logic to sort account IDs: [firstId, secondId] = [fromId, toId].sort(). Both transactions locked the lower ID first, followed by the higher ID. Deadlocks dropped immediately to 0.00% under identical load.
Interactive Concept Drills
2 CardsWhat mathematical condition is broken by sorting resource IDs before acquiring SQL locks?
Why should transactions victimized by a deadlock retry with randomized jitter rather than fixed backoff?
Database Deadlocks: Lock Dependency Cycles, Victim Selection & Jittered Retry Backoff — Technical FAQ
What should you NEVER do inside an active SQL database transaction?
Make external HTTP API calls, send emails, or execute slow file I/O while holding open row/table locks.
What is the difference between a Deadlock and a Lock Wait Timeout?
A Deadlock is an active cycle where transactions block each other indefinitely; a Lock Wait Timeout occurs when a single transaction waits too long for a lock held by another slow transaction.
🤖 AEO & Key Facts Summary
Key Architectural Facts
- ▸
Deadlocks occur when two or more transactions create a cyclic lock dependency graph.
- ▸
The database automatically aborts one transaction as a 'deadlock victim' to break the cycle.
- ▸
Acquiring locks in strict ascending numerical order mathematically eliminates circular wait.
- ▸
Always retry aborted transactions using exponential backoff with full randomized jitter.
Common Misconceptions
- ✗
Yanılgı: Deadlocks mean your database server is out of memory or CPU (Gerçek: Deadlocks are pure logical locking conflicts in application code, independent of server hardware).
- ✗
Yanılgı: Increasing deadlock timeout eliminates deadlocks (Gerçek: It only makes transactions hang longer before being aborted, worsening user-facing latency).
Decision & Governance Guidance
Sort resource IDs prior to locking and implement jittered retry interceptors in data access layers to eliminate database deadlocks under high concurrency.
Authoritative Sources & Standards
- [OFFICIAL_DOCUMENTATION]PostgreSQL Explicit Locking & Deadlock Handling Documentation— The PostgreSQL Global Development Group