Fix PostgreSQL Deadlock Detected: Root Causes and Minimal Checks

Quick Answer

• Conclusion: PostgreSQL deadlocks occur when two or more transactions hold locks that the other needs, creating a circular wait. The database automatically detects and resolves deadlocks by aborting one transaction, but frequent deadlocks indicate application design issues.
• First checks: Query pg_locks and pg_stat_activity to identify blocked and blocking sessions. Look for transactions holding conflicting locks (e.g., multiple updates on overlapping row sets in different orders).
• Minimal fix: Restructure application transactions to acquire locks in a consistent global order. Reduce transaction duration and avoid user interaction within transactions. Add retry logic for deadlock victims.
• Monitoring setup: Create a dedicated read-only monitoring user, grant SELECT ON pg_locks and pg_stat_activity, and configure a detection interval of 30-60 seconds. Use log action initially before enabling automatic termination.
• Version boundary: This guidance applies to PostgreSQL 9.6+ (all supported versions). The pg_locks view and deadlock detection mechanism are stable across versions.

What Problem It Solves

PostgreSQL deadlocks are a concurrency problem where two or more transactions are stuck waiting for each other to release locks. The database engine automatically detects deadlocks and resolves them by aborting one transaction (the "victim"), which then rolls back. While this prevents indefinite hangs, frequent deadlocks degrade performance, cause application errors, and indicate poor transaction design.

This guide covers:

• How to detect and diagnose deadlocks using PostgreSQL system views
• How to configure automated deadlock monitoring and optional remediation
• How to prevent deadlocks through application-level changes

Root Cause Analysis

PostgreSQL deadlocks typically arise from these patterns:

1. Inconsistent lock ordering: Transaction A locks row 1 then row 2, while Transaction B locks row 2 then row 1. If both acquire their first lock simultaneously, each waits for the other's second lock.
2. Long-running transactions: Holding locks for extended periods increases the window for conflicts.
3. Missing indexes: Table-level locks (e.g., ACCESS EXCLUSIVE from DDL) can conflict with row-level locks from concurrent transactions.
4. Implicit locks from foreign keys: INSERT or UPDATE on a referenced table may acquire locks on the referencing table's rows.

To diagnose a deadlock, check the PostgreSQL log for messages like:

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 67890.
Process 67890 waits for ShareLock on transaction 12345; blocked by process 12345.

Minimal Working Configuration

1. Create a Dedicated Monitoring User

SQL
CREATE USER monitor_user WITH PASSWORD 'your_secure_password';
GRANT CONNECT ON DATABASE your_database TO monitor_user;
GRANT SELECT ON pg_stat_activity TO monitor_user;
GRANT SELECT ON pg_locks TO monitor_user;

Do not grant superuser privileges. For automatic termination, you will need additional permissions (see Common Errors section).

2. Configure Automated Deadlock Detection

The following Python-based detector runs as a standalone script. It queries pg_locks at a configurable interval and logs or terminates deadlocked sessions.

PYTHON
# postgres_deadlock_detector.py
import psycopg2
import time
import logging
import sys

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

def detect_deadlocks(conn, action='log'):
    with conn.cursor() as cur:
        # Find blocked processes waiting on locks
        cur.execute("""
            SELECT
                blocked.pid AS blocked_pid,
                blocked.query AS blocked_query,
                blocking.pid AS blocking_pid,
                blocking.query AS blocking_query
            FROM pg_stat_activity blocked
            JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
            JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
                AND blocked_locks.database = blocking_locks.database
                AND blocked_locks.relation = blocking_locks.relation
                AND blocked_locks.page = blocking_locks.page
                AND blocked_locks.tuple = blocking_locks.tuple
            JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
            WHERE NOT blocked_locks.granted
                AND blocking_locks.granted
                AND blocked.pid <> blocking.pid
                AND blocked.state = 'active'
                AND blocking.state = 'active';
        """)
        deadlocks = cur.fetchall()
        
        if deadlocks:
            for blocked_pid, blocked_query, blocking_pid, blocking_query in deadlocks:
                logging.warning(f"Deadlock detected: blocked PID {blocked_pid} waiting on PID {blocking_pid}")
                logging.warning(f"Blocked query: {blocked_query}")
                logging.warning(f"Blocking query: {blocking_query}")
                
                if action == 'terminate':
                    try:
                        cur.execute(f"SELECT pg_terminate_backend({blocked_pid})")
                        logging.info(f"Terminated blocked process {blocked_pid}")
                    except Exception as e:
                        logging.error(f"Failed to terminate PID {blocked_pid}: {e}")
        else:
            logging.debug("No deadlocks detected")

def main():
    config = {
        'host': 'localhost',
        'port': 5432,
        'database': 'mydb',
        'user': 'monitor_user',
        'password': 'your_password',
        'interval': 30,  # seconds
        'action': 'log'  # 'log' or 'terminate'
    }
    
    conn = psycopg2.connect(**config)
    conn.autocommit = True
    
    try:
        while True:
            detect_deadlocks(conn, config['action'])
            time.sleep(config['interval'])
    except KeyboardInterrupt:
        logging.info("Shutting down")
    finally:
        conn.close()

if __name__ == '__main__':
    main()

3. Run the Detector

BASH
python postgres_deadlock_detector.py

Or use the MCP server configuration (for integration with AI assistants):

JSON
{
  "mcpServers": {
    "postgres-deadlock-detector": {
      "command": "python",
      "args": [
        "-m",
        "postgres_deadlock_detector",
        "--host",
        "localhost",
        "--port",
        "5432",
        "--database",
        "mydb",
        "--user",
        "monitor_user",
        "--password",
        "your_password",
        "--interval",
        "30",
        "--action",
        "log"
      ]
    }
  }
}

Common Errors and Fixes

Permission Fix for Automatic Termination

SQL
-- Grant the ability to terminate backends (PostgreSQL 14+)
GRANT pg_signal_backend TO monitor_user;

-- For older versions, you may need superuser
ALTER USER monitor_user WITH SUPERUSER;  -- Use with caution

Production Notes and Security Checks

Security Requirements

1. Use a read-only monitoring user with minimal privileges. Never use the application's database user for monitoring.
2. Enable SSL for the monitoring connection to prevent password interception:

   sslmode=require
   

3. Restrict network access to the monitoring host in pg_hba.conf:

   hostssl your_database monitor_user 192.168.1.0/24 md5
   

4. Avoid automatic termination in production until you understand the deadlock pattern. Start with action='log' and analyze the logs for at least a week.

Performance Considerations

• Set the detection interval to 30-60 seconds minimum. Shorter intervals increase CPU usage on both the monitor and the database.
• The pg_locks query shown above is efficient but still adds overhead. In high-concurrency systems (1000+ active connections), consider sampling or using PostgreSQL's built-in deadlock_timeout parameter instead.
• Do not run multiple monitoring instances against the same database concurrently—they may conflict or produce duplicate results.

Limitations

• This approach does not detect distributed deadlocks across multiple database instances.
• Automatic termination may cause transaction rollbacks and data inconsistency if the application lacks retry logic.
• The detector only sees current lock waits, not historical deadlock events. For historical analysis, enable log_lock_waits = on in postgresql.conf.

FAQ

Q: How can I safely detect deadlocks without impacting production performance?

A: Use a dedicated read-only monitoring user with a detection interval of 30-60 seconds. Start with action='log' to observe patterns before enabling any automatic remediation. Avoid querying pg_locks more frequently than every 30 seconds. Use Netdata or similar monitoring tools for visualization rather than running ad-hoc queries on production databases.

Q: What is the best practice for automatic deadlock remediation?

A: First, log all deadlock events for at least one week to identify patterns. Then, enable automatic termination only for non-critical transactions. Set up a whitelist of PIDs that should never be terminated (e.g., long-running reporting queries). Implement retry logic in your application so that deadlock victims can re-execute their transactions. For high-frequency deadlocks, focus on fixing the application's lock ordering rather than relying on automatic termination.

Q: How does this approach compare to manually querying pg_stat_activity?

A: Manual queries only provide a point-in-time snapshot and require constant human attention. An automated detector provides continuous monitoring, immediate alerting, and optional automatic response. It also integrates with monitoring systems like Netdata for historical trend analysis and visualization, which helps identify recurring deadlock patterns that manual inspection would miss.