Deadlocks in Databases and Distributed Systems: Causes, Detection, and Prevention

Deadlocks in Databases and Distributed Systems matter because two healthy writers can wait on each other forever. Neither commit can proceed. The engine must abort one side, or your pool fills with stuck sessions. You need a plan before that page fires.

What a Deadlock Is

A deadlock is a wait cycle. Session A holds lock 1 and wants lock 2. Session B holds lock 2 and wants lock 1.

Each waits for the other. No timeout on the work itself will save you if both waits are patient.

Databases detect many of these cycles and abort one victim. The other session then gets the lock and continues. Your code sees an error that means “try the whole unit again.” If you only retry the last statement, you can write a partial change.

Distributed systems add cycles the database cannot see. Service A calls B while holding a row. Service B calls A while holding another row.

Each HTTP call waits. There is no single lock manager. The cycle ends only when a client or a proxy times out.

This is different from a slow lock. A slow lock has a line, and the line moves. A cycle does not move until someone dies.

If you only chart average wait, you will miss it. Count aborts and stuck calls as their own signals.

Why It Fails in Production

Cycles show up when lock order is not stable. One code path locks the buyer then the seller. Another path locks the seller then the buyer.

Under light traffic the overlap is rare. During a sale both orders exist at once, and the cycle appears.

I have seen a batch job deadlock with the API every hour. The job scanned rows in primary key order. The API locked the parent first because of a foreign key.

Each side was locally reasonable. Together they waited. The job’s retry then collided with the next API call.

Gap locks and insert intent locks widen the blast. In MySQL, a read that looks point-shaped can lock a range. An insert into that range waits.

A second statement locks the other way. You did not name two rows, but you still built a cycle. The InnoDB deadlock notes show this pattern.

Long calls inside a lock make distributed cycles likely. You hold a row, then call another service that takes its own lock and calls you back. Even a callback to the same service can cycle if a second request needs your row. Keep locks off the network.

How Detection Works

PostgreSQL checks for cycles on a timer. deadlock_timeout is how long a waiter sleeps before the check runs. It is not the lock timeout.

A short value finds cycles faster and costs more CPU on a busy system. The PostgreSQL lock settings describe both knobs.

When PostgreSQL finds a cycle, it aborts one transaction and returns an error. The other waiter proceeds. You should log the SQLSTATE and the statements, not only the word deadlock. Without the statements you cannot see the order bug.

MySQL records the last cycle in the engine status. Turn the log on so each cycle hits an error log. Then ship that log to the same place as your traces. A graph of deadlocks per minute is more useful than a single status dump after the fact.

Distributed cycles need timeouts on every outbound call. If service B does not answer, A must give up and release its locks. A retry budget stops a timeout storm from building a new cycle. Trace ids must cross the call so you can see who waited on whom.

What to capture on each abort

Save the waiter, the holder, and the lock type if the engine gives them to you. Also save the application route, not only the SQL text. Two routes can share one statement and still lock in different orders because of earlier statements in the unit.

Page on a rate, not on a single abort. A rare cycle that retries cleanly is noise. A climb during a deploy is a stop signal. Tie the alert to the release so you can halt the rollout while old and new orders still differ.

Trade-offs You Should Weigh

A global lock order is the strongest local fix. Every path locks rows from low key to high key. Cycles from opposite order disappear.

You pay a sort, and you must apply the rule to jobs and to the API. One forgotten path brings the cycle back.

Lock timeouts fail the waiter even when there is no cycle. The user sees a busy error sooner. You can abort a session that would have succeeded a moment later. Pick a timeout that is above normal waits and below your request deadline.

Optimistic locking avoids many cycles because writers do not wait. The loser gets a version mismatch and retries. On a hot row the retry storm is the new problem.

Use it when clashes are rare. Use ordered locks when clashes are the common case.

Read the lock mode list in the PostgreSQL explicit locking guide before you pick FOR UPDATE for every read. A weaker mode lets foreign keys share the row. That single change removes a class of cycles between inserts and parent updates.

Pitfalls and Failure Modes

First, retry the whole unit, with a limit and jitter. A tight retry re-creates the same cycle at once. After a few tries, fail the request.

Let the client back off. Also mark the metric so you can tell a retry from a new user action.

Second, foreign keys lock the parent. An insert into a child can wait on a parent update, and the parent update can wait on a child read. You will not see both locks in your own SQL. Explain the plan and the constraints when a cycle looks impossible.

Third, ORMs can reorder statements. A save of a graph may update rows in pointer order, not key order. Two requests with mirrored graphs will cycle. Sort the writes yourself, or lock the keys in order before the ORM flushes.

Fourth, pessimistic locking without an order rule is how teams manufacture cycles. The lock fixed the lost update and added a wait graph. If you take more than one row lock, sort the keys in the same function every caller uses.

Fifth, a distributed lock service can cycle too. If A locks object 1 in the lock service and then row 2 in the database, while B does the reverse, you still wait. Use one order across stores. Prefer not to hold a database lock and a remote lock at the same time.

A Practical Order Example

The statements below lock two accounts in key order before any balance change. Both the transfer API and the batch job must use the same order. Set a lock timeout so a waiter fails instead of sitting until the pool is empty.

BEGIN;
SET LOCAL lock_timeout = '400ms';
SELECT account_id, balance
FROM accounts
WHERE account_id IN (10, 42)
ORDER BY account_id
FOR UPDATE;

UPDATE accounts
SET balance = balance - 25
WHERE account_id = 10 AND balance >= 25;

UPDATE accounts
SET balance = balance + 25
WHERE account_id = 42;
COMMIT;

If the engine returns a deadlock error, roll back first. Then wait a short random time and run the whole block again. Do not sleep while the transaction is open. A common mistake I have seen is a catch block that retries the last update only.

Review checks before you ship

When a change takes more than one lock, reject it unless the order is explicit. The same rule applies to jobs, admin tools, and the request path. Race conditions in backend systems are the bug you are trying to close. A cycle is the bug you can add while you close it.

1. List every lock the request takes.
2. Sort keys before the first lock.
3. Set a lock timeout on that session.
4. Keep network calls outside the lock.
5. Retry the whole unit with jitter, then stop.

Performance, Scale, and Cost

Detection is cheap next to a stuck pool. A deadlock abort wastes one unit of work. A pool full of waiters wastes the whole host.

In an illustrative production range, a few dozen cycles a minute can be fine if retries succeed. Hundreds per minute usually mean a new order bug.

deadlock_timeout too low will scan for cycles on ordinary waits. That scan takes shared state on a busy primary. Start with the engine default.

Lower it only if victims sit so long that clients give up first. Measure CPU and abort latency before you keep the change.

Scale does not remove cycles. More app hosts mean more overlap, so a latent order bug appears sooner. More database cores do not break a cycle.

The fix is order, shorter critical sections, or fewer locks. Sharding can isolate hot keys, and it can also create cross-shard cycles if one request locks two shards in mixed order.

Cost shows up as retries, user errors, and oversized pools. Teams often raise max connections to hide waiters. That spends money and makes the next cycle larger. Fail fast, retry outside, and keep the pool small enough that a stall is visible.

If a deploy introduces a new lock order, be ready to undo it. Rollback strategies for backend systems should treat a deadlock spike as a release blocker. Rolling forward with a hotfix is fine when the patch is the sort. Rolling back is better when you do not yet know which statement joined the cycle.

Key Takeaways

• A deadlock is a wait cycle, so the line will not move on its own.
• Lock rows in one global key order on every path.
• Retry the whole unit after an abort, with a cap and jitter.
• Set lock timeouts and call timeouts so waiters fail fast.
• Watch foreign keys and gap locks, because they add edges you did not write.
• Do not hold a database lock across a network call.
• Alert on abort rate during deploys, not on a single error.

FAQ

Who should the engine abort?

Most engines abort the waiter that closes the cycle, or the one that is cheaper to undo. You should not depend on which side dies. Both code paths must handle the error and retry safely. If one path ignores the error, you will store a partial result.

Does a shorter transaction remove deadlocks?

Shorter units shrink the window, so cycles get rarer. They do not fix opposite lock order. Two short units can still deadlock if each holds one lock and wants the other.

Sort the locks first. Then shorten the unit so the remaining waits stay small.

How is a distributed deadlock different?

No single engine sees every edge. A waits on B over HTTP, and B waits on a row A holds. Timeouts are your detector.

Give every call a deadline, release local locks on failure, and retry with backoff. Trace both sides or you will guess at the cycle.

Should you disable deadlock checks?

No. Without detection, a cycle sits until you cancel sessions by hand. Keep the check.

Tune the timeout only with evidence. If aborts are noisy, fix the order. Turning the check off hides the bug and fills the pool.

Pick one lock order and apply it to the API, the jobs, and the admin tools. Add a lock timeout, log every abort with the statement text, and retry the whole unit with jitter. Then watch the abort rate on the next deploy. If it climbs, stop the rollout and find the path that locks out of order.

Last updated on 12 September 2026.