Database deadlocks happen when two transactions each hold a lock the other needs, so neither can move forward — and unlike an application-level deadlock between two threads, the database itself watches for exactly this and breaks it automatically. That is what surprises developers coming from general concurrency concepts: you do not need to detect a database deadlock yourself. You need to know one happened, understand why the engine picked the transaction it killed, and write code that retries correctly.
What changed in 2026
- Deadlock logging got more actionable. Postgres's deadlock log entries now include the full query text of both blocked statements by default in most managed setups, cutting the time it takes to find the offending code path.
- ORMs added built-in retry helpers. Prisma and SQLAlchemy both ship documented patterns for catching a deadlock error code and retrying automatically, rather than leaving every team to hand-roll it.
- Gap-lock-related deadlocks in MySQL got more scrutiny as more teams moved to stricter isolation levels, since InnoDB's gap locks under REPEATABLE READ create deadlock scenarios that do not exist under Postgres's default READ COMMITTED.
How a database actually detects a deadlock
| Engine |
Detection method |
What triggers resolution |
| PostgreSQL |
Builds a wait-for graph among blocked transactions; checks for a cycle |
A cycle found within deadlock_timeout (default 1s) — one transaction is killed |
| MySQL (InnoDB) |
Maintains a similar wait-for graph internally |
Cycle detected immediately; the "smaller" transaction (least work done) is usually chosen as victim |
| SQL Server |
Background "deadlock monitor" thread scans lock graphs on an interval |
Victim chosen by cost, unless DEADLOCK_PRIORITY is set explicitly |
This is worth separating from a plain lock wait: if transaction B is simply waiting for transaction A to finish and release a lock, and A will eventually finish, that is normal contention — B waits, then proceeds. A deadlock is the case where waiting will never resolve on its own, because the wait forms a cycle.
A deadlock, reproduced
Two sessions against the same accounts table, updating two rows in opposite order:
-- Session 1
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1; -- locks row 1
-- (pauses here)
UPDATE accounts SET balance = balance + 50 WHERE id = 2; -- waits for row 2
-- Session 2, started while Session 1 is paused
BEGIN;
UPDATE accounts SET balance = balance - 20 WHERE id = 2; -- locks row 2
UPDATE accounts SET balance = balance + 20 WHERE id = 1; -- waits for row 1, cycle forms
Postgres detects the cycle and returns an error to one session, typically:
ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678; blocked by process 5678.
Process 5678 waits for ShareLock on transaction 1234; blocked by process 1234.
HINT: See server log for query details.
The other session's transaction proceeds and commits normally, unaware a deadlock ever occurred.
How the victim gets chosen
The database is not being arbitrary, but it is also not protecting "your" transaction specifically — it picks whichever transaction is cheapest to abort, usually the one that has done the least work, and rolls that one back so the other can complete. The rolled-back transaction gets a specific deadlock error code (40P01 in Postgres, 1213 in MySQL), which application code should treat as distinct from a generic failure: try the exact same database transaction again, not assume something is broken.
Preventing deadlocks in application code
The single highest-leverage fix is consistent lock order: if every transaction that touches multiple rows always acquires them in the same order — say, always by ascending primary key — a circular wait cannot form, since there is no way for two transactions to wait on each other in opposite directions. Keeping transactions short reduces the window further. It also pays to check whether a composite index is missing on a column used in a multi-row UPDATE or DELETE, since a scan locking far more rows than necessary — for lack of a selective index — raises the odds two transactions overlap on rows neither needed to touch.
FAQ
Does every deadlock mean there is a bug in the application?
Not necessarily. Under real concurrency, an occasional deadlock is expected even in well-designed schemas. The bug is not catching the error and retrying — an application that lets a deadlock surface as an unhandled error to a user has a bug; the deadlock itself is often just contention.
How is a deadlock different from a lock wait timeout?
A lock wait timeout fires when a transaction waits longer than a configured limit for a lock that would eventually free up — the other transaction just has not finished yet. A deadlock is a cycle that would never resolve on its own, detected structurally, not by a clock running out.
Should application code retry automatically on a deadlock error?
Yes, for the specific deadlock error code, with a short backoff. Retrying other errors blindly is usually wrong; retrying a deadlock specifically is the documented, expected pattern.
Where to go next