LLM Best Practices

Deadlock Detection

2 min read

View markdown source

Overview

This page is the atomic definition. The general concept of deadlocks is defined at deadlock.

Definition

Deadlock detection is the process a database uses to discover and resolve circular lock dependencies between concurrently executing transactions. A deadlock arises 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. Neither can proceed. PostgreSQL runs a lightweight deadlock detection algorithm periodically (controlled by deadlock_timeout, defaulting to 1 second). When a lock wait exceeds deadlock_timeout, Postgres performs a full dependency graph traversal to check for cycles. If a cycle is found, Postgres selects one transaction as the victim (typically the one with the lowest cost to roll back), aborts it with error code 40P01, and allows the remaining transactions to proceed. The victim transaction receives an error and must be retried by the application. Deadlock detection is reactive; deadlock prevention (consistent lock ordering) is always preferable. Setting deadlock_timeout too low increases CPU overhead from frequent graph traversals; too high increases latency for legitimate deadlocks.

When it applies

Deadlock detection is automatic in PostgreSQL; applications should handle 40P01 errors by implementing retry logic with exponential backoff. The design goal is to prevent deadlocks structurally by always acquiring locks in a consistent order across all code paths.

Example

Two API endpoints both update users and wallets. Endpoint A acquires the user lock then the wallet lock. Endpoint B does the reverse. Under concurrent load they deadlock. The fix: establish a convention that all code acquires user lock before wallet lock. Detection fires; the fix eliminates the cycle.

  • deadlock - the underlying phenomenon that detection resolves.
  • postgres - PostgreSQL’s detection algorithm and deadlock_timeout configuration.
  • transaction-isolation - higher isolation levels increase lock contention and deadlock probability.
  • connection-pool - long-held pool connections during deadlock waits exhaust available connections.
  • write-ahead-log - aborted transactions are rolled back via WAL undo records.

Citing this term

See Deadlock Detection (llmbestpractices.com/glossary/deadlock-detection).

Graph View

Backlinks