PostgreSQL

PostgreSQL "Deadlock Detected" During Concurrent Batch Updates

Deadlocks during concurrent batch updates specifically (as opposed to normal application traffic) almost always come down to multiple batch processes updating overlapping sets of rows in different orders — a pattern that's easy to introduce when a batch job processes rows in whatever order a query happens to return them, rather than a consistent, predictable order every time.

The Problem

Running multiple batch update jobs concurrently (or a single batch job with parallel workers) results in one or more failing with:

ERROR:  deadlock detected
DETAIL:  Process 12345 waits for ShareLock on transaction 67890; blocked by process 54321.
Process 54321 waits for ShareLock on transaction 11223; blocked by process 12345.
HINT:  See server log for query details.

This might happen consistently every time the batch jobs run concurrently, or only intermittently depending on exact timing and data overlap between runs.

Two batch jobs, opposite row order Batch A updates rows 1, 2, 3... Batch B updates rows 3, 2, 1... collision on shared rows

Why It Happens

PostgreSQL deadlocks occur when two transactions each hold a lock the other needs, in a circular wait pattern. For batch updates specifically, this typically arises from:

  • Two batch jobs (or parallel workers within one job) fetching and processing rows in different orders — often because the underlying SELECT feeding the batch has no explicit ORDER BY, so PostgreSQL is free to return rows in whatever order is most efficient for that specific query execution, which can genuinely differ between two concurrent executions of the same query
  • Batch jobs processing overlapping ranges of data concurrently, where the same specific rows happen to be touched by more than one job around the same time
  • A batch update joining across multiple tables, where different jobs lock the involved tables' rows in different sequences depending on the specific join order the query planner chose for that particular execution
  • Insufficient batching granularity — a single enormous transaction touching a huge number of rows increases the statistical likelihood of overlapping with another concurrent transaction's row set, compared to many smaller, more surgical transactions

The Fix

The most direct and reliable fix is ensuring every batch process updates rows in a consistent, predictable order — typically by primary key — rather than whatever order a query happens to return:

UPDATE orders SET status = 'processed'
WHERE id IN (
    SELECT id FROM orders WHERE status = 'pending'
    ORDER BY id
    LIMIT 1000
);

With every concurrent batch process consistently ordering by the same column, two jobs touching overlapping rows will naturally acquire locks in the same relative order as each other, eliminating the circular wait pattern that causes deadlocks in the first place.

Reduce batch size to shrink each individual transaction's lock footprint, processing more, smaller batches rather than fewer, larger ones — this reduces both the duration any given set of locks is held and the statistical chance of two jobs' row sets overlapping significantly at any given moment:

-- Process in smaller chunks with a loop, rather than one enormous update
DO $$
DECLARE
    rows_updated INTEGER;
BEGIN
    LOOP
        UPDATE orders SET status = 'processed'
        WHERE id IN (
            SELECT id FROM orders WHERE status = 'pending'
            ORDER BY id LIMIT 500
        );
        GET DIAGNOSTICS rows_updated = ROW_COUNT;
        EXIT WHEN rows_updated = 0;
    END LOOP;
END $$;

Use SELECT ... FOR UPDATE SKIP LOCKED for batch processing patterns where multiple workers pull from a shared queue of rows to process, which explicitly avoids the deadlock scenario entirely by having each worker simply skip any row currently locked by another worker, rather than waiting on it and potentially creating a circular dependency:

SELECT id FROM orders
WHERE status = 'pending'
ORDER BY id
LIMIT 500
FOR UPDATE SKIP LOCKED;

This pattern is specifically designed for exactly this kind of concurrent batch-worker scenario, and is generally the most robust solution when you have genuinely parallel workers competing for the same underlying set of rows to process.

Implement retry logic at the application level for the specific deadlock error, since even with the above precautions significantly reducing frequency, an occasional deadlock under sufficiently high concurrency is expected, normal behavior that a well-designed batch system should gracefully recover from rather than treating as a fatal, unretriable failure:

except psycopg2.errors.DeadlockDetected:
    time.sleep(random.uniform(0.1, 0.5))  # brief random delay before retry
    retry_batch()

Still Not Working?

If deadlocks persist even with consistent ordering and smaller batches, check the PostgreSQL logs for the full deadlock detail output, which names the exact queries and lock types involved on both sides of the conflict — this level of detail often reveals a less obvious contributing factor, like an index update or a trigger firing additional row locks beyond what the visible UPDATE statement itself would suggest:

SET log_lock_waits = on;
SET deadlock_timeout = '1s';
-- then review postgresql.log for the detailed DETAIL section of the next deadlock

You might also like