What Is a Database Deadlock (and How Do You Fix It)?

Quick answer
A deadlock happens when two or more transactions each hold a lock and each is waiting for a lock another holds, so none can proceed.
The database does not hang: it detects the cycle and aborts one transaction with an error, which is why you see an occasional failure rather than a freeze.
The usual cause in application code is inconsistent lock ordering — one path updates rows A then B, another updates B then A.
The fix is to order writes consistently, keep transactions short, and retry the transaction that was chosen as the victim.
A deadlock error is normal under concurrency and must be handled by retrying; an unhandled one becomes a user-visible failure.

What is a database deadlock?

It is a cycle of waiting. Transaction one holds a lock on row A and requests row B; transaction two holds a lock on row B and requests row A. Neither can proceed until the other releases, and neither will release until it proceeds. Rather than wait forever, the database runs deadlock detection, finds the cycle, and rolls one transaction back — the victim — returning an error to that client so the others can continue.

The important consequence is that a deadlock is not a bug in the database and not (usually) a sign that a query is slow. It is a scheduling collision, and the correct handling is to retry the aborted transaction. Systems that never see deadlocks are either serialised to the point of no concurrency or lucky; systems that see them and ignore the error are losing writes.

How the database resolves it

Detection is usually a graph search: the database tracks which transaction waits for which lock and looks for a cycle. PostgreSQL and MySQL both do this and abort a transaction when a cycle is found, choosing a victim by cost, such as the one with the least work done. SQL Server by default picks the cheaper transaction; other engines vary. The error names the detection explicitly, and the transaction is already rolled back when you receive it.

Some engines also offer a timeout as a safety net: a lock wait that exceeds a configured interval is aborted even without a detected cycle. That covers cases where detection is expensive or where a transaction is waiting on something outside the database. Either way the remedy is the same — the application must be prepared to retry, because a lock wait that ends in an abort is not a permanent failure.

-- What PostgreSQL reports for the aborted transaction:
-- ERROR:  deadlock detected
-- DETAIL:  Process 4127 waits for ShareLock on transaction 9931;
--          blocked by process 4131.
--          Process 4131 waits for ShareLock on transaction 9930;
--          blocked by process 4127.

-- Find what is actually waiting, and on what.
SELECT pid, wait_event_type, wait_event, left(query, 80) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

Why generated code creates them

Two patterns dominate. The first is inconsistent ordering: a checkout flow updates the order then the inventory, while a restock flow updates inventory then the order. Each is sensible alone; together they deadlock whenever they overlap. The second is a long transaction that acquires many locks, because the longer a transaction holds a lock the wider its window for colliding with another.

A third contributor is convenience-driven looping: generated code frequently iterates over items and updates each row in its own statement, inside one transaction, in whatever order the list arrived. Two such transactions processing overlapping sets in different orders deadlock reliably. Batching or sorting the rows before updating removes most of it, and it is usually faster as well.

How to fix and prevent them

Start with ordering. Give every write path a defined order — always by primary key, always products before orders — and apply it before touching the database. Then shorten the transaction: do validation, computation, and external calls before opening it, and keep only the writes inside. Lower the isolation level where you do not need the stronger guarantees, since some locks are only taken at the higher levels.

Finally, handle the error. Wrap the transaction in a bounded retry that only retries on the deadlock or serialisation failure code, with a short randomised delay. That combination — consistent order, short transactions, retry — resolves the overwhelming majority of production deadlocks. The retry matters because the database already told you the transaction was rolled back cleanly; treating that as a permanent failure is what turns a scheduling collision into a broken checkout.

# Retry only on the two lock-conflict errors, with jitter and a cap.
import random, time

DEADLOCK = '40P01'      # PostgreSQL deadlock_detected
SERIALIZE = '40001'     # serialization_failure

def run_tx(conn, work):
    for attempt in range(4):
        try:
            with conn.transaction():
                return work(conn)
        except Exception as exc:            # driver error carries pgcode
            if getattr(exc, 'pgcode', None) not in (DEADLOCK, SERIALIZE):
                raise
            time.sleep((2 ** attempt) * 0.02 + random.random() * 0.02)
    raise RuntimeError('transaction kept conflicting')

Where this bites vibecoders
Generated multi-step writes rarely have a defined ordering and almost never retry. The result is a rare, unreproducible failure — an occasional “deadlock detected” in the logs and, worse, an exception path that returns an error to the user instead of retrying. Because it happens under concurrency, it will never reproduce locally, and the fix has two parts: impose an order on the writes and add a bounded retry. Both are small; the second is the one that gets skipped.

Where AI coding assistants get this wrong

Updating related rows in a different order in different code paths.
Holding a transaction open across a network call, widening the lock window.
Updating rows in a loop inside one transaction instead of in one statement.
Treating a deadlock error as a permanent failure and returning it to the user.
Retrying with no delay or no cap, turning a collision into a stampede.
Raising the isolation level for everything as a blanket fix.

Checklist

List the write paths that touch more than one table.
Give every path the same row order — usually primary key ascending.
Move validation and external calls outside the transaction.
Add a bounded retry on deadlock and serialisation errors only.
Log each retry with its attempt number so frequency is visible.
Load-test with two overlapping writers before shipping.

FAQ

Is a deadlock a bug in my database?

Normally not. It is a scheduling collision between concurrent transactions, and the database detects and resolves it correctly by aborting one. The bug is in the application if it does not expect that and does not retry. Persistent deadlocks point at a lock-ordering problem in the code, not at the engine.

Should I just use a lower isolation level to avoid deadlocks?

It can reduce lock conflicts, and it trades the guarantee you were relying on. Read-committed still takes write locks, so it does not eliminate deadlocks on writes. The right move is to fix ordering and transaction length first, choose the weakest isolation level your logic actually needs, and keep the retry for the residual cases.

How do I reproduce a deadlock locally?

Open two database sessions and interleave the two lock acquisitions by hand: begin both transactions, update the first row in each, then try to update the other’s row in both. That is the cycle. Application-level tests reproduce it by running two overlapping requests against the same rows with a small random delay.

Does retrying make the problem worse?

If it is unconditional, instant, and unbounded, yes — the transactions collide again immediately. A bounded retry with a small randomised delay lets one win and the other proceed. Because the database raises the deadlock before doing the work it rolled back, a retry is cheap rather than wasteful.