Diagnosing and Resolving Deadlocks in PostgreSQL: Inspecting Lock Trees and Configuring Proper Timeouts - ITFROMZERO - Share tobe shared! An in-depth look at the root causes of PostgreSQL deadlocks, step-by-step reproduction, Lock Tree query techniques to locate root blockers, and timeout tuning to safeguard your system.

Table of Contents

1. Background & Why Deadlocks Must Be Handled

Back then, our team was migrating a 100GB database from MySQL to PostgreSQL. It took three days of planning and a full day for cutover. Everything went smoothly until 8:00 PM—right during a flash sale when thousands of users topped up wallets and checked out at the same time. The backend’s 200-connection pool was completely drained in 30 seconds. APIs timed out en masse, while PostgreSQL logs lit up red with the classic error: ERROR: deadlock detected.

The root cause lies in PostgreSQL’s locking mechanism designed to guarantee ACID integrity. A deadlock occurs when Transaction A holds a lock on Record 1 and attempts to acquire a lock on Record 2. Concurrently, Transaction B holds Record 2 and requests a lock on Record 1. Neither transaction yields. Both end up permanently stuck in a mutual waiting loop.

PostgreSQL has a background process that detects this circular dependency and automatically aborts one of the transactions to unblock the system. However, you should never treat this fail-safe mechanism as a long-term solution. When deadlocks strike in rapid succession, the database constantly scans the lock graph, spiking CPU usage to 90–100%, throwing payment failures at users, and toppling application connection pools like dominoes.

2. Reproducing a Deadlock Locally

To cure the ailment, you first need to understand how it manifests. The most intuitive way is to open two terminal windows and orchestrate a lock collision yourself.

First, create an accounts table and seed two mock records for Alice and Bob:

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    owner_name VARCHAR(50) NOT NULL,
    balance NUMERIC(12, 2) NOT NULL DEFAULT 0
);

INSERT INTO accounts (owner_name, balance) VALUES 
('Alice', 1000.00),
('Bob', 2000.00);

Now, open two terminal tabs (Session 1 and Session 2) connected to Postgres, and execute commands following this timeline:

Step 1: In Session 1, start a transaction and deduct $100 from Alice:

-- Session 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

Step 2: Switch to Session 2, start another transaction, and deduct $200 from Bob:

-- Session 2
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 2;

Step 3: Return to Session 1 and try adding $100 to Bob’s account:

-- Session 1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

Terminal 1 freezes here. The query hangs because row id = 2 is held by Session 2 with a RowExclusiveLock.

Step 4: In Session 2, run the statement to transfer funds back to Alice:

-- Session 2
UPDATE accounts SET balance = balance + 200 WHERE id = 1;

Boom! Exactly 1 second later, the lock cycle completes. PostgreSQL detects the deadlock and terminates Session 2 on the spot:

ERROR:  deadlock detected
DETAIL:  Process 14210 waits for ShareLock on transaction 85412; blocked by process 14205.
Process 14205 waits for ShareLock on transaction 85413; blocked by process 14210.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,1) in relation "accounts"

3. The Lifesaving Config Trio: deadlock_timeout, lock_timeout, and Logging

PostgreSQL does not check for deadlocks immediately when a query gets blocked. The reason is simple: lock graph traversal algorithms are CPU-intensive. Postgres waits for a specified interval before kicking off the detection routine.

3.1. deadlock_timeout: Shortening Detection Latency

deadlock_timeout determines how long a process must wait for a lock before Postgres spawns a worker to scan for deadlocks. The engine default is 1s.

For high-throughput transactional systems (OLTP with thousands of TPS), a 1-second delay is an eternity. Hundreds of incoming requests will pile up behind it. You should lower this value to 200ms - 300ms:

-- Apply immediately to the cluster without restarting the server
ALTER SYSTEM SET deadlock_timeout = '300ms';
SELECT pg_reload_conf();

3.2. log_lock_waits: Enabling Early Warning Radar

By default, PostgreSQL only logs an entry when things have already spiraled into disaster (a deadlock error). If you want to catch slow queries holding locks and blocking others in the act, enable log_lock_waits right away. Whenever a process waits on a lock longer than deadlock_timeout, the server writes detailed information to the log file:

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

3.3. lock_timeout: Cutting the Fuse

This is your most crucial line of defense for the database. By default, lock_timeout = 0 (wait forever). In the event of lock contention, queries will hang indefinitely until connection pools run dry. Set a threshold so queries abort themselves if they fail to acquire a lock within a few seconds:

-- Set to 3s for the current session
SET lock_timeout = '3s';

-- Crucial when running DDL migrations in production
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE accounts ADD COLUMN status VARCHAR(20) DEFAULT 'active';
COMMIT;

If the accounts table has ongoing read/write transactions, the ALTER TABLE command above will abort after 2 seconds instead of locking the entire table.

4. Monitoring & Root-Cause Mitigation at the Application Layer

4.1. Catching Root Blockers Red-Handed with Lock Tree Queries

When database metrics flash red and latency spikes, you need to know immediately: who is the root blocker holding the lock, and who are the victims? The query below pinpoints the exact wait relationships between PIDs:

SELECT
    blocked_locks.pid         AS blocked_pid,
    blocked_activity.usename  AS blocked_user,
    blocking_locks.pid        AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query    AS blocked_statement,
    blocking_activity.query   AS blocking_statement,
    now() - blocked_activity.query_start AS waiting_duration
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

If you spot a PID holding a lock for too long and freezing the system, you have two tiers of emergency intervention:

-- Level 1: Gracefully cancel the running query for that PID
SELECT pg_cancel_backend(<blocking_pid>);

-- Level 2: If the command above fails to respond, forcefully terminate the connection
SELECT pg_terminate_backend(<blocking_pid>);

4.2. Permanent Fixes in Backend Code

Database tuning only mitigates damage. To eliminate deadlocks for good, you must address them in your business logic:

Always order resource updates consistently: This is a vital rule. Whenever Transaction A and B need to operate on multiple accounts, they must always lock resources in the exact same sequence (e.g., sorting IDs in ascending order). Note: UPDATE ... ORDER BY syntax is invalid in PostgreSQL. You must sort the array of IDs directly in your backend code before execution, or lock rows upfront: SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE.
Never place external I/O inside transactions: Never call third-party HTTP APIs, send emails, or generate PDF files between BEGIN ... COMMIT blocks. Prepare all payload data in memory first. Open the transaction, perform the database writes swiftly, and commit immediately (aiming for < 50ms).
Leverage SKIP LOCKED for queue-based tasks: For background workers polling jobs to process, avoid standard SELECT FOR UPDATE. Switch to SELECT ... FOR UPDATE SKIP LOCKED instead. Workers will automatically bypass rows currently locked by other processes, completely eliminating lock contention risks.
Implement retries with exponential backoff: Deadlocks are transient errors. Your application layer should include middleware that catches Postgres error code 40P01. When caught, automatically retry after 50ms, 100ms, 200ms with a small amount of random jitter. The user experience remains completely seamless.