Locks and Deadlocks

Detection and prevention.

Overview

Database locks and deadlocks are the concurrency reality of any system that runs multiple transactions in parallel. Database engines detect deadlocks and abort one transaction to break the cycle; the application’s job is to acquire locks in consistent order to prevent deadlocks where possible, and to retry transparently when the engine aborts a transaction. The discipline is structural: consistent lock ordering prevents deadlocks at the design level rather than relying on retry to paper over them.

• Detection and prevention. Database engines detect deadlocks at runtime; the application prevents them at design time via consistent lock order.
• Lock granularity. Row, page, or table-level locks; finer granularity scales further but adds overhead per lock.
• Lock ordering. Always acquire locks in the same order across transactions; prevents deadlocks structurally rather than via retry.
• SELECT FOR UPDATE plus deadlock retries. Explicit row locks where correctness requires them; transparent retry with backoff handles the deadlocks that slip through.

The approach

The practical approach is consistent lock order across transactions (deadlocks become impossible by design), retry on deadlock with exponential backoff (the engine’s deadlock signal triggers application-level retry), monitor lock-wait time as a leading signal of contention, use SELECT FOR UPDATE deliberately where row-level locking matters, and document per-table lock patterns committed to the schema documentation.

• Consistent lock order. Acquire locks in the same order across transactions; deadlocks become structurally impossible rather than rare.
• Retry on deadlock. Catch deadlock error code, retry with exponential backoff; the user sees a clean retry rather than an error.
• Monitor lock waits. Per-database lock-wait time tracked; long waits indicate contention before they produce user-visible slowness.
• SELECT FOR UPDATE deliberately plus documented locking. Per-query SELECT FOR UPDATE where row-level locking matters; per-table lock pattern committed to the schema documentation.

Why this compounds

Lock discipline compounds across services and tables. Each correct lock-ordering pattern prevents deadlocks for the lifetime of the codebase; each documented pattern survives team turnover; the team builds intuition for concurrent database design that pays off on every new feature. Without the discipline, deadlocks recur and the team treats retry as a substitute for design.

• Reduced lock contention. Right granularity supports throughput; locks scale with the workload rather than constraining it.
• Reduced incidents. Deadlock retries hide pathological cases; the user sees a clean retry rather than an error.
• Engineering quality. Consistent lock order produces correctness; deadlocks become rare exceptions rather than recurring features.
• Institutional knowledge. Each lock pattern teaches concurrency reasoning; the team learns where contention hides.

Lock and deadlock discipline is a database discipline that pays off across years. Nova AI Ops integrates with database telemetry, surfaces lock patterns, and supports the team’s database engineering discipline.