Two Transactions, Frozen Forever: The Deadlock Explained

Your logs show a "deadlock detected" error and one of your requests just died for no obvious reason. It feels random. It isn't. Deadlocks happen for one specific, avoidable reason — and once you see it, both the fix and the honest truth about them become obvious.

Two of your requests freeze, each holding exactly what the other needs, neither able to move — a silent standoff that would last forever if the database didn't step in and kill one. That's a deadlock, and if you run a database-backed app long enough, you will meet one.

Most explanations open with four abstract academic conditions and a wall of config flags. Let's do it differently: deadlocks happen for one concrete reason, they're prevented by one simple habit, and there's an honest truth that changes how you handle them.

The standoff, in one picture

When a transaction updates a row, the database locks it so no one else can modify it until the transaction finishes. That's the same tool that fixes the lost-update race condition — remember that, it matters shortly. A deadlock is what happens when two transactions each grab a lock the other needs next.

Transaction A locks Row 1 and works. Transaction B locks Row 2. Fine so far. But now A needs Row 2 (locked by B, so A waits), and B needs Row 1 (locked by A, so B waits). A waits for B; B waits for A. Each holds what the other needs, neither will let go until it finishes, and neither can finish. It's a circle — without intervention, both wait forever.

A deadlock is a circle of waiting: A holds what B needs, B holds what A needs, and politeness alone keeps them frozen forever.

The single cause: locking in different orders

What actually created the circle? Not that both touched the same rows — that happens constantly without deadlocking. The circle formed because they grabbed the locks in opposite orders.

If both A and B always lock Row 1 first, then Row 2: A grabs Row 1, B tries Row 1 and simply waits, A grabs Row 2 and finishes, releases, then B proceeds. One waited politely. No circle, no deadlock. The deadlock needed each transaction holding one row while reaching for the other — which only happens if they approach the rows in different orders.

A deadlock needs a circle, and a circle needs two transactions going in opposite directions. Make everyone go the same way, and there's nothing left to close the loop.

So the seasoned advice, stripped of jargon, is anticlimactically simple: always acquire locks in a consistent order. Lock rows by ascending ID; touch tables in the same sequence everywhere.

What that looks like in code

The classic trigger is anything touching two records at once — a money transfer:

# DANGEROUS: locks whichever account is "from" first
def transfer(from_id, to_id, amount):
    lock(from_id)   # A: locks 1 then 2   B: locks 2 then 1
    lock(to_id)     # opposite orders = deadlock waiting to happen

If Alice pays Bob while Bob pays Alice, A locks Alice-then-Bob and B locks Bob-then-Alice — the exact circle. The fix is tiny: sort the IDs so every transfer locks the lower ID first.

# SAFE: always lock the smaller ID first, regardless of direction
def transfer(from_id, to_id, amount):
    first, second = sorted([from_id, to_id])
    lock(first)     # everyone agrees on the order now
    lock(second)    # the circle can never form

One sorted() call eliminates an entire category of production incident.

The honest truth: you can't prevent them all

Consistent lock ordering kills the common case, but you can't eliminate every deadlock — locks get taken on indexes and foreign keys you didn't ask for, ORMs reorder operations, and some patterns are genuinely hard to fully order. Chasing a 100% deadlock-proof system is a losing game.

The mindset shift: a deadlock is not a bug to eliminate. It's a condition to survive. PostgreSQL, MySQL, and the rest all detect the circle within milliseconds, pick a victim, kill it, and let the other proceed. The system never freezes forever — one transaction just fails with a deadlock error.

Stop trying to make deadlocks impossible. Start making your code survive them. The database already broke the tie. Your job is to try again.

Because the killed transaction did nothing wrong, the right response is almost always to catch the deadlock error and retry the whole transaction. On the retry, timing has shifted and it sails through — invisible to the user.

The full strategy: prevent most, survive the rest

Prevent the common case: acquire locks in a consistent order (the big one); keep transactions short so locks are held briefly (do slow work outside the transaction); touch as few rows as possible.

Survive the rest: wrap risky transactions in retry logic — catch the deadlock error and try again, adding a little randomness ("jitter") so two conflicting transactions don't just retry in lockstep and collide again.

for attempt in range(3):
    try:
        run_transaction()
        break
    except DeadlockError:
        sleep(random() * 0.1 * (attempt + 1))  # jitter, then retry

Prevent most deadlocks by always locking in the same order and keeping transactions short. Survive the rest by catching the error and retrying with jitter. Do both.

Deadlock vs. race condition: the sibling bugs

In the lost-update race condition, the problem was too little locking — two transactions read the same row without coordinating and one overwrote the other; the fix was to lock. Here, the problem is caused by locking — two transactions lock in conflicting orders and freeze.

Not a contradiction — two edges of the same blade. Locks enforce order on concurrent access (preventing lost updates), but locks are also a resource transactions wait on (enabling deadlocks).

A race condition is what happens when transactions don't wait enough. A deadlock is what happens when they wait on each other.

Key Takeaways

• A deadlock is a circular wait: each transaction holds a lock the other needs.

• The root cause is acquiring locks in different orders.

• Prevent most by locking in a consistent order (e.g. by ascending ID) and keeping transactions short.

• You can't prevent all — the database detects and kills one; your job is to catch the error and retry.

• Add jitter to retries so conflicting transactions don't collide again in lockstep.

FAQ

What causes a database deadlock?

Two or more transactions each hold a lock that another needs, forming a circular wait none can escape. The usual root cause is transactions acquiring locks on the same rows in different orders, so each ends up holding one resource while waiting for another that a different transaction holds. Touching the same rows isn't enough on its own — the conflicting order is what closes the circle.

How do you prevent database deadlocks?

The single most effective step is acquiring locks in a consistent order everywhere — for example, always locking rows by ascending primary key. Also keep transactions short (hold locks for less time), do slow work outside the transaction, and touch as few rows as possible. Because you can't prevent every deadlock, also catch deadlock errors and retry the transaction with a small random delay.

What happens when a deadlock is detected?

Modern databases like PostgreSQL and MySQL/InnoDB automatically detect the circular wait within milliseconds using cycle detection. They choose one transaction as the "victim," roll it back to break the cycle, and let the other transactions continue. The victim receives a deadlock error (e.g. PostgreSQL 40P01, MySQL 1213), which the application should catch and retry. The system never stays frozen.

Should you retry a transaction after a deadlock?

Yes, in almost all cases. The victim transaction was rolled back through no fault of its own — the database simply had to pick one to break the tie. Retrying the entire transaction usually succeeds because the timing has shifted and the conflicting transaction has finished. Add jitter (a small random delay) so simultaneous victims don't retry in lockstep and immediately deadlock again.

What is the difference between a deadlock and a race condition?

A race condition comes from too little coordination: transactions don't lock properly, so they overwrite each other's changes (a lost update). A deadlock comes from locking in conflicting orders, so transactions freeze waiting on each other. One is caused by insufficient locking; the other by careless locking. Together they represent the core trade-off of database concurrency.

Does lock ordering completely eliminate deadlocks?

No. Consistent lock ordering eliminates the most common cause, but real systems still deadlock because databases take implicit locks on indexes, foreign keys, and gaps that you don't explicitly control, and ORMs can reorder operations. That's why the complete strategy is two-part: prevent the common case with lock ordering and short transactions, and survive the rest with detection (built into the database) and application-level retries.

Related reading

• Your Database Transaction Didn't Save You From This Bug — the lost-update race condition, the sibling concurrency bug.

• Why Your API Charges Customers Twice — idempotency, another concurrency-on-shared-state problem.

• Why Your App Is Fast in Dev and Slow in Production — the N+1 query, another "only in prod" backend trap.

The bottom line

A deadlock isn't the database rolling dice. It's a circular wait — two transactions each holding what the other needs — and it forms because they grabbed their locks in different orders. Force a consistent order to prevent the common case; accept you can't prevent them all, catch the error, and retry with jitter to survive the rest.

The database will always break the tie for you. Your only job is to decide, in advance, what happens next — and the answer is almost always: just try again.

I'm a software engineer writing about the systems behind the software, minus the jargon. Follow along for the next one.