2026-09-03 · 8 min read
- sql
- database
- reliability
A database deadlock is a cycle of transactions waiting for locks held by one another. The engine detects the cycle and aborts a victim, so applications must expect the error even after query and index improvements.
Correct handling combines prevention, complete-transaction retries, and idempotent side effects. Lock waits, statement timeouts, serialization failures, and connection errors may need different policies even when users see the same failed request.
Keep transactions small and ordered
Choose a stable order for touching tables, indexes, and rows across every code path that performs the same operation. Fetch required external data before opening the transaction and avoid waiting for network calls or user input while locks are held.
- Update multiple rows in a deterministic key order.
- Use indexes that avoid broad scans and excess locking.
- Select only rows and columns required by the transaction.
- Document isolation levels and explicit lock clauses.
Interpret database evidence
Collect the engine's deadlock graph or report, normalized statements, query plans, transaction timestamps, and application operation names. The statement selected as victim may not be the statement that introduced the inconsistent order.
Protect confidential values while preserving table, index, predicate shape, and execution order. Correlate database evidence with application traces rather than reconstructing the cycle from one exception message.
Retry the whole transaction safely
After rollback, rerun the complete transaction only for error classes the engine and driver identify as retryable. Use a small bounded attempt count with exponential backoff and jitter, then surface a controlled failure.
- Re-read values whose validity depended on the transaction snapshot.
- Use an idempotency key for request-level duplicate protection.
- Publish external effects after commit through a durable outbox.
- Track attempts, delay, final outcome, and contention hot spots.
Verify under concurrency
Use Flashman's SQL formatter to compare operation order, timestamp converter to align traces, UUID generator for synthetic idempotency keys, diff to review plans and transaction revisions, and JSON formatter for sanitized deadlock events.
Test opposite row orders, hot keys, missing indexes, realistic isolation, rollback at each statement, duplicate delivery, retry exhaustion, failover, and sustained load using the same database engine and schema as production.