Database Deadlocks Explained: Causes and Prevention

A database deadlock happens when two or more transactions each hold a lock the other one needs, and neither can proceed until the other releases it — which never happens, because it’s waiting too. Left alone, the two transactions would wait forever. Instead, the database detects the cycle and forcibly kills one of them, returning an error the application has to handle.

How a deadlock actually forms

Deadlocks require locking, and most relational databases lock rows (or ranges of rows) as transactions touch them, releasing the locks at commit or rollback under the concurrency rules of the configured isolation level. A deadlock needs at least two transactions and at least two resources, acquired in opposite order:

1. Transaction A locks row 1.
2. Transaction B locks row 2.
3. Transaction A tries to lock row 2 — blocked, because B holds it.
4. Transaction B tries to lock row 1 — blocked, because A holds it.

Neither transaction can move forward. Both are waiting on a lock the other holds, and neither will release its own lock until it finishes. That’s the classic deadlock cycle, and it’s an easy trap to fall into by accident: two code paths that update the same two tables in a different order will deadlock under enough concurrent load, even though neither one looks wrong in isolation.

Detection: how the database breaks the tie

Databases don’t wait indefinitely for a deadlock to resolve itself, because it can’t — by definition, nothing changes until something intervenes. Instead, most engines periodically build a wait-for graph: a map of which transactions are blocked on which locks, and which transactions hold those locks. A cycle in that graph is a deadlock, and once one is found, the database picks a victim — typically the transaction that has done the least work, or the one that would be cheapest to roll back — and aborts it with an error, releasing its locks so the survivor can continue.

This is different from ordinary lock waiting. A transaction that’s simply waiting its turn for a busy row isn’t deadlocked; it will proceed as soon as the lock holder commits. Deadlock detection specifically looks for cycles that can never resolve on their own.

Why the application has to handle it

Because the database picks a victim more or less arbitrarily, the aborted transaction’s work is simply lost — it has to be retried from the start. This is the one thing that makes deadlocks different from most other database errors: a well-written application treats a deadlock error as expected, retryable behavior under concurrency, not as a bug to alert on. A typical pattern wraps the transaction in a retry loop with a small random backoff, so that if the same two transactions collide again, they’re unlikely to collide in exactly the same order a second time.

Common causes in real applications

• Inconsistent lock ordering. The single most common cause. If one code path updates accounts in the order (A, B) and another updates them in the order (B, A), concurrent execution of both is a deadlock waiting to happen.
• Long-running transactions. The longer a transaction holds its locks, the larger the window for another transaction to grab a conflicting lock and create a cycle.
• Unindexed foreign keys or scans. A query that has to lock far more rows than intended — because there’s no index to narrow the scan — locks more surface area and raises the odds of colliding with another transaction. See database indexing for why this matters beyond just query speed.
• Mixed read and write patterns under high concurrency. Especially at higher isolation levels, where read locks can participate in the same wait cycles as write locks.

Prevention strategies

The most reliable fix is also the simplest: always acquire locks in the same order. If every transaction that touches accounts A and B locks the lower ID first, the circular-wait condition simply can’t form — one transaction will always get there first and the other will wait behind it, not across from it.

Beyond ordering:

• Keep transactions short. Do as little work as possible between a lock’s acquisition and the commit that releases it.
• Use the lowest isolation level that’s actually correct for the workload. Stricter levels lock more, for longer, which raises collision odds; see the isolation levels explainer above for the trade-offs.
• Index the columns your writes filter and join on, so updates lock the rows they need and nothing extra.
• Consider optimistic concurrency for high-contention rows. Optimistic locking checks for conflicts at commit time instead of holding locks throughout, which sidesteps deadlocks entirely at the cost of occasionally having to retry a whole transaction after the fact.

Deadlocks versus plain lock contention

	Lock contention	Deadlock
Resolution	Resolves on its own once the lock holder commits	Never resolves without intervention
Symptom	Slower transactions, temporary waiting	One transaction aborted with an error
Cause	Normal concurrent access to a hot row	A cycle of mutual waiting across ≥2 resources
Fix	Reduce contention, shorten transactions	Fix lock ordering; retry on abort

The takeaway

A deadlock is a cycle of transactions each waiting on a lock the other holds, and it can’t resolve without the database stepping in to abort one of them. The most durable fix is architectural — always acquire locks on shared resources in the same order — but a well-behaved application should also treat deadlock errors as expected and retryable, not exceptional. Combined with short transactions, sensible indexing, and the lowest workable isolation level, that keeps deadlocks rare enough to be a non-event rather than a recurring outage.