Deadlock Handling

Detect; retry; design.

Overview

Deadlocks are an expected part of concurrent database access, not a sign of broken application code. The discipline is detecting them via specific error codes, retrying with backoff so the user never sees the abort, and designing schemas that acquire locks in consistent order to make deadlocks rare. Application code that ignores the possibility of deadlocks ships brittle behaviour.

• Detection via error code. PostgreSQL 40001, MySQL 1213. The engine aborts one transaction; the application catches the specific code.
• Transparent retry. Aborted transaction retries with backoff. User sees the result, not the deadlock.
• Schema and access design. Lock acquisition order consistent across transactions. Reduces deadlock frequency structurally.
• Lock ordering plus monitoring. Same-order acquisition prevents many deadlocks; deadlock-rate metric catches regressions.

The approach

Three habits make deadlock handling produce resilient applications: detect on the specific error code, retry with bounded exponential backoff, and design schemas with consistent lock ordering.

• Detect via specific error code. PG 40001 for serialisation failure, MySQL 1213 for deadlock detection. Generic exception handlers miss the pattern.
• Retry with exponential backoff plus jitter. Avoids thundering-herd retries that recreate the deadlock immediately.
• Cap retries. Bounded retry count. After three or five retries, fail loudly rather than loop forever.
• Consistent lock ordering plus monitored rate. Acquire locks in a stable order across transactions; deadlock-rate metric on the standing dashboard.

Why this compounds

Each deadlock handled transparently preserves user experience. The team’s concurrent-database fluency deepens; new tables ship with consistent lock-order conventions instead of producing deadlock incidents under load.

• User experience preserved. Transparent retry hides deadlocks from users entirely.
• Incident rate drops. Deadlock cascades caught early stop becoming customer-impact incidents.
• Schema design improves. Lock-order discipline produces cleaner schemas as a side effect.
• Year-one investment, year-two habit. First deadlock pattern is heavy lift. By the third table, lock-order conventions are settled.