Nov 20, 20265 min read

Postgres Deadlocks: Why They Happen and How to Fix Them

How to read a deadlock detected error, the access patterns that cause most Postgres deadlocks, lock ordering and smaller transactions as fixes, foreign key and batch upsert surprises, and retrying safely when one still happens.

Illustration for Postgres Deadlocks: Why They Happen and How to Fix Them

The Postgres error deadlock detected tends to appear under load and disappear when you try to reproduce it, which makes it feel random. It is not: a deadlock means two transactions locked the same rows in a different order, and the log message says exactly which ones.

This guide covers how to read that message, the patterns that cause most deadlocks, and how to remove them.

Reading the error

When a transaction waits on a lock for longer than deadlock_timeout (1 second by default), Postgres checks for a cycle in the wait graph. If it finds one, it aborts one transaction with SQLSTATE 40P01 and logs the cycle:

ERROR: deadlock detected DETAIL: Process 4120 waits for ShareLock on transaction 88213; blocked by process 4377. Process 4377 waits for ShareLock on transaction 88209; blocked by process 4120. Process 4120: UPDATE accounts SET balance = balance - 500 WHERE id = 7 Process 4377: UPDATE accounts SET balance = balance - 200 WHERE id = 3 HINT: See server log for query details. CONTEXT: while updating tuple (12,4) in relation "accounts"

Read it as: process 4120 already holds a row that 4377 wants, and vice versa. The statements shown are the ones that were waiting, not the ones that took the first locks, so look at what each transaction did earlier. The deadlock section of the locking docs walks through the same two-transfer example.

The usual causes

PatternWhy it deadlocks
Transfers or moves between two rowsA to B locks A then B, B to A locks B then A
Batch updates or upserts in arbitrary orderTwo batches touching overlapping rows lock them in different orders
Foreign keysInserting a child takes a FOR KEY SHARE lock on the parent, which conflicts with an update on the parent's key
Long transactions mixing reads and writesMore locks held for longer, more chances to overlap
Explicit LOCK TABLE in different ordersSame cycle at table level

Enable log_lock_waits so every wait longer than deadlock_timeout is logged, which reveals the contention that precedes deadlocks even when no cycle forms. Both settings are covered in the lock management parameters.

Fix 1: lock in a consistent order

If every transaction takes locks in the same global order, no cycle can form. For a transfer, lock both rows up front, sorted by primary key:

BEGIN; SELECT id FROM accounts WHERE id IN (3, 7) ORDER BY id FOR UPDATE; UPDATE accounts SET balance = balance - 500 WHERE id = 7; UPDATE accounts SET balance = balance + 500 WHERE id = 3; COMMIT;

For batch writes, sort the input by the conflict key before building the INSERT ... ON CONFLICT or the UPDATE ... FROM (VALUES ...), so two overlapping batches walk the rows in the same order.

Fix 2: shorter transactions

A transaction that calls an external API or waits on user input while holding row locks makes every overlap last longer. Do the slow work before BEGIN or after COMMIT, and keep the transaction to the reads and writes that must be atomic. This reduces deadlocks and lock waits alike.

Fix 3: retry what remains

Some deadlocks are hard to rule out completely, for example with foreign keys under heavy concurrent inserts. Postgres already rolled back the victim transaction, so the safe response is to retry the whole transaction from the start, a small number of times with jitter. Treat 40P01 and 40001 (serialization failure) the same way. The error codes appendix lists both under transaction rollback.

Best fit: consistent lock order and short transactions as the fix, a bounded retry for the rare deadlocks that remain.

Deadlock-safe transfers in Encore

Encore

Encore is a backend framework with built-in infrastructure, and its database package maps Postgres errors to codes, so a deadlock can be recognized with sqldb.ErrCode instead of parsing SQLSTATE strings. A transfer endpoint that orders its locks and retries the rare remaining deadlock looks like this:

Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

package ledger import ( "context" "math/rand/v2" "time" "encore.dev/beta/errs" "encore.dev/storage/sqldb" "encore.dev/storage/sqldb/sqlerr" ) var db = sqldb.NewDatabase("ledger", sqldb.DatabaseConfig{Migrations: "./migrations"}) type TransferParams struct { From int64 `json:"from"` To int64 `json:"to"` AmountCents int64 `json:"amount_cents"` } //encore:api auth method=POST path=/transfers func Transfer(ctx context.Context, p *TransferParams) error { if p.From == p.To || p.AmountCents <= 0 { return &errs.Error{Code: errs.InvalidArgument, Message: "invalid transfer"} } for attempt := 1; ; attempt++ { err := transferOnce(ctx, p) if sqldb.ErrCode(err) != sqlerr.DeadlockDetected || attempt == 3 { return err } time.Sleep(time.Duration(20+rand.IntN(80)) * time.Millisecond) } } func transferOnce(ctx context.Context, p *TransferParams) error { tx, err := db.Begin(ctx) if err != nil { return err } defer tx.Rollback() // Lock both accounts in id order so concurrent transfers cannot form a cycle. if _, err := tx.Exec(ctx, `SELECT id FROM accounts WHERE id IN ($1, $2) ORDER BY id FOR UPDATE`, p.From, p.To); err != nil { return err } res, err := tx.Exec(ctx, `UPDATE accounts SET balance_cents = balance_cents - $2 WHERE id = $1 AND balance_cents >= $2`, p.From, p.AmountCents) if err != nil { return err } if res.RowsAffected() == 0 { return &errs.Error{Code: errs.FailedPrecondition, Message: "insufficient balance"} } if _, err := tx.Exec(ctx, `UPDATE accounts SET balance_cents = balance_cents + $2 WHERE id = $1`, p.To, p.AmountCents); err != nil { return err } return tx.Commit() }

encore run starts Postgres locally, and a test can fire opposite transfers between the same two accounts from many goroutines to confirm no deadlock escapes the retry. Each attempt shows up in the request's trace with its queries, which makes lock waits visible during development. In production the database runs on RDS or Cloud SQL in your own account, as the AWS infrastructure docs describe.

An AI coding agent extending the ledger can follow the lock-ordering helper already in the service, and a reviewer can check the ORDER BY id FOR UPDATE line rather than reasoning about every interleaving.

Best fit: services with concurrent writes to shared rows, such as balances, inventory or counters.

How to approach it

Turn on log_lock_waits, read the next deadlock message for the two statements involved, and find where those transactions take their first locks. Order the locks, move slow work out of the transaction, and add a bounded retry on 40P01 for what remains.

Frequently asked questions

What causes deadlock detected in Postgres?

Two or more transactions each hold a lock the other needs, most often because they update the same rows in a different order. Postgres detects the cycle after deadlock_timeout, one second by default, and aborts one of the transactions with SQLSTATE 40P01.

How do I prevent deadlocks in Postgres?

Acquire row locks in a consistent order, for example by locking with SELECT ... ORDER BY id FOR UPDATE before updating, keep transactions short, and sort the rows in batch inserts and upserts. Retry the aborted transaction for the rare deadlocks that remain.

Is it safe to retry a transaction after a deadlock?

Yes. The aborted transaction was rolled back completely, so retrying the whole transaction from the start is safe, as long as the code does not repeat side effects outside the database, such as sending an email, on every attempt.

How do I detect a deadlock error in Encore?

Call sqldb.ErrCode(err) and compare it with sqlerr.DeadlockDetected. Wrap the transaction in a small retry loop that starts a fresh transaction on that code and returns any other error unchanged.

Deploy to your cloud on AWS and Google Cloud
without managing infrastructure

Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders