PostgreSQL Deep Dive: Deadlock Detection, log_lock_waits, and Why Your Transactions Keep Aborting

Your application logs show a sudden spike in SQLSTATE 40P01 errors. Transactions are aborting mid-flight, users see “something went wrong” messages, and retry logic kicks in — but some retries deadlock again. The database is fine. Connections are healthy. There’s no obvious bottleneck. Just a handful of transactions killing each other in a circular standoff that PostgreSQL resolves by shooting one of them.

Deadlocks are one of the most frustrating database problems because they’re intermittent, they’re hard to reproduce, and they only happen under concurrency — exactly the condition that’s hardest to test locally. Today we’ll look at how PostgreSQL detects them, why log_lock_waits is just as important as deadlock detection, and how to prevent both from disrupting your application.

How Deadlocks Happen

A deadlock occurs when two or more transactions hold locks that the other needs, forming a cycle. The classic pattern:

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE acctnum = 11111;
-- holds row lock on 11111

-- Transaction 2 (concurrent)
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE acctnum = 22222;
-- holds row lock on 22222
UPDATE accounts SET balance = balance - 100 WHERE acctnum = 11111;
-- blocks, waiting for T1's lock on 11111

-- Transaction 1 (continues)
UPDATE accounts SET balance = balance - 100 WHERE acctnum = 22222;
-- blocks, waiting for T2's lock on 22222
-- DEADLOCK: T1 waits for T2, T2 waits for T1

PostgreSQL detects the cycle and aborts one transaction:

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

The surviving transaction proceeds normally. The aborted transaction must retry from the beginning.

How PostgreSQL Detects Deadlocks: The Wait-For Graph

PostgreSQL does NOT continuously scan for deadlocks. That would be too expensive. Instead, it uses a deferred detection strategy controlled by deadlock_timeout:

When a backend needs a lock that’s already held, it adds itself to the lock’s wait queue.
The backend sleeps for deadlock_timeout (default 1s) before doing anything else.
After the timeout, it runs a deadlock check by constructing a wait-for graph: a directed graph where nodes are transactions and edges represent “transaction A is waiting for a lock held by transaction B.”
It then searches this graph for cycles using a depth-first traversal.
If a cycle is found, one transaction in the cycle is chosen as the “victim” and aborted with SQLSTATE 40P01.

The victim is chosen arbitrarily. PostgreSQL does not pick the “most guilty” transaction or the one that’s been running longest. It’s essentially random from the application’s perspective — you cannot predict which transaction will die. This means every transaction in a deadlock-prone workload must be prepared to retry.

Why not check immediately? Building and traversing the wait-for graph requires scanning the shared lock table, which is a shared data structure protected by LWLocks. On a busy system with thousands of active locks, this scan is expensive. The deadlock_timeout acts as a throttle — the assumption is that most lock waits resolve quickly because the holding transaction commits, so checking only after a delay avoids wasting CPU on transient waits.

This has an important implication: PostgreSQL won’t detect a deadlock until at least deadlock_timeout has elapsed. If you set deadlock_timeout = 1s (the default), a deadlock blocks for at least one second before anyone gets an error. If you set it to 10s to reduce spurious checks, your deadlocked transactions block for 10 seconds.

Deadlocks vs Long Lock Waits: Different Problems, Similar Symptoms

Deadlocks get all the attention, but in practice, long lock waits are far more common and often more damaging. Consider the difference:

Deadlock: Two transactions block each other in a cycle. PostgreSQL detects it and aborts one. You get an error you can retry. The total delay is deadlock_timeout plus retry time.

Long lock wait: Transaction A holds a row lock for 30 seconds (because it’s doing something slow inside the transaction). Transaction B waits. And waits. No error — it just blocks. From the application’s perspective, the query hung for 30 seconds. No retry logic triggers because there’s no error to catch. The connection pool might time out. The user sees a spinner.

Long lock waits are stealthier than deadlocks because they don’t produce error codes. They just make your application slow in ways that are hard to attribute to locking.

log_lock_waits: Your Window Into Lock Waits

log_lock_waits (default off) tells PostgreSQL to log a message whenever a session waits longer than deadlock_timeout to acquire a lock:

LOG:  process 12345 still waiting for ShareLock on transaction 67890 after 1000.123 ms
DETAIL:  Process holding the lock: 67890. Wait queue: 12345.
STATEMENT:  UPDATE accounts SET balance = balance - 100 WHERE acctnum = 11111

This is invaluable for diagnosing lock contention. When enabled, every lock wait longer than deadlock_timeout gets logged with:

The waiting PID
The lock type and object
The duration of the wait
The PID of the process holding the conflicting lock
The query that’s waiting

Important: log_lock_waits uses the same timer as deadlock detection. The log message fires at deadlock_timeout, and the deadlock check also fires at deadlock_timeout. They’re two sides of the same mechanism — one logs, the other checks for cycles.

Tuning tip: For diagnostic purposes, you can temporarily lower deadlock_timeout to get faster feedback. Set deadlock_timeout = '100ms' and log_lock_waits = on, and you’ll catch lock waits that last more than 100ms. On a production system with many short transactions, this will be noisy. On a system where you’re investigating specific slowness, it’s the fastest way to find out if locking is the cause.

Detecting Deadlocks Without log_lock_waits

Even without log_lock_waits, deadlocks are always logged when they’re detected:

ERROR:  deadlock detected
DETAIL:  Process 12345 waits for ShareLock on transaction 67890;
        Process 67890 waits for ShareLock on transaction 12345.
CONTEXT:  while updating tuple (0,2) in relation "accounts"

But this only tells you about the deadlock after it happened. To find the queries involved, you need to correlate the PIDs in the log with your application logs. Better to have log_lock_waits on so you can see the lock wait before the deadlock abort.

pg_locks: Finding Blockers in Real Time

pg_locks is your live view of the lock table. Here’s a query to find blocking relationships:

SELECT
  blocked.pid     AS blocked_pid,
  blocked.query   AS blocked_query,
  blocking.pid    AS blocking_pid,
  blocking.query  AS blocking_query,
  blocked.waitstart
FROM pg_locks bl
JOIN pg_stat_activity blocked ON bl.pid = blocked.pid
JOIN pg_locks kl ON bl.locktype = kl.locktype
  AND bl.database IS NOT DISTINCT FROM kl.database
  AND bl.relation IS NOT DISTINCT FROM kl.relation
  AND bl.page IS NOT DISTINCT FROM kl.page
  AND bl.tuple IS NOT DISTINCT FROM kl.tuple
  AND bl.pid != kl.pid
  AND bl.granted = false
  AND kl.granted = true
JOIN pg_stat_activity blocking ON kl.pid = blocking.pid
WHERE blocked.wait_event IS NOT NULL;

This finds every session currently waiting for a lock and the session holding the conflicting lock. Run this during a suspected deadlock event and you’ll see the exact blocking chain.

Limitation: pg_stat_activity.query shows the currently executing query, not necessarily the query that acquired the lock. If Transaction A ran UPDATE accounts SET ... five queries ago and is still holding the row lock, blocking.query will show whatever Transaction A is executing right now, not the original UPDATE. This is why log_lock_waits is more useful for post-mortem analysis — it captures the statement at the time of the wait.

The Four Patterns That Cause Deadlocks

Pattern 1: Inconsistent Lock Ordering

This is the textbook deadlock. Two transactions update the same set of rows but in different order:

-- T1: UPDATE row A, then row B
-- T2: UPDATE row B, then row A

Fix: Always access rows in a deterministic order. If you’re updating accounts 11111 and 22222, always update the smaller ID first:

-- Both transactions
UPDATE accounts SET balance = balance + 100 WHERE acctnum = 11111;
UPDATE accounts SET balance = balance - 100 WHERE acctnum = 22222;

Pattern 2: Foreign Key Locking

This one surprises people. When you INSERT a row with a foreign key, PostgreSQL acquires a FOR KEY SHARE lock on the referenced row. When you DELETE or UPDATE the referenced row, it acquires a FOR NO KEY UPDATE lock. These can deadlock with ordinary operations on the child table:

-- T1: inserts a child row referencing parent row 5
INSERT INTO order_items (order_id, product_id) VALUES (1, 5);
-- acquires FOR KEY SHARE on products row 5

-- T2: updates the parent row
UPDATE products SET price = 29.99 WHERE product_id = 5;
-- acquires FOR NO KEY UPDATE on products row 5 — blocks on T1

-- T1: now tries to update the parent row too
UPDATE products SET stock = stock - 1 WHERE product_id = 5;
-- DEADLOCK: T1 holds KEY SHARE, waits for NO KEY UPDATE
--          T2 holds NO KEY UPDATE, waits for KEY SHARE

Fix: Access parent and child rows in a consistent order. If you need to modify both, lock the parent first, then the child. Or batch parent updates to reduce the window where child inserts can conflict.

Pattern 3: Missing Indexes on Foreign Keys

When you delete a row from a parent table, PostgreSQL must scan the child table to verify no foreign key references exist. If there’s no index on the child’s FK column, this scan locks every row it checks — potentially thousands of rows. Any concurrent INSERT into the child table can then deadlock with the DELETE.

-- Check if FK columns are indexed
SELECT
  tc.constraint_name,
  tc.table_name AS child_table,
  kcu.column_name AS child_column,
  ccu.table_name AS parent_table
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND NOT EXISTS (
    SELECT 1 FROM pg_indexes pi
    WHERE pi.tablename = tc.table_name
      AND pi.indexdef LIKE '%' || kcu.column_name || '%'
  );

Fix: Index every foreign key column. This is one of the easiest wins in PostgreSQL performance.

Pattern 4: Application-Level Retry Conflicts

Retry logic can create deadlocks by replaying the same conflicting operations:

# Application retry logic
for attempt in range(3):
    try:
        transfer(account_a, account_b, amount)
        break
    except DeadlockDetected:
        sleep(0.1 * attempt)

If two transfers involving the same accounts deadlock, both retry, and both retry at roughly the same time with exponential backoff, they can deadlock again. The probability decreases with each retry, but under high concurrency it’s not zero.

Fix: Combine retry with jittered backoff and ensure lock ordering within the retry path.

Practical SQL: Diagnosing Deadlocks

1. Check pg_stat_database for deadlock counts

SELECT
  datname,
  deadlocks,
  xact_commit,
  round(
    deadlocks::numeric / NULLIF(xact_commit, 0) * 100000, 2
  ) AS deadlocks_per_100k_commits
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY deadlocks DESC;

A healthy system has zero deadlocks. Anything above zero means your application has a concurrency bug. The rate relative to transaction volume tells you how severe it is — one deadlock per 100,000 commits might be tolerable with retry logic, but one per 1,000 commits is a problem that needs fixing.

2. Find queries that were involved in deadlocks

PostgreSQL logs the queries involved in a deadlock, but they’re in the server log, not in any system view. To capture them systematically, enable log_lock_waits and correlate with your application logs.

Alternatively, on PG14+, pg_stat_activity shows the current query for every backend. If you poll it frequently during a suspected deadlock window, you can catch the blocking and blocked queries.

3. Detect missing FK indexes

SELECT
  conrelid::regclass AS child_table,
  a.attname AS child_column,
  confrelid::regclass AS parent_table,
  af.attname AS parent_column,
  conname AS constraint_name
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
JOIN pg_attribute af ON af.attrelid = c.confrelid AND af.attnum = ANY(c.confkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND a.attnum = ANY(i.indkey)
  );

The Gotcha: deadlock_timeout Is Not “How Long Before You Get a Deadlock Error”

A common misconception is that lowering deadlock_timeout makes deadlocks resolve faster. It doesn’t — it makes deadlock detection faster. The actual deadlock exists the moment the cycle forms. PostgreSQL just doesn’t notice until the timeout fires.

More importantly, deadlock_timeout has a dual purpose: it controls both deadlock detection and log_lock_waits logging. If you lower it to detect deadlocks faster, you also generate log messages for every lock wait longer than that threshold. On a busy system with lots of legitimate (but slow) lock waits, you’ll flood your logs.

Recommendation: Keep deadlock_timeout at or above your typical transaction duration. If most transactions complete in under 500ms, set deadlock_timeout = '1s'. If you have longer transactions, raise it to match. Then set log_lock_waits = on to catch the waits that exceed this threshold.

Prevention Strategy

Code-level:

Always access rows in a consistent order (smallest ID first, alphabetical, whatever — just be consistent)
Index every foreign key column
Keep transactions as short as possible — commit early, open late
Never hold locks while waiting for external calls (API requests, user input)
Implement retry logic with exponential backoff for SQLSTATE 40P01

Configuration-level:

Set log_lock_waits = on in production
Set deadlock_timeout to match your typical transaction time
Set deadlock_timeout = '100ms' temporarily when debugging specific lock contention issues

Monitoring-level:

Track pg_stat_database.deadlocks and alert on any increase above zero
Monitor lock wait counts from log_lock_waits output
Set up alerts for SQLSTATE 40P01 in application error tracking

Key Takeaways

PostgreSQL detects deadlocks by building a wait-for graph and checking for cycles, but only after deadlock_timeout (default 1s) has elapsed.
The deadlock victim is chosen arbitrarily. Every transaction in a deadlock-prone workload must handle SQLSTATE 40P01 with retry logic.
Long lock waits are more common than deadlocks and more damaging because they don’t produce errors — they just make your application hang.
log_lock_waits = on is essential for production. It logs every lock wait exceeding deadlock_timeout with the full query context.
The four common deadlock patterns are: inconsistent lock ordering, foreign key locking, missing FK indexes, and retry-induced conflicts.
deadlock_timeout controls both deadlock detection and lock wait logging. Lowering it for faster detection also increases log noise.
Index every foreign key. This single action prevents an entire class of deadlocks caused by full-table FK validation scans.

What’s Next

Tomorrow we’ll dive into index bloat — why your indexes keep growing even when your table doesn’t, how to measure the actual waste, and when to reach for REINDEX CONCURRENTLY versus pg_repack.

Previous: Temporary Files and log_temp_files — When Your Queries Secretly Write Gigabytes to Disk