Database Locks and Isolation: Diagnose Blocking, Deadlocks, and Retries

A lock wait is a queue, not yet a deadlock

A transaction waiting for a lock may simply be behind a slow transaction. A deadlock requires a cycle: transaction A waits for a resource held by B, while B waits for something held by A. Databases detect many such cycles and abort one participant so the other can proceed. Applications should treat the victim error as a retryable transaction failure only when the whole operation is safe to repeat.

Keep transaction duration short. Do not hold row locks while calling a remote service, waiting for user input, or processing an unbounded batch. Establish a consistent order when acquiring multiple resources; this reduces cycles but cannot eliminate all deadlocks under every workload.

Isolation controls visibility

Isolation levels govern what concurrent transactions may observe, such as uncommitted writes, changes committed after a transaction begins, or rows inserted during a repeated predicate query. Names and exact behavior vary between engines. Do not infer semantics from the label alone: inspect the target database's documentation and test the anomaly relevant to the application.

Stronger isolation can surface serialization failures that require retrying the entire transaction. Retrying one statement from the middle may reuse assumptions based on a snapshot that is no longer valid. Keep retry boundaries aligned with the business operation and cap attempts with backoff.

Diagnose with transaction evidence

Capture blocking and blocked sessions, transaction age, lock type, SQL text, and wait duration. In PostgreSQL, inspect activity and lock views; in MySQL, inspect InnoDB transaction and lock-wait data; in Oracle, use session and lock views. Exact views and privileges vary by version. Record the database engine/version and query plan so a later diagnosis can reproduce the same conditions.

Look for missing indexes on update predicates, inconsistent resource acquisition order, large transactions, and idle-in-transaction sessions. A query that scans many rows may lock more resources or hold locks longer than expected.

Safe recovery patterns

Make retried writes idempotent using stable request IDs or unique constraints. Return clear conflict responses for uniqueness violations. Add jitter to retries so a contention burst does not synchronize clients into another burst. Do not automatically retry non-idempotent external side effects inside a transaction.

Measure lock wait time and deadlock rate alongside user-facing latency. Increasing lock timeouts can reduce visible errors while making requests occupy connections for longer; use it only when the workload contract justifies the wait.