Fix: PostgreSQL ERROR: deadlock detected

ERROR: deadlock detected

The first time I hit a deadlock in production I assumed I had a bug in my locking, and I did, just not the one I expected. Two transactions were updating the same two rows in opposite orders, and roughly once a second they would tangle and Postgres would shoot one of them. I learned that “deadlock detected” is almost never a Postgres problem; it is a lock-ordering problem in your own code, and the durable fix is to make every transaction touch rows in the same order. In my experience the retry loop everyone reaches for first is a band-aid, consistent ordering is the actual cure.

Your application logs show:

ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678; blocked by process 9012.
Process 9012 waits for ShareLock on transaction 1234; blocked by process 1234.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,1) in relation "orders"

Or from your ORM:

OperationalError: deadlock detected

org.postgresql.util.PSQLException: ERROR: deadlock detected

Two or more transactions are waiting for each other to release locks, creating a circular dependency. PostgreSQL detects the deadlock after a timeout and kills one of the transactions to break the cycle.

How a Deadlock Cycle Forms

A deadlock occurs when:

1. Transaction A locks Row 1 and then tries to lock Row 2.
2. Transaction B locks Row 2 and then tries to lock Row 1.
3. Both are waiting for the other, neither can proceed.

PostgreSQL’s deadlock detector (controlled by deadlock_timeout, default 1 second) periodically checks for these cycles. When it finds one, it aborts one of the transactions with the “deadlock detected” error. The error always carries SQLSTATE 40P01, which is the value your retry logic should match on, string-matching the message text is brittle because it gets localized in some distributions.

The reason a victim is chosen rather than both waiters being held forever is straightforward: PostgreSQL implements pessimistic locking and the only way out of a true cycle is to break it. The planner does not predict the cycle in advance, it only detects the cycle once deadlock_timeout elapses without progress. That is why a deadlock looks like “everything was fine for one second, then suddenly an error”, the detector simply was not running during that second. The victim is typically the transaction that requested the lock that closed the cycle, but PostgreSQL does not guarantee which one will be killed, so do not write code that assumes a particular transaction will survive.

Lock conflicts that lead to deadlocks involve more lock modes than just the obvious ROW EXCLUSIVE from UPDATE. A SELECT ... FOR UPDATE takes FOR UPDATE on the row, SELECT ... FOR NO KEY UPDATE takes a weaker mode introduced in 9.3 (Sep 2013) that lets foreign-key checks run concurrently, and SELECT ... FOR SHARE allows multiple readers. Foreign-key validation itself takes a KEY SHARE lock on the parent row. Mixing these modes inconsistently across transactions creates non-obvious cycles that are not visible just by reading the UPDATE statements.

Common causes:

• Inconsistent lock ordering. Different transactions lock rows in different orders.
• Long-running transactions. Transactions that hold locks for extended periods increase deadlock chances.
• Bulk updates on the same table. Multiple concurrent updates touching overlapping rows.
• Foreign key cascading. Updates to parent tables that cascade to child tables can create unexpected lock patterns.
• Explicit table locks. Using LOCK TABLE or SELECT ... FOR UPDATE in conflicting patterns.
• Index page locks during concurrent inserts. Some index types serialize concurrent inserts. The PostgreSQL docs note that hash indexes can deadlock because their bucket locks are held longer than a single operation, and GIN does several key insertions per row, which widens the contention window. B-tree, GiST, and SP-GiST take only short-term page locks released immediately, so they rarely contribute.

Version History That Changes the Failure Mode

Deadlock detection has been in PostgreSQL since the early 7.x days, but the tooling for diagnosing deadlocks improved significantly over the last two decades. If you are running an older version, expect fewer signals and worse defaults:

• PostgreSQL 8.3 (Feb 2008), log_lock_waits added. Before this, PostgreSQL silently waited on locks and you only saw the final deadlock. With log_lock_waits = on, the server logs any session that has been blocked for longer than deadlock_timeout, even when no deadlock occurs. This is the single best diagnostic you can enable. It is off by default, so most fresh installs do not have it.
• PostgreSQL 9.3 (Sep 2013), FOR NO KEY UPDATE and FOR KEY SHARE. These weaker lock modes let foreign-key checks run concurrently with UPDATE statements that do not change key columns, dramatically reducing a class of foreign-key deadlocks. If you are on 9.2 or earlier, every UPDATE on a row takes FOR UPDATE, which blocks all foreign-key validation against that row.
• PostgreSQL 9.6 (Sep 2016), pg_blocking_pids(pid). Before 9.6, finding which session blocked another required a hand-written join against pg_locks plus pg_stat_activity. pg_blocking_pids returns a clean array of blocker PIDs. Most “show me current lock waits” snippets you find online assume 9.6+.
• PostgreSQL 9.6 (Sep 2016), wait_event and wait_event_type in pg_stat_activity. These columns replaced the old boolean waiting column and surface not just heavyweight Lock waits but lightweight locks and buffer pins too. PostgreSQL 10 then broadened the catalog of wait events, adding IO, Client, and other categories. When debugging “the transaction is stuck,” check wait_event_type first to see whether it is even a lock at all.
• PostgreSQL 14 (Sep 2021), pg_locks.waitstart. A new timestamp with time zone column records when each lock wait began, so you can read how long a session has been blocked straight from pg_locks instead of joining it against pg_stat_activity. (Note PostgreSQL’s deadlock detection stays reactive, it waits for deadlock_timeout and then breaks the cycle; there is no predictive detector.)
• PostgreSQL 15 (Oct 2022), MERGE statement. MERGE evaluates its match conditions and writes in a single statement, but it still takes the same row-level locks as the equivalent INSERT/UPDATE/DELETE. People sometimes assume MERGE is atomic in a way that prevents deadlocks, it is not. If you are migrating from manual upserts to MERGE, audit lock ordering the same way you would for any multi-row write.
• PostgreSQL 16 (Sep 2023), pg_stat_io. New view exposes I/O statistics per backend, helping rule out “is this a lock wait or a disk stall?” without falling back to OS-level tools.

Fix 1: Lock Rows in a Consistent Order

The most effective fix. If all transactions lock rows in the same order, deadlocks cannot occur:

Broken, inconsistent order:

-- Transaction A:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction B (opposite order):
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

Fixed, sort by primary key:

-- Transaction A:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction B (same order):
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;

In application code:

def transfer(from_id, to_id, amount):
    # Always lock lower ID first
    first_id = min(from_id, to_id)
    second_id = max(from_id, to_id)

    cursor.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s",
                   (amount if first_id == from_id else -amount, first_id))
    cursor.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s",
                   (amount if second_id == to_id else -amount, second_id))

The simplest ordering strategy I have found is to always touch rows sorted by primary key. It needs no shared convention and no clever logic, every transaction independently sorts the ids it is about to lock, and because they all sort the same way, the cycle can never form.

Fix 2: Retry the Failed Transaction

Deadlocks are sometimes unavoidable in high-concurrency systems. The application should catch and retry:

Python:

import time
import random
from psycopg2 import OperationalError

MAX_RETRIES = 3

def execute_with_retry(func):
    for attempt in range(MAX_RETRIES):
        try:
            return func()
        except OperationalError as e:
            # Match the SQLSTATE, not the message text (the text gets localized)
            if e.pgcode == '40P01' and attempt < MAX_RETRIES - 1:
                # Exponential backoff with jitter so the two victims don't collide again
                time.sleep(0.1 * (2 ** attempt) + random.uniform(0, 0.1))
                continue
            raise

Java:

int maxRetries = 3;
for (int attempt = 0; attempt < maxRetries; attempt++) {
    try {
        executeTransaction();
        break;
    } catch (PSQLException e) {
        if (e.getSQLState().equals("40P01") && attempt < maxRetries - 1) {
            // Exponential backoff with jitter
            Thread.sleep((long) (100 * Math.pow(2, attempt) + Math.random() * 100));
            continue;
        }
        throw e;
    }
}

Django:

from django.db import OperationalError
from django.db import transaction

@transaction.atomic
def process_order(order_id):
    try:
        order = Order.objects.select_for_update().get(id=order_id)
        order.status = "processed"
        order.save()
    except OperationalError:
        # Retry logic here
        pass

The mistake that quietly makes this worse is retrying with no delay. Two transactions that just deadlocked will, if you fire them both again at once, deadlock again on the same rows, so a tight retry loop can spin without ever making progress. Add randomized exponential backoff so the two victims come back at slightly different times and one of them gets a clean run.

Fix 3: Reduce Transaction Duration

Shorter transactions hold locks for less time, reducing deadlock probability:

Broken, long transaction with user interaction:

cursor.execute("BEGIN")
cursor.execute("SELECT * FROM orders WHERE id = 1 FOR UPDATE")
# ... wait for user input or slow API call ...
cursor.execute("UPDATE orders SET status = 'confirmed' WHERE id = 1")
cursor.execute("COMMIT")

Fixed, minimize lock duration:

# Do slow work BEFORE the transaction
user_input = get_user_confirmation()
api_result = call_external_api()

# Quick transaction with minimal lock time
cursor.execute("BEGIN")
cursor.execute("UPDATE orders SET status = %s WHERE id = 1", (api_result,))
cursor.execute("COMMIT")

Use batch operations instead of row-by-row:

-- Slow — locks one row at a time, holding earlier locks while locking later ones
UPDATE orders SET status = 'shipped' WHERE id = 1;
UPDATE orders SET status = 'shipped' WHERE id = 2;
UPDATE orders SET status = 'shipped' WHERE id = 3;

-- Faster — single statement, single lock acquisition
UPDATE orders SET status = 'shipped' WHERE id IN (1, 2, 3);

Fix 4: Use SELECT FOR UPDATE with SKIP LOCKED

For queue-like patterns where multiple workers process rows concurrently:

-- Worker picks up a row, skipping any that are locked by other workers
BEGIN;
SELECT id, data FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- Process the job...

UPDATE jobs SET status = 'done' WHERE id = <selected_id>;
COMMIT;

SKIP LOCKED tells PostgreSQL to skip rows that are currently locked by other transactions, instead of waiting (and potentially deadlocking).

Alternative, FOR UPDATE NOWAIT:

SELECT * FROM jobs WHERE id = 1 FOR UPDATE NOWAIT;

NOWAIT raises an error immediately if the row is locked, instead of waiting. Your application can then try a different row.

Fix 5: Fix Foreign Key Deadlocks

Foreign keys create implicit locks on parent tables when child rows are inserted, updated, or deleted. These locks can cause unexpected deadlocks:

-- Transaction A:
INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
-- Takes a FOR KEY SHARE lock on the parent orders row (order_id = 1)

-- Transaction B:
INSERT INTO order_items (order_id, product_id) VALUES (1, 200);
-- Also takes FOR KEY SHARE on the same orders row (order_id = 1)

-- If both then try to UPDATE that orders row, each needs to upgrade the
-- shared lock the other already holds, and that is the deadlock.

Fix: Lock the parent row first:

-- Transaction A:
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
INSERT INTO order_items (order_id, product_id) VALUES (1, 100);

-- Transaction B:
SELECT * FROM orders WHERE id = 1 FOR UPDATE;  -- Waits here, no deadlock
INSERT INTO order_items (order_id, product_id) VALUES (1, 200);

By explicitly locking the parent row first, both transactions queue up on the same lock instead of creating a cycle.

Fix 6: Monitor and Diagnose Deadlocks

Enable deadlock logging:

In postgresql.conf:

log_lock_waits = on
deadlock_timeout = 1s

This logs all lock wait events and deadlocks to the PostgreSQL log.

Query current locks:

SELECT
    pid,
    pg_blocking_pids(pid) AS blocked_by,
    query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Detailed lock information:

SELECT
    l.pid,
    l.locktype,
    l.mode,
    l.granted,
    a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
ORDER BY l.pid;

Check for idle-in-transaction sessions:

SELECT pid, state, query, now() - xact_start AS duration
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY duration DESC;

Idle-in-transaction sessions hold locks indefinitely. Set a timeout to prevent this:

ALTER DATABASE mydb SET idle_in_transaction_session_timeout = '5min';

Fix 7: Use Advisory Locks

For application-level locking that does not conflict with row locks:

-- Acquire an advisory lock (blocks until available)
SELECT pg_advisory_lock(hashtext('process_order_' || order_id::text));

-- Do work...

-- Release the lock
SELECT pg_advisory_unlock(hashtext('process_order_' || order_id::text));

Try-lock variant (non-blocking):

SELECT pg_try_advisory_lock(12345);
-- Returns true if acquired, false if already locked

Advisory locks are not tied to tables or rows. They are application-defined and do not interfere with regular DML operations.

Prefer the transaction-scoped variants in pooled apps. pg_advisory_lock is held for the whole session, so if your work throws before pg_advisory_unlock runs, the lock leaks, and on a pooled connection the next request that reuses it inherits the orphaned lock. pg_advisory_xact_lock (and pg_try_advisory_xact_lock) release automatically at COMMIT or ROLLBACK, which is almost always what you want:

BEGIN;
SELECT pg_advisory_xact_lock(hashtext('process_order_' || order_id::text));
-- Do work; the lock releases automatically when the transaction ends
COMMIT;

Fix 8: Tune deadlock_timeout

The deadlock_timeout setting controls how long PostgreSQL waits before checking for deadlocks:

SHOW deadlock_timeout;
-- Default: 1s

• Lower value (100ms-500ms): Detects deadlocks faster, but adds CPU overhead from frequent checks.
• Higher value (5s-10s): Less overhead, but deadlocked transactions wait longer before being aborted.

For most applications, the default 1 second is appropriate. Only increase it if you have false-positive deadlock detections (unlikely) or decrease it if you need faster deadlock resolution.

Deadlocks That Keep Coming Back

Check for application-level deadlocks. Your application code might have its own mutexes or locks that conflict with database locks. Review synchronization primitives in your code. If you also see relation does not exist errors mid-transaction, the deadlock may have rolled back a session in the middle of a multi-statement migration.

Check for connection pool contention. If your connection pool is exhausted, transactions wait for a connection while holding locks. This can create deadlock-like situations. Increase the pool size or reduce transaction duration.

For PostgreSQL connection issues, see Fix: PostgreSQL connection refused. For role authentication issues, see Fix: PostgreSQL FATAL: role does not exist.

Check for GIN and hash index contention. Full-text (GIN) inserts do heavy per-row work, and hash indexes hold bucket locks long enough that the docs warn deadlock is possible; both can serialize concurrent writes on the same index pages. Building a replacement index with CREATE INDEX CONCURRENTLY avoids the heavy lock during the build itself.

Consider serializable isolation, with eyes open. The SERIALIZABLE isolation level detects serialization anomalies and aborts the offending transaction with a serialization_failure (SQLSTATE 40001) that your application must catch and retry, Postgres does not retry for you. It does not eliminate deadlocks (you can still hit 40P01), but it lets you express correctness as “retry on conflict” instead of hand-ordering every lock. Reuse the same retry wrapper from Fix 2, just match 40001 alongside 40P01:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Check pg_stat_activity for wait_event_type = 'Lock' outside the deadlock window. A deadlock is the visible failure, but chronic lock waits are the underlying signal. Query:

SELECT pid, wait_event_type, wait_event, state, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

If multiple sessions consistently appear here, you have lock contention even when no full deadlock cycle forms. Reduce transaction scope or add explicit ordering before chasing the deadlock log further.

Audit MERGE statement lock ordering (PostgreSQL 15+). MERGE is convenient but holds the same row-level locks as the underlying writes. If your deadlocks started after migrating from manual INSERT ... ON CONFLICT DO UPDATE to MERGE, check whether two MERGE statements running concurrently touch the source rows in different orders.

Test with lock_timeout instead of deadlock_timeout as the safety net. Set lock_timeout = '5s' at the session or role level. The session aborts with canceling statement due to lock timeout (SQLSTATE 55P03) rather than waiting for the deadlock detector. This converts hidden lock waits into loud failures so you can fix them before they become deadlocks. See Fix: PostgreSQL duplicate key violates unique constraint for a related issue with ON CONFLICT clauses that interact with row locks.