Database

Handle Deadlocks and Lock Wait Timeouts

Diagnose transaction conflicts and implement bounded, safe retries for concurrent database workloads.

Overview

Deadlocks occur when transactions wait on resources held by one another in a cycle. InnoDB detects many deadlocks and rolls back one transaction so the other can proceed. Lock wait timeouts occur when a transaction waits too long without a deadlock cycle being resolved.

Start with the workload and the invariant

Start by capturing the exact SQL statements, transaction boundaries, involved indexes, and database diagnostics. In MySQL, inspect InnoDB status immediately after an incident because detailed deadlock information may be transient. Correlate database timestamps with application request IDs and worker logs.

Practical example

Common causes include inconsistent row-update order, missing indexes that cause broader locking, long transactions, and range operations under isolation levels that use next-key locks. Two code paths may update the same rows in opposite orders. Standardize the order in which related records are locked.

START TRANSACTION;
SELECT id, status FROM jobs WHERE id = ? FOR UPDATE;
UPDATE jobs SET status = 'processing' WHERE id = ?;
COMMIT;

Trade-offs and failure modes

Retry only errors that are known to be transient, and retry the complete transaction. Use a small maximum attempt count and exponential backoff with jitter. A retry loop without a limit can amplify an overloaded database. Ensure that external actions such as charging a card or sending an email are not repeated accidentally.

Verification and rollout

Reduce transaction duration. Do not perform HTTP requests, wait for user input, or process large files while holding locks. Add indexes that let statements find only the intended rows. Be careful: adding an index can change locking behavior, so validate the resulting plan and concurrency characteristics.

Engineering checklist

Build tests that create conflicting operations deliberately. Confirm that business invariants hold after retries and that clients receive a controlled response when the retry budget is exhausted. Monitor deadlock rate, lock wait duration, transaction age, and retry counts to identify regressions.

Before shipping, review the database version, storage engine, SQL mode, character set, collation, and deployment process used by the target environment. Features and DDL behavior can differ between MySQL and MariaDB releases. Validate the exact statements on a representative staging database, capture a baseline, and keep a tested recovery path for changes that affect production data.

Share this page Help other developers discover this resource.