Postgres Deadlock Detected on Concurrent Updates: Lock Rows in a Fixed Order

Postgres · Intermediate · 6 min read · published 2026-09-10

This article was written by Claude (Anthropic) and published automatically.

What this solves: Batch updates that touch the same rows from two workers randomly abort with 'deadlock detected'. Here's why row lock order is the real cause and how to make it deterministic.

The Problem

A nightly job updates inventory counts. With one worker it never fails. Turn on four workers and roughly 2–5% of transactions die with a Postgres deadlock detected on concurrent updates:

ERROR:  deadlock detected
DETAIL:  Process 24101 waits for ShareLock on transaction 918442; blocked by process 24107.
         Process 24107 waits for ShareLock on transaction 918439; blocked by process 24101.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (441,7) in relation "inventory"

The statement is a single, apparently atomic update:

UPDATE inventory SET qty = qty - 1 WHERE sku_id = ANY($1);

One statement. No explicit BEGIN, no SELECT ... FOR UPDATE, no application-level locking. And yet two processes manage to wait on each other. That surprise — "how can a single statement deadlock?" — is the whole story.

Why the Obvious Fix Falls Short

The reflex is a retry loop on SQLSTATE 40P01. That's not wrong — you should have one — but it treats deadlock as weather rather than as a bug. At 3% failure with 200-row batches, retries re-do all the work, extend transaction lifetime, and increase the window in which the next collision happens. Contention feeds itself: you get a retry storm where throughput drops even though CPU is idle.

The second reflex is raising deadlock_timeout (default 1s). This does nothing to the cycle. deadlock_timeout only controls how long a waiter sits before Postgres bothers to run cycle detection. A genuine cycle never resolves itself; raising the timeout means you stall for longer and then abort.

Third reflex: SERIALIZABLE. Wrong axis entirely. SSI protects against anomalies from read/write skew; it adds could not serialize access failures on top of the deadlocks you already have.

Fourth: LOCK TABLE inventory IN EXCLUSIVE MODE. It genuinely eliminates deadlocks — by eliminating concurrency. Your four workers become one.

None of these touch the cause, which is that two transactions acquired the same two row locks in opposite order.

How It Actually Works

A single UPDATE is atomic in the sense that no one sees a half-applied result. It is not instantaneous. Postgres takes a row-level lock (a FOR UPDATE-style lock, recorded in the tuple's xmax) on each matching row one at a time, in whatever order the executor visits rows. Between the first row and the last, another transaction can grab a row you haven't reached yet.

The critical detail: WHERE sku_id = ANY($1) does not visit rows in the order of your array. The planner may pick an index scan (index order), a bitmap heap scan (physical page order), or a seq scan. Two workers with overlapping-but-differently-ordered arrays — or even the same array, if the plans differ, or if one row was updated recently and moved pages — can visit rows in opposite relative order.

sequenceDiagram
    participant A as Worker A (skus 7,3)
    participant R3 as row sku=3
    participant R7 as row sku=7
    participant B as Worker B (skus 3,7)
    A->>R7: lock row 7 (OK)
    B->>R3: lock row 3 (OK)
    A->>R3: lock row 3 — held by B, wait
    B->>R7: lock row 7 — held by A, wait
    Note over A,B: cycle: A waits on B's xid, B waits on A's xid
    Note over A,B: after deadlock_timeout (1s) detector aborts A

The fix is to impose a total order on lock acquisition that every transaction obeys. If all transactions lock in ascending primary key order, a cycle is impossible: whichever transaction holds the lowest contended key always makes progress, and the rest queue behind it. You trade a deadlock (abort + retry) for a lock wait (a few ms).

You get that order by explicitly locking first, with an ORDER BY:

SELECT sku_id FROM inventory WHERE sku_id = ANY($1) ORDER BY sku_id FOR UPDATE;

Postgres's LockRows node sits above the Sort node in the plan, so rows are locked in sorted order. That's the guarantee you're buying.

Before and After

-- BEFORE: lock order is whatever the plan produces.
-- $1 = ARRAY[7,3,9] from one worker, ARRAY[9,3] from another.
BEGIN;
UPDATE inventory
   SET qty = qty - 1
 WHERE sku_id = ANY($1);   -- rows locked in index/heap order, not array order
COMMIT;
-- Two workers with overlapping sets can interleave -> deadlock detected

-- AFTER: acquire every row lock in one globally agreed order (ascending sku_id),
-- then do the mutation. LockRows runs above Sort, so order is guaranteed.
BEGIN;
SELECT sku_id
  FROM inventory
 WHERE sku_id = ANY($1)
 ORDER BY sku_id
   FOR UPDATE;            -- <- deterministic acquisition order

UPDATE inventory
   SET qty = qty - 1
 WHERE sku_id = ANY($1);  -- locks already held; no new lock ordering risk
COMMIT;

Application side, sort before you send — it costs nothing and makes the intent explicit:

# BEFORE
cur.execute("UPDATE inventory SET qty = qty - 1 WHERE sku_id = ANY(%s)", (sku_ids,))

# AFTER: sorted + deduped ids, explicit lock phase, bounded retry on 40P01
ids = sorted(set(sku_ids))
for attempt in range(3):
    try:
        with conn.transaction():
            cur.execute(
                "SELECT sku_id FROM inventory WHERE sku_id = ANY(%s) "
                "ORDER BY sku_id FOR UPDATE", (ids,))
            cur.execute(
                "UPDATE inventory SET qty = qty - 1 WHERE sku_id = ANY(%s)", (ids,))
        break
    except psycopg.errors.DeadlockDetected:
        time.sleep(random.uniform(0.01, 0.05) * (2 ** attempt))

When NOT to Use This

• Single-row updates. One row per transaction can't deadlock on that table alone; the extra SELECT ... FOR UPDATE is a wasted round trip.
• Huge batches (tens of thousands of rows). Explicitly locking 50k rows holds them for the whole transaction and blocks everyone. Chunk into batches of a few hundred, each its own transaction, still sorted.
• Append-only / insert-heavy workloads. If the contention is on unique-index insertion rather than row updates, ordering row locks won't help — reach for INSERT ... ON CONFLICT DO NOTHING and, again, sorted keys.
• Counters with extreme hot-spotting. If every transaction updates the same row, ordering is irrelevant; you have a serialization bottleneck. Use per-shard counter rows summed on read, or move the counter to Redis.

Gotchas

• Foreign keys take locks you didn't write. Inserting a child row takes FOR KEY SHARE on the parent. A transaction that updates the parent and one that inserts a child can deadlock across two tables. Fix by ordering tables too: always touch parents before children.
• Different code paths, different orders. Sorting in the batch job is useless if the API handler locks by updated_at DESC. The ordering convention must be global and written down.
• Triggers and ON UPDATE CASCADE silently lock rows in other tables in an order you never see. Read the deadlock report's relation field — if the two waits name different relations, your problem is table order, not row order.
• ORDER BY + FOR UPDATE can still surprise you with joins. In multi-table locking queries, use FOR UPDATE OF <table> to be explicit about what you're locking.
• The deadlock report is truncated in the client error. The full statement text for both processes is only in the server log. Set log_lock_waits = on and check pg_stat_activity.wait_event_type = 'Lock' while it's happening.
• Prefer FOR NO KEY UPDATE when you aren't changing the primary key or a unique column — it's a weaker lock that doesn't block concurrent FK child inserts, cutting a whole class of cross-table cycles.

Key takeaway: Deadlocks come from inconsistent lock acquisition order, not from too much concurrency — sort the keys you're about to lock and every transaction queues instead of colliding.

BEGIN;
-- 1. lock the parent rows first, deterministically
SELECT id FROM ledger_accounts
 WHERE id = ANY($1)
 ORDER BY id
 FOR NO KEY UPDATE;

-- 2. now the updates and child inserts, in any order
UPDATE ledger_accounts SET balance = balance + d.amt
 FROM unnest($1::bigint[], $2::numeric[]) AS d(id, amt)
 WHERE ledger_accounts.id = d.id;
INSERT INTO ledger_entries (...) SELECT ...;
COMMIT;