🚨 PostgreSQL Error Guide: 'deadlock detected' Transaction Lock Cycles

Overview

A deadlock happens when two or more transactions each hold a lock the other needs, forming a cycle that can never resolve on its own. PostgreSQL’s deadlock detector wakes up after deadlock_timeout (default 1s), notices the cycle, and aborts one transaction (the “victim”) so the others can proceed. The victim’s statement fails and its whole transaction is rolled back.

The application sees:

ERROR:  deadlock detected
DETAIL:  Process 18452 waits for ShareLock on transaction 99213; blocked by process 18460.
Process 18460 waits for ShareLock on transaction 99210; blocked by process 18452.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,14) in relation "accounts"

It occurs whenever concurrent transactions acquire locks on the same rows (or related rows via foreign keys) in opposite orders. Unlike a simple lock wait, a deadlock is a true cycle — PostgreSQL cannot wait it out, so it must kill someone. Deadlocks are usually intermittent and load-dependent, which makes them hard to reproduce on demand.

Symptoms

• A statement fails with ERROR: deadlock detected and the transaction rolls back.
• The error is sporadic and correlates with concurrency (more frequent under load).
• The server log records both processes, their PIDs, and the conflicting statements.
• pg_stat_database shows a rising deadlocks counter.

SELECT datname, deadlocks
FROM pg_stat_database
WHERE datname = current_database();

 datname  | deadlocks
----------+-----------
 appdb    |        37
(1 row)

SELECT pid, state, wait_event_type, wait_event,
       now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

  pid  | state  | wait_event_type | wait_event |    xact_age     |                    query
-------+--------+-----------------+------------+-----------------+----------------------------------------------
 18452 | active | Lock            | tuple      | 00:00:00.812    | UPDATE accounts SET balance = balance - 100
 18460 | active | Lock            | transactionid | 00:00:00.744 | UPDATE accounts SET balance = balance + 100
(2 rows)

Common Root Causes

1. Inconsistent lock ordering across transactions

The classic cause: transaction A updates row 1 then row 2, while transaction B updates row 2 then row 1. Each grabs its first row, then blocks on the other’s — a cycle.

-- Inspect what each blocked PID is waiting on
SELECT blocked.pid AS blocked_pid,
       blocking.pid AS blocking_pid,
       left(blocked.query, 50)  AS blocked_query,
       left(blocking.query, 50) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

 blocked_pid | blocking_pid |          blocked_query           |         blocking_query
-------------+--------------+----------------------------------+--------------------------------
       18452 |        18460 | UPDATE accounts SET balance ...  | UPDATE accounts SET balance ...
       18460 |        18452 | UPDATE accounts SET balance ...  | UPDATE accounts SET balance ...
(2 rows)

Each PID blocks the other — a mutual block is the signature of an ordering deadlock.

2. Long-running transactions holding row locks

A transaction that updates a row and then does slow work (network calls, big computations) keeps the row lock for its whole duration, widening the window in which other transactions collide with it.

SELECT pid, now() - xact_start AS xact_age, state, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

  pid  |   xact_age    | state               |                    query
-------+---------------+---------------------+----------------------------------------------
 17004 | 00:04:38.221  | idle in transaction | UPDATE orders SET status = 'processing' WH...
 18452 | 00:00:01.004  | active              | UPDATE accounts SET balance = balance - 100
(2 rows)

PID 17004 has been idle in transaction for over four minutes, still holding write locks acquired by its UPDATE.

3. Foreign key lock contention

Inserting or updating a child row takes a FOR KEY SHARE lock on the referenced parent row. Two transactions touching children of the same parent, plus an update of that parent, can deadlock even though they never touch the same child.

-- Find FK-related locks on the parent table
SELECT l.pid, l.locktype, l.mode, l.granted, c.relname
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
WHERE c.relname = 'customers'
ORDER BY l.granted;

  pid  | locktype |     mode      | granted | relname
-------+----------+---------------+---------+-----------
 18460 | tuple    | ForKeyShare   | f       | customers
 18452 | tuple    | ForKeyShare   | t       | customers
(2 rows)

A non-granted ForKeyShare on the parent points to FK-induced contention from child writes.

4. Explicit LOCK statements

Code that issues LOCK TABLE or SELECT ... FOR UPDATE over multiple tables in differing orders can deadlock just like row updates. Explicit table locks are coarse, so collisions are easy.

SELECT l.pid, c.relname, l.mode, l.granted
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
WHERE l.mode IN ('AccessExclusiveLock', 'ExclusiveLock', 'ShareLock')
ORDER BY l.granted, c.relname;

  pid  | relname  |        mode         | granted
-------+----------+---------------------+---------
 18460 | inventory| ExclusiveLock       | f
 18452 | inventory| ExclusiveLock       | t
 18452 | shipments| ExclusiveLock       | f
 18460 | shipments| ExclusiveLock       | t
(4 rows)

PID 18452 holds inventory and wants shipments; PID 18460 holds the reverse — an explicit-lock cycle.

5. UPDATE / SELECT FOR UPDATE ordering

Batch jobs that update many rows in arbitrary (or index-scan) order will lock rows in that order. Two such jobs scanning the same table with different plans can take rows in opposite sequences and deadlock.

-- A FOR UPDATE without ORDER BY locks rows in scan order
EXPLAIN (COSTS off)
SELECT id FROM jobs WHERE status = 'queued' FOR UPDATE;

                  QUERY PLAN
-----------------------------------------------
 LockRows
   ->  Seq Scan on jobs
         Filter: (status = 'queued'::text)
(3 rows)

A Seq Scan under LockRows means rows are locked in physical order — non-deterministic across sessions with different visibility.

6. Application code with no retry logic

A deadlock is a normal, transient condition; PostgreSQL expects the loser to retry. Code that treats 40P01 as a fatal error surfaces deadlocks to users instead of transparently re-running the transaction.

-- The SQLSTATE applications must catch and retry
SELECT '40P01'::text AS deadlock_sqlstate;

 deadlock_sqlstate
-------------------
 40P01
(1 row)

If your driver logs raw 40P01 errors with no retry, every detected deadlock becomes a user-facing failure.

Diagnostic Workflow

Step 1: Enable detailed lock-wait logging

ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s';
SELECT pg_reload_conf();

With log_lock_waits on, any wait longer than deadlock_timeout is logged with the blocking PID, which captures near-deadlocks too.

Step 2: Read the deadlock report in the server log

sudo journalctl -u postgresql --no-pager | grep -A6 "deadlock detected" | tail -30
# or the file log
sudo tail -200 /var/log/postgresql/postgresql-16-main.log | grep -A6 "deadlock detected"

The DETAIL lines name both processes and the relation/tuple, and (with log_statement or the deadlock context) the conflicting statements — the raw material for fixing lock order.

Step 3: Inspect live blocking chains

SELECT blocked.pid AS blocked_pid,
       pg_blocking_pids(blocked.pid) AS blocked_by,
       blocked.wait_event, left(blocked.query, 60) AS query
FROM pg_stat_activity blocked
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

If you catch a deadlock cycle live, pg_blocking_pids will show two PIDs blocking each other.

Step 4: Identify the colliding tables and lock modes

SELECT l.pid, c.relname, l.mode, l.granted, l.locktype
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE l.pid IN (SELECT pid FROM pg_stat_activity WHERE state = 'active')
ORDER BY l.pid, l.granted;

Comparing which PID holds vs. waits for each relation reveals the lock-acquisition order to standardize.

Step 5: Clear a stuck idle-in-transaction holder if needed

-- Find idle-in-transaction sessions older than 5 minutes
SELECT pid, now() - xact_start AS age, left(query, 50) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > interval '5 minutes';

-- Cancel (graceful) or terminate (forceful)
SELECT pg_cancel_backend(17004);
SELECT pg_terminate_backend(17004);

Killing a long-idle holder releases its row locks and breaks the contention window.

Example Root Cause Analysis

A payments service intermittently logs deadlock detected during peak hours. The transfers move money between two accounts in a single transaction.

The server log shows the cycle:

ERROR:  deadlock detected
DETAIL:  Process 18452 waits for ShareLock on transaction 99213; blocked by process 18460.
Process 18460 waits for ShareLock on transaction 99210; blocked by process 18452.
CONTEXT:  while updating tuple (0,14) in relation "accounts"

The transfer code updates the sender first, then the recipient:

-- transfer(A -> B)
UPDATE accounts SET balance = balance - 100 WHERE id = $sender;
UPDATE accounts SET balance = balance + 100 WHERE id = $recipient;

When user A pays B at the same instant B pays A, the two transactions lock A and B in opposite orders — a textbook ordering deadlock.

Fix: always lock the accounts in a deterministic order (e.g., by ascending id) regardless of who is sender or recipient:

-- Lock both rows up front in a fixed order, then update
SELECT id FROM accounts
WHERE id IN ($sender, $recipient)
ORDER BY id
FOR UPDATE;
-- now perform both UPDATEs

With both transactions acquiring locks in id order, no cycle can form. Adding a retry-on-40P01 wrapper in the application handles any residual deadlocks from other code paths. The deadlock counter stops climbing.

Prevention Best Practices

• Acquire locks in a consistent, deterministic order everywhere (e.g., sort IDs before locking), so two transactions can never form a cycle.
• Keep transactions short: never hold an open transaction across user input, network calls, or sleeps. Eliminate idle in transaction holders.
• Set log_lock_waits = on and a sane idle_in_transaction_session_timeout so contention is visible and runaway holders are reaped automatically.
• Wrap write transactions in retry logic that catches SQLSTATE 40P01 and re-runs the transaction with backoff — deadlocks are expected, not exceptional.
• Add ORDER BY to SELECT ... FOR UPDATE and batch UPDATEs so rows lock in a predictable sequence across sessions.
• For ad-hoc triage, the free incident assistant can turn a deadlock log block into the likely lock-ordering culprit and a suggested fix.

Quick Command Reference

-- Deadlock counter for this database
SELECT datname, deadlocks FROM pg_stat_database WHERE datname = current_database();

-- Who is blocking whom (live)
SELECT blocked.pid AS blocked_pid,
       pg_blocking_pids(blocked.pid) AS blocked_by,
       left(blocked.query, 60) AS query
FROM pg_stat_activity blocked
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- Locks held/waited per relation
SELECT l.pid, c.relname, l.mode, l.granted
FROM pg_locks l LEFT JOIN pg_class c ON c.oid = l.relation
ORDER BY l.granted, c.relname;

-- Long idle-in-transaction sessions
SELECT pid, now() - xact_start AS age, left(query, 50) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

-- Enable lock-wait logging
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s';
SELECT pg_reload_conf();

-- Cancel or terminate a stuck holder
SELECT pg_cancel_backend(<PID>);
SELECT pg_terminate_backend(<PID>);

Conclusion

A deadlock detected error means two transactions formed a lock cycle and PostgreSQL aborted one to break it. The usual root causes:

1. Inconsistent lock-acquisition order across transactions.
2. Long-running or idle in transaction sessions holding row locks too long.
3. Foreign key contention via FOR KEY SHARE locks on shared parent rows.
4. Explicit LOCK TABLE / SELECT FOR UPDATE over multiple tables in differing orders.
5. Unordered batch UPDATE / FOR UPDATE locking rows in non-deterministic scan order.
6. Application code with no retry on SQLSTATE 40P01.

Read the log’s deadlock report to learn which two statements collided, standardize their lock order, and wrap writes in a retry loop. More PostgreSQL guides are in the Postgres category.