TransactionsGENERALDATABASE-SPECIFICDATABASE-SPECIFICDATABASE-SPECIFIC

Deadlocks in Application Code

Two transactions take the same two locks in opposite orders, each waits for the other, and the database kills one of them — with an error your code has to expect.

What actually happens How to build it

The requirement, the obvious build, and why it breaks

Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.

The question

Why does one of my transactions get aborted with "deadlock detected", and whose fault is it?

The requirement

A transfer endpoint debits one account and credits another. Under load it starts failing intermittently with a deadlock error, and the same request succeeds when retried.

The obvious build

Lock the source account, lock the destination account, move the money, commit. Two row locks, one transaction, obviously correct.

Why it breaks

Request A transfers from account 1 to account 2. Request B transfers from account 2 to account 1, at the same moment.

How it breaks in production

Request A transfers from account 1 to account 2. Request B transfers from account 2 to account 1, at the same moment.
A locks row 1 and asks for row 2. B locks row 2 and asks for row 1. Neither can proceed and neither will give up: a cycle in the wait-for graph.
The database detects the cycle and aborts one transaction. The application sees an error it did not anticipate, on a code path that is correct in isolation.
It is rare at low traffic and common at high traffic, so it arrives as "the new load is breaking the database" rather than as a lock-ordering bug.

Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale

What is actually happening

A deadlock is a cycle in the graph of "transaction X waits for a lock held by transaction Y". No transaction can proceed, and no amount of waiting resolves it (Deadlock).
The database detects the cycle and breaks it by aborting one transaction — the victim — with a specific error. The survivor proceeds. This is a normal, expected outcome, not a corruption event.
The classic cause is inconsistent lock ordering: two code paths acquire the same set of rows in different orders.
It is not the only cause. Updating a set of rows without a deterministic order, escalating a shared lock to an exclusive one, foreign-key checks taking locks on parent rows you did not mention, and index-level gap locks all produce cycles that are not visible in the statement text.
Longer transactions raise the probability quadratically-ish, because the chance of two transactions overlapping on the same rows grows with the time each one holds locks (External Calls Inside a Transaction).
The same cycle can form outside the database entirely: a request holding one pooled connection and waiting for a second from the same pool is a deadlock with no database involvement (Connection Pools).

The cycle, in two requests

Two transfers running at the same instant in opposite directions are enough. Neither transaction is wrong; the pair is. Read the interleaving and note that both code paths are the same function called with swapped arguments — which is why code review does not catch it.

Two transactions, opposite lock order

1-- Request A: transfer 1 -> 2        -- Request B: transfer 2 -> 1
2BEGIN;                                BEGIN;
3UPDATE accounts SET balance =
4  balance - 50 WHERE id = 1;          -- A now holds the lock on row 1
5                                      UPDATE accounts SET balance =
6                                        balance - 30 WHERE id = 2;
7                                      -- B now holds the lock on row 2
8UPDATE accounts SET balance =
9  balance + 50 WHERE id = 2;          -- A waits for B
10                                      UPDATE accounts SET balance =
11                                        balance + 30 WHERE id = 1;
12                                      -- B waits for A  ->  cycle
13 
14-- The database aborts one of them:
15-- Postgres: ERROR 40P01  deadlock detected
16-- MySQL:    ERROR 1213   Deadlock found when trying to get lock

Neither transaction did anything unusual. The cycle exists only because the two acquire the same pair of rows in opposite orders, and that depends entirely on which arguments each caller passed.

Sort, then lock

DATABASE-SPECIFICPostgres syntax. SELECT ... FOR UPDATE with ORDER BY locks in the produced order on both Postgres and InnoDB, but InnoDB may additionally take gap locks at REPEATABLE READ, so an equivalent range-based lock can still contend on rows that do not exist yet.

The fix is not cleverness, it is a total order. If every transaction that touches a set of rows acquires them in the same sequence — sorted by primary key, always, everywhere — a cycle cannot form, because there is no way for one transaction to hold a later lock while waiting for an earlier one.

The transfer becomes: sort the two account ids, lock both in that order, then apply the debit and the credit. The business direction of the transfer no longer influences the lock order at all, which is the property you need.

Locking two accounts

Lock in business order

await withTransaction(async (tx) => {
  await lockAccount(tx, fromId)   // order depends on the caller
  await lockAccount(tx, toId)
  await debit(tx, fromId, amount)
  await credit(tx, toId, amount)
})

Lock in sorted order, then apply

await withTransaction(async (tx) => {
  const [first, second] = [fromId, toId].sort()   // total order
  await tx.query(
    'SELECT id FROM accounts WHERE id IN ($1, $2) ORDER BY id FOR UPDATE',
    [first, second],
  )
  await debit(tx, fromId, amount)     // business direction, after locking
  await credit(tx, toId, amount)
})

Lock order is now a property of the data, not of the request. Two opposite transfers acquire the same locks in the same sequence, so one simply waits for the other and both complete. The ORDER BY inside the locking read matters as much as the sort: without it the engine may take the row locks in scan order, which is not guaranteed to be the order you listed (Lock Ordering).

Recognising which cycle you have

Not every deadlock is two rows and two orders. The engine's deadlock report names the statements and relations involved, and that report is almost always sufficient to identify which of these you are looking at — provided you read it rather than reaching straight for a retry.

Deadlock shapes and their fixes

Trigger	Symptom	Cause	Response
Two code paths lock the same rows in different orders	Deadlocks scale with traffic; retry always succeeds	No global lock order	Sort by primary key before locking, in every path including jobs and admin tools
Bulk `UPDATE ... WHERE` with no `ORDER BY`	Two batch jobs deadlock against each other	Rows locked in plan order, which differs between executions	Add a deterministic `ORDER BY`, or process disjoint key ranges per worker
Read then update the same row	Deadlock between two readers that both then write	Shared lock escalated to exclusive by both	Take the exclusive lock up front with `FOR UPDATE`, or use a single conditional `UPDATE`
Insert into a table with a foreign key	Deadlock on a parent row nobody wrote	The FK check locks the referenced row	Order inserts by parent key; consider deferring the constraint
Concurrent inserts into the same index range (InnoDB)	Deadlock between inserts of different rows	Gap / next-key locks at REPEATABLE READ	Reduce the range contention, or use READ COMMITTED where the semantics allow (Isolation Levels)
Long transaction overlapping short ones	Deadlock rate tracks a dependency's latency	Locks held across a slow call, widening every overlap window	Shorten the bracket first — it is usually the cheapest fix (External Calls Inside a Transaction)
Request holds one pooled connection and needs a second	Total hang, no CPU, no database deadlock reported	Cycle on the pool, which has no detector	Pass the connection down; never acquire a second inside a transaction (Connection Pools)

How to build it

Most important first.

Acquire locks in a deterministic global order — sort by primary key before locking, always. For the transfer, lock the lower account id first regardless of which is the source.
Make the whole transaction retryable and retry it on the deadlock error, with a small bounded attempt count and jitter. The database is telling you to try again (Backoff and Jitter).
Keep transactions short. Duration is the single biggest lever on deadlock frequency, and it is usually easier to shorten than to reorder (Where the Transaction Boundary Goes).
Prefer one statement to a read-then-write pair where possible: UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1 takes one lock and needs no explicit ordering (Atomic Operations).
Add ORDER BY to multi-row updates and deletes so concurrent executions touch rows in the same sequence.
Use SELECT ... FOR UPDATE deliberately and narrowly. Locking more rows than you need is the easiest way to create a cycle (Pessimistic Locking).
Set a lock-wait timeout so a non-deadlock lock wait fails in bounded time instead of hanging until a request timeout somewhere else fires.

What can go wrong

Failure modes

Deadlock rate rising with traffic and being misread as database instability.
A retry that is not safe to run twice, so the retry double-applies an effect (Idempotency in Backends).
Retrying forever under sustained contention, converting deadlocks into a saturated pool and a much worse outage (Retry Storms).
Ordering fixed in the obvious path and missed in the batch job, the admin tool or the migration that touches the same rows.
A lock-wait timeout mistaken for a deadlock: the transaction may not have been rolled back, so blind retry is wrong.
Application-level deadlock on the pool, which no database deadlock detector will ever report — the symptom is a hang with no CPU usage and no errors.
Deadlocks introduced by an index change or a foreign key added later, because the locks taken changed without any application code changing.

What can race

The deadlock itself is the race: two interleavings out of many produce a cycle, which is why it is load-dependent and hard to reproduce (Interleavings: The Schedule Is Part of the Program).
The victim is chosen by the database, so which request fails is nondeterministic. Both callers must handle the error; neither can assume it will be the survivor.
A retried transaction re-races the same rows and can deadlock again, which is why attempts must be bounded and jittered (Thundering Herd).
Application-level deadlock on the connection pool has no detector at all: it hangs until acquire timeouts fire, if you set any (Connection Pool Exhaustion).

Security

Deadlock errors surfaced to callers leak schema details — table and index names appear in engine messages. Map them to a generic conflict response (Not Leaking Your Internals).
An endpoint that lets a caller influence lock order is a denial-of-service primitive: an attacker can drive the deadlock rate deliberately (Resource Limits).
Unbounded retry on conflict amplifies load under attack. Bound the attempts and shed rather than retry when the rate is abnormal (Backpressure).

Misreads

"A deadlock means the database is broken." It means the database detected a cycle and resolved it correctly. The bug is in the lock order, and the error is the diagnosis.
"Deadlocks and lock-wait timeouts are the same thing." A deadlock is a cycle resolved by aborting a victim; a lock-wait timeout is one transaction waiting too long for a lock nobody is waiting on it for. The error codes and the correct responses differ.
"Retrying fixes it." Retrying makes the symptom go away for the caller. Without ordering or shorter transactions, the rate keeps climbing with traffic.
"We do not use explicit locks, so we cannot deadlock." Every UPDATE takes a row lock. Foreign key checks take locks on rows you never named. Explicit locking is not required to form a cycle.
"It is always lock ordering." Often, not always: gap locks, index changes, escalation and cross-pool waits all produce the same error.

Operating it

How you see it in production

Deadlock count as a metric, from the database's own counters, alerted on rate rather than on any single occurrence.
The engine's deadlock report — Postgres logs both statements and the relations involved; InnoDB keeps the latest deadlock in its status output. That report names the cycle and usually the bug (Deadlock Detection: The Waits-For Graph).
Lock wait time and lock waits per transaction, which rise before deadlocks appear (Low CPU, High Latency: Lock Contention).
Retry counts by endpoint. A rising retry rate with a flat error rate is the system absorbing contention successfully — until it is not.

What changes at 10x and 100x

Deadlock probability rises sharply with concurrency on the same rows, so an endpoint that never deadlocked can start doing so purely from a traffic increase.
Hot rows are the underlying problem at scale. A single counter row updated by every request will deadlock and contend no matter how well ordered your locks are; the fix is to stop having one row (Atomic Operations).
At 100x, contention design replaces lock ordering: append-only writes with periodic aggregation, sharded counters, or queueing the mutations to a single writer.
More application instances do not change deadlock behaviour directly — concurrency on the same rows does, and that is a function of traffic shape, not fleet size.

What this costs

Deterministic lock ordering costs a sort and some discipline across every code path that touches the same tables, including ones written later by people who have not read this.
Retrying is correct and costs duplicated work plus an idempotency requirement on anything the transaction did outside the database.
Shorter transactions reduce deadlocks and introduce intermediate states you must design (One Transaction or Two).
Coarser locks — locking a parent row instead of many children — remove deadlocks by removing concurrency. Sometimes that is the right trade; it is always a throughput cost.

Where this applies

Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.

GENERALCycles in a wait-for graph, detection, victim selection and retry are common to every row-locking engine.
DATABASE-SPECIFICDetection and reporting differ. Postgres waits deadlock_timeout (default 1 second) before checking for a cycle, then aborts one transaction with SQLSTATE 40P01 and logs both statements. InnoDB maintains a wait-for graph and detects immediately, returning error 1213 and choosing as victim the transaction that has modified the fewest rows; the details are in SHOW ENGINE INNODB STATUS, which only keeps the most recent one.
DATABASE-SPECIFICInnoDB at REPEATABLE READ takes gap and next-key locks on index ranges, so two inserts into the same gap can deadlock even though they touch different rows — a shape that does not occur on Postgres, where inserts do not lock gaps. Deadlock advice tuned on one engine can miss the cause entirely on the other.
DATABASE-SPECIFICLock waiting is bounded differently: MySQL has innodb_lock_wait_timeout (default 50 seconds) applied to every lock wait, while Postgres leaves lock_timeout disabled by default, so a Postgres transaction can wait indefinitely for a lock unless you set it.

Where the depth lives

This domain teaches the application-side mechanism and hands the rest off.

Databaselocks-and-deadlocks deadlock-detection-internals lock-manager-internals isolation-levels innodb-internals

Performancelock-contention

Concurrencydeadlock deadlock-conditions deadlock-prevention lock-ordering interleavings

OS & Networkingdeadlocks