Deadlocks: Two Transactions, Two Orders, and a Permanent Stop

A deadlock is not a slow query. It is two transactions that each hold what the other needs, neither of which can proceed, and neither of which will ever release anything. The only way out is for one of them to be killed.

The interesting part is how unlikely that is, and why the fact that it is unlikely makes it worse for you.

Locks are not the only way transactions block each other, and a queue is harder to diagnose than a cycle because nothing is obviously wrong. A blocked ALTER TABLE behind a long-running SELECT is the same symptom with a completely different fix, and it is covered in ALTER TABLE and the lock queue.

A deadlock is a cycle

Draw the wait-for graph. A vertex per transaction, an edge from A to B when A is waiting for something B holds.

  no cycle: a wait

    T1 --wants--> T2
    T2 is working, will commit, T1 proceeds
    resolves in bounded time

  with a cycle: a deadlock

    T1 --wants--> T2
    T2 --wants--> T1
    neither holds anything the other will release
    resolves in no time at all, ever

That is the entire definition and the entire difference. A wait has an end. A deadlock does not, so something has to break the cycle deliberately, and the only party positioned to do that is the database.

A transaction visibly locking one row can still participate in a deadlock through table locks, foreign-key checks, unique-index conflicts, triggers, advisory locks, or resources it acquired earlier. In the narrow two-row example, a consistent global order removes that cycle. The rule must cover every relevant lock target, not only the two IDs shown in application code.

  a cycle requires an order violation

    every transaction locks 1 then 2  ->  no cycle possible
    T1 locks 1 then 2, T2 locks 2 then 1  ->  cycle for the interleaving shown

Why you will find it in production and not in a test

For two transactions to deadlock you need them to overlap, in a specific interleaving, on the same rows. Sequential execution cannot produce it. Neither can a functional test where each request is processed one at a time.

  needed interleaving

    T1: lock account 1
                          T2: lock account 2
    T1: lock account 2  ->  waits for T2
                          T2: lock account 1  ->  waits for T1

Under low concurrency that window is narrow and the probability falls off fast. Under load it opens, repeatedly. The deadlock is not a bug that appears when traffic rises. It is a bug whose trigger condition is a concurrency level you only reach in production.

That has a practical consequence for how you reproduce it. A test that runs requests sequentially will never find it, and a load test that does not verify the outcome will find it and not notice, because a deadlock that the database resolves looks like a slightly slower request.

The victim is the good outcome

PostgreSQL checks for a deadlock after deadlock_timeout; its documented default is one second, but the setting is configurable. It then walks the wait graph and aborts one participant.

ERROR:  deadlock detected
DETAIL:  Process 4821 waits for Process 4819; Process 4819 waits for Process 4821.

deadlock_timeout defaults to 1 second, so a genuine deadlock surfaces after roughly that, and a mere slow transaction surfaces as ordinary waiting without a false positive.

It is worth being clear that this is the good outcome, because the instinct is to treat any error as a defect. It is not. Compare the two alternatives:

  database aborts one transaction
    one request gets 40001, the client retries, the other proceeds
    state is correct, one request was slow

  database hangs forever
    both connections are held
    every other request needing a connection now queues behind them
    state is unknown and the outage grows

An engine that detects and reports a deadlock is behaving correctly. The defect is the lock ordering that created it, and the missing piece on your side is the retry.

The transaction that gets aborted is chosen as the cheapest to undo, which is usually the one that has done the least work. So the victim is not random, and you should not assume it will always be the newer or the smaller transaction when you are reasoning about which request the user sees fail.

Ordering removes the whole class

The classic two-account transfer is the minimal example and it is worth writing out, because the fix is legible in it.

-- T1: transfer 100 from account 1 to account 2
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- T2: transfer 50 from account 2 to account 1
BEGIN;
UPDATE accounts SET balance = balance - 50  WHERE id = 2;
UPDATE accounts SET balance = balance + 50  WHERE id = 1;
COMMIT;

Same operation, opposite direction, opposite order. T1 locks 1 then 2. T2 locks 2 then 1. If they overlap, one of them is going to be a victim, every time, forever.

The fix is to decide the order before the transaction and never deviate from it. Every transaction that touches accounts 1 and 2 locks 1 first.

BEGIN;

SELECT id, balance
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

-- validate the transfer, then update both rows before commit

COMMIT;

PostgreSQL applies a top-level locking clause after sorting, so an ordered SELECT ... FOR UPDATE is the direct way to lock the selected rows in a consistent order. At READ COMMITTED, a row’s ordering column can change while the statement waits, so the returned values can appear out of order; do not update the lock-order key inside this workflow.

static async Task TransferAsync(NpgsqlConnection conn, long from, long to, decimal amount)
{
    // the order is decided once, here, and is the same for every caller
    var (first, second) = from < to ? (from, to) : (to, from);

    await using var tx = await conn.BeginTransactionAsync();

    var balances = await LockBalancesInOrderAsync(
        conn, tx, [first, second]);

    var fromBalance = balances[from];
    var toBalance = balances[to];

    if (fromBalance < amount) throw new InsufficientFundsException();

    // recompute both writes from the values read above
    await SetBalanceAsync(conn, tx, from, fromBalance - amount);
    await SetBalanceAsync(conn, tx, to, toBalance + amount);

    await tx.CommitAsync();
}

The values are now validated from one locked snapshot, and direction is kept separate from lock order. The operation still holds both row locks until commit; its safety comes from the shared acquisition order and short transaction, not from moving arithmetic into C#.

Reduce lock scope without breaking atomicity

PostgreSQL row locks are normally held until the transaction ends. Moving arithmetic into C# does not turn this sequence into “lock A, unlock A, lock B” inside one transaction. The first row remains locked when the second is acquired.

Splitting a transfer into two transactions would release the first lock sooner, but it would also permit the debit to commit without the credit. That is not a deadlock fix; it is a different, non-atomic product contract that would need a ledger, idempotent workflow, and reconciliation.

For an atomic transfer, keep both changes in one short transaction and acquire the rows in stable order:

SELECT id, balance
FROM accounts
WHERE id = ANY ($1)
ORDER BY id
FOR UPDATE;

Then validate and update before commit. Do not call another service, wait for user input, or perform unrelated computation while those locks are held. Reducing duration and the number of touched resources lowers contention and the number of possible cycles; consistent ordering removes the known multi-row cycle.

The less visible lock sources still need the same inventory:

• foreign-key checks can lock referenced rows;
• range locking statements can lock many matching rows;
• uniqueness checks can wait on another transaction’s speculative or uncommitted insert;
• triggers can touch tables absent from the submitted statement;
• advisory and table locks participate in the same wait graph;
• remote calls can form a distributed cycle the database cannot detect.

Deadlocks you did not write

The two-lock pattern above is the easy case. The ones that consume time are the locks taken by machinery rather than by your statements.

A foreign key check locks the referenced row. Insert a child row and you now hold a lock on a parent row you never mentioned.

INSERT INTO order_items (order_id, sku) VALUES (88213, 'SKU-4471');
   -> lock on order_items row
   -> FK check takes a row lock on orders 88213 to confirm it exists

Now your insert can deadlock against a transaction that updates the order. You wrote one statement and took two locks, and the second is on a table your query never named.

An index entry can also be a conflict target. Two transactions inserting the same unique value will block on the same index page, and if each holds another lock at the same time you have a deadlock involving no application logic you can see.

And triggers run inside your transaction. A trigger that touches a second table, for an audit row or a denormalised counter, doubles your lock footprint invisibly. A trigger written by someone else, on a table you do not own, doing a lookup, can deadlock your otherwise trivial update.

The way to find these is to read the DETAIL line rather than the message, because it names both processes and you can correlate the process id back to the query.

SELECT pid, state, wait_event_type, wait_event, query, xact_start
FROM pg_stat_activity
WHERE datname = current_database() AND state <> 'idle';

Deadlocks across a service boundary

The version that genuinely hurts is not in the database. It is two services each holding a resource the other needs, held across a network call.

  Service A                          Service B
  ---------                          ---------
  BEGIN; lock row X
  POST /reserve  ----------------->  BEGIN; lock row Y
  <---------------- 200 OK
  commit (needs Y? no, fine)

  now reverse it:

  Service A                          Service B
  BEGIN; lock row Y
  POST /reserve  ----------------->  BEGIN; lock row X
  <---------------- waits
                                    waits

The database is fine in this scenario, because the cycle exists between two application services rather than between two transactions. The database cannot see it, cannot break it, and cannot help. Both sides block until a timeout fires, the connection pool exhausts, and the failure surfaces as a generic request timeout rather than as anything that looks like a concurrency problem.

The fix is the only fix that works for distributed cycles: do not hold a lock across a call you do not control. Check availability, release, then commit the reservation. Or hold the lock and accept that you must design the other service to be callable without holding anything. Whichever you choose, it is a design decision made once rather than a retry that gets added after the incident.

Getting the timeouts to fail cleanly

deadlock_timeout controls when the engine checks for a cycle. lock_timeout controls how long a statement may wait for any lock. They answer different questions and should reflect the operation’s latency budget.

-- bound ordinary lock waits for this operation
SET lock_timeout = '2s';

-- 55P03 lock_not_available: classify against the operation budget
-- 40P01 deadlock_detected: retry the complete transaction when safe

If lock_timeout expires before the deadlock detector runs, a real cycle can surface only as a lock timeout. Keeping the operation’s lock budget above deadlock_timeout allows genuine cycles to be classified as 40P01 while still bounding ordinary contention. A timeout is not automatically safe to retry: retry the complete idempotent transaction, cap attempts and elapsed time, and shed load when a hot resource remains unavailable.

A production-ready architecture

  transaction that needs a second resource
       |
       v
  +-------------------------------------------+
  | can the arithmetic move out of the DB?   |
  +-------------------------------------------+
     | yes -> 1 lock at a time, no deadlock   |
     |                                        |
     | no                                      v
     |        +-------------------------------+
     |        | is the order globally fixed? |
     |        | sorted ids, one lock per key |
     |        +-------------------------------+
     |                    | yes -> safe        |
     |                    | no  -> pick an order
     |                                       and hold to it
     v
  +------------------------------------------------+
  | retry safe full transactions with jitter and a total budget |
  | lock_timeout < deadlock_timeout                |
  | never hold a lock across an outbound call     |
  +------------------------------------------------+

A delivery checklist:

1. Grep for transactions that issue more than one write to the same table. That is where the lock count comes from.
2. Move arithmetic out of UPDATE expressions wherever the values are read anyway. One lock at a time removes the problem rather than the symptom.
3. Where two locks are unavoidable, sort the identifiers first and lock them in that order, with one statement per key.
4. Decide the ordering convention once and write it down. A rule that lives in one developer’s head is not a rule.
5. Retry 40P01 only at the complete transaction boundary. Treat 55P03 as retryable only when the operation is idempotent and the total deadline permits it.
6. Set lock_timeout below deadlock_timeout so contention produces a fast clean error.
7. Read the DETAIL line on a deadlock. It names both processes and points at the statements, which is the only fast way to find the pair.
8. Audit foreign keys on tables you insert into. The parent lock is invisible in your statement and counts.
9. Check triggers on tables you do not own before concluding your own one-statement update cannot deadlock.
10. Never hold a transaction open across an HTTP call to another service. The database cannot break that cycle and you will not get a deadlock error.
11. Watch the deadlock rate as a metric. A rising count after a deploy is the earliest signal of an ordering regression.

Failure stories worth testing

Force the two-account interleaving deliberately

Lock account 1 in one session, lock account 2 in another, then have each try the other. You should get a deadlock in about a second. Do it with the ordering fix in place and you should get a wait instead, which is the proof that the fix works rather than an assumption.

Retry on 40P01 and confirm the final balances are correct

Run the transfer under concurrency with retries enabled and with retries disabled. With retries the invariant holds and some requests take longer. Without them, some transfers fail outright. The comparison is the clearest argument for the retry loop you will be asked to justify.

Set lock_timeout to 30s and deadlock_timeout to 1s, then create contention

Confirm that ordinary contention is reported as a deadlock-detected error rather than as a lock timeout, and that the reverse configuration produces the fast clean error you actually want. This is a two-minute test for a setting that is wrong in a lot of production databases.

Insert a child row while another transaction updates the parent and measure the lock count

Use pg_locks from a third session during the insert. Seeing a lock on a table your INSERT never named is the fastest way to understand that a foreign key is a lock.

Trace a deadlock back to its statements using only the DETAIL line

In a staging environment, force a deadlock, take the DETAIL output, map the process ids to statements through pg_stat_activity, and identify the second lock. If you cannot find it, the DETAIL line is the tool you are missing rather than a log you need to turn on.

Common mistakes

Mistake	What actually happens	Better decision
Testing concurrency sequentially	A sequential test cannot interleave, so it cannot produce a deadlock	Force the interleaving deliberately
Treating a deadlock error as a database defect	It is the detector working correctly	Fix the ordering, add the retry
Assuming you know the victim	PostgreSQL documents the choice as difficult to predict	Do not build logic on which transaction is aborted
Retrying without jitter	The two transactions collide again on the next attempt	Exponential backoff with jitter
Retrying forever	Contention becomes a retry storm and the outage widens	Cap at a few attempts
Updating rows in caller-supplied order	Opposite-direction operations acquire the same rows in reverse	Lock the full set with a stable ORDER BY ... FOR UPDATE
Assuming application arithmetic releases locks	PostgreSQL retains row locks until transaction end	Keep the atomic transaction short; do not split it accidentally
Counting only the locks in your own statements	A foreign key or a trigger takes the second lock	Read pg_locks during the operation
lock_timeout shorter than deadlock_timeout without intent	A real cycle can surface only as a generic lock timeout	Let detection run first when deadlock classification matters
Holding a transaction open across an HTTP call	The cycle is between services, so the database cannot break it	Commit before calling out, or design it out
Assuming a one-statement update cannot deadlock	A foreign key, a trigger, or a unique index is enough	Check what else runs inside the transaction
Fixing the symptom with more retries	The ordering defect stays and the rate rises under load	Fix the order, then add the retry

The complete story in one minute

A deadlock is a cycle in the wait-for graph where each transaction waits for a resource another participant holds. A plain wait is not a deadlock because the holder can release the resource, though an abandoned or long transaction can still make that wait operationally unbounded. A cycle has no natural release, so PostgreSQL aborts one participant. That converts the cycle into a transaction failure the application can retry from a clean boundary.

Deadlocks do not reproduce in a sequential test, but they are entirely testable. Use two real connections, barriers after each first lock, and then release both sessions toward the second lock. That deterministic interleaving is more useful than hoping a load test happens to open the window.

The prevention rule is a consistent order across every resource the operation can lock. Sort the identifiers, lock the full set in that order, validate, write, and commit quickly. PostgreSQL retains row locks until transaction end, so moving arithmetic into the application does not unlock the first row; splitting an atomic transfer into separate transactions merely trades deadlock risk for partial state. Retry a 40P01 from the beginning of the whole transaction, with idempotency, jitter, and a total deadline. Never hold a transaction across a remote call, because that can create a cycle the database cannot see or break.

Technical references

• PostgreSQL explicit locking and deadlocks
• PostgreSQL deadlock_timeout
• PostgreSQL lock_timeout
• PostgreSQL error codes
• PostgreSQL locking clauses