Database Deadlocks
debt(d5/e5/b5/t7)
Closest to 'specialist tool catches it' (d5). Deadlocks are detected by specialized monitoring tools like mysql-innodb-monitor, laravel-debugbar, and datadog as listed in detection_hints.tools. The database itself reports deadlock errors (MySQL 1213, PostgreSQL 40P01), but discovering the pattern of unhandled deadlocks requires these monitoring tools rather than static analysis or linters.
Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix indicates multiple changes needed: consistent lock ordering across all transactions, keeping transactions short, and proper SELECT FOR UPDATE usage. While individual retry logic might be a few lines, ensuring consistent lock ordering 'across all transactions' requires reviewing and potentially modifying multiple transaction sites throughout the codebase.
Closest to 'persistent productivity tax' (b5). Deadlock handling applies to web, cli, and queue-worker contexts per applies_to. Once deadlocks start occurring in a high-concurrency system, every transaction-heavy feature must consider lock ordering and retry logic. This creates ongoing cognitive load for developers writing new transactional code, but doesn't quite reach b7 since it's confined to database-touching code paths rather than shaping all architectural decisions.
Closest to 'serious trap' (t7). The misconception explicitly states developers wrongly believe 'deadlocks are always application bugs' when in fact databases handle them automatically by rolling back. The real trap is that developers don't realize they must retry the rolled-back transaction — the database silently rolls back without completing the operation, and developers assume success or assume the database 'fixed' it. This contradicts how most error handling works (where catching an exception means handling it, not retrying the entire operation).
Also Known As
TL;DR
Explanation
Deadlocks occur when transaction A holds a lock on row 1 and waits for row 2, while transaction B holds row 2 and waits for row 1. Databases detect this cycle and roll back the transaction with the smallest undo log (the 'victim'). The application must retry the victim transaction. Prevention strategies: acquire locks in a consistent order, keep transactions short, use SELECT ... FOR UPDATE to acquire all locks upfront.
Diagram
sequenceDiagram
participant T1 as Transaction 1
participant T2 as Transaction 2
T1->>T1: LOCK account 1
T2->>T2: LOCK account 2
T1->>T1: Wait for account 2 - blocked
T2->>T2: Wait for account 1 - blocked
Note over T1,T2: DEADLOCK - circular wait
T2-->>T2: DB kills T2 as victim
T1->>T1: Gets account 2 lock
T1->>T1: COMMIT
Common Misconception
Why It Matters
Common Mistakes
- Not catching and retrying deadlock exceptions (error code 1213 in MySQL, 40P01 in PostgreSQL).
- Accessing rows in different orders in different transactions — consistent lock ordering prevents deadlocks.
- Long transactions that hold locks for seconds instead of milliseconds.
- Not using SELECT ... FOR UPDATE when a read-then-write sequence must be atomic.
Code Examples
// Inconsistent lock order — deadlock prone:
// Transaction A: lock user 1, then lock user 2
// Transaction B: lock user 2, then lock user 1 — deadlock!
$pdo->beginTransaction();
$pdo->query('SELECT ... FROM accounts WHERE id = ' . $fromId . ' FOR UPDATE');
$pdo->query('SELECT ... FROM accounts WHERE id = ' . $toId . ' FOR UPDATE');// Consistent lock order — always lock lower ID first:
$pdo->beginTransaction();
$ids = [$fromId, $toId];
sort($ids); // Always acquire locks in ascending ID order
foreach ($ids as $id) {
$pdo->query('SELECT ... FROM accounts WHERE id = ' . $id . ' FOR UPDATE');
}