Troubleshooting PostgreSQL Deadlocks: Transaction Cancel Lock Conflicts

Resolve PostgreSQL deadlocks related to transaction cancel lock conflicts. Diagnose root causes from logs, optimize queries, and refine application logic.

When your PostgreSQL database logs start reporting “deadlock detected” messages, particularly those mentioning a “transaction cancel lock conflict”, it’s a clear signal of serious contention within your application’s interaction with the database. These deadlocks can manifest as slow query responses, failed transactions, and an overall degradation of application performance and reliability. Understanding and resolving these requires a deep dive into both database behavior and application transaction logic.

This guide provides a highly technical, step-by-step approach to diagnose, understand, and resolve PostgreSQL deadlocks specifically related to transaction cancel lock conflicts, drawing on common production hosting practices.

Symptom & Error Signature

The primary symptom you’ll encounter is an application error indicating a failed transaction, a query timeout, or a database connection error. Concurrently, your PostgreSQL logs will be filled with ERROR: deadlock detected entries. The “transaction cancel lock conflict” specifically points to a scenario where one transaction is waiting for a lock held by another, which itself is in the process of being cancelled or terminated, leading to a circular dependency.

Typical log entries might look like this:

2026-06-27 10:00:01 UTC [23456]: [APPLICATION] ERROR:  deadlock detected
2026-06-27 10:00:01 UTC [23456]: [APPLICATION] DETAIL:  Process 23456 waits for AccessExclusiveLock on relation 12345 of database 12346; blocked by process 23457.
    Process 23457 waits for transactionid 7890 (transaction cancel lock conflict); blocked by process 23456.
    Process 23456: UPDATE accounts SET balance = balance - 100 WHERE id = 123;
    Process 23457: SELECT * FROM audit_logs WHERE account_id = 123 FOR UPDATE;
2026-06-27 10:00:01 UTC [23456]: [APPLICATION] HINT:  See server log for full deadlock information.
2026-06-27 10:00:01 UTC [23456]: [APPLICATION] STATEMENT:  UPDATE accounts SET balance = balance - 100 WHERE id = 123;

Another variation could be:

2026-06-27 10:05:05 UTC [98765]: [APPLICATION] ERROR:  deadlock detected
2026-06-27 10:05:05 UTC [98765]: [APPLICATION] DETAIL:  Process 98765 waits for ExclusiveLock on tuple (0,27) of relation 67890 of database 67891; blocked by process 98766.
    Process 98766 waits for transactionid 1234 (transaction cancel lock conflict); blocked by process 98765.
    Process 98765: INSERT INTO orders (user_id, product_id, quantity) VALUES (456, 789, 1);
    Process 98766: DELETE FROM carts WHERE user_id = 456;
2026-06-27 10:05:05 UTC [98765]: [APPLICATION] HINT:  See server log for full deadlock information.

Root Cause Analysis

A deadlock occurs when two or more transactions are waiting for each other to release locks, forming a cyclical dependency where none can proceed. PostgreSQL’s deadlock detector automatically identifies these situations and arbitrarily aborts one of the transactions (the “deadlock victim”) to allow the others to proceed.

The specific “transaction cancel lock conflict” indicates a more nuanced scenario:

  1. Transaction A holds a lock (e.g., on a row or table).
  2. Transaction B attempts to acquire a lock that Transaction A holds, so Transaction B waits.
  3. Concurrently, some external process (e.g., pg_cancel_backend(), pg_terminate_backend(), or a client-side timeout) attempts to cancel or terminate Transaction A.
  4. The cancellation/termination itself requires a lock on Transaction A’s internal state (e.g., a “transaction cancel lock”).
  5. However, Transaction B is waiting for Transaction A’s initial lock, and now Transaction A is blocked internally trying to acquire the cancel lock because Transaction B might be holding it indirectly, or vice-versa. This creates a circular wait involving the cancellation mechanism itself.

Root causes often include:

  • Inconsistent Lock Ordering: Transactions acquire locks on resources (rows, tables) in different orders. For example, Transaction 1 locks resource A then resource B, while Transaction 2 locks resource B then resource A.
  • Long-Running Transactions: Transactions that hold locks for extended periods increase the window for conflicts.
  • Inefficient Queries: Queries that scan large portions of tables or lack appropriate indexes can acquire more locks than necessary, or hold them for longer.
  • Aggressive Connection Management: Applications or external monitors might be too eager to cancel or terminate “idle” or long-running transactions, inadvertently triggering or exacerbating deadlocks if those transactions hold critical locks.
  • Application Logic Flaws: Business logic that performs multiple database operations within a single transaction without proper atomicity considerations.
  • High Concurrency: A large number of concurrent transactions operating on the same data.
  • Misunderstood Isolation Levels: While READ COMMITTED (the default) usually prevents predicate locks from leading to deadlocks, higher isolation levels like SERIALIZABLE or REPEATABLE READ are more prone to deadlocks if not handled carefully.

Step-by-Step Resolution

Resolving “transaction cancel lock conflict” deadlocks typically involves a combination of database optimization and application code refactoring.

1. Analyze PostgreSQL Logs and Current Activity

The first step is to gather as much information as possible from the PostgreSQL logs and the live database state.

  • Access PostgreSQL Logs: On Systemd-based systems (like Ubuntu), logs are often managed by journald.

    # View recent PostgreSQL logs for a specific version (e.g., 15)
sudo journalctl -u [email protected] --since "1 hour ago" | grep "deadlock detected"

# Follow live PostgreSQL logs
sudo journalctl -u [email protected] -f

    Alternatively, if log_destination is set to stderr and logging_collector to on, logs will be in /var/lib/postgresql/<version>/main/log/.

  • Identify Conflicting Processes and Queries: The DETAIL: section of the deadlock error is crucial. Note the Process ID (PID), relation ID, database ID, and the exact STATEMENT for both processes. This information directly points to the queries and tables involved.

  • Inspect pg_stat_activity: While the deadlock is happening, or to investigate its aftermath, pg_stat_activity can show what queries were running, their states, and the locks they held.

    -- Connect to your database as a superuser (e.g., postgres)
sudo -u postgres psql your_database_name

SELECT
    pid,
    application_name,
    datname,
    usename,
    client_addr,
    state,
    wait_event_type,
    wait_event,
    query_start,
    query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start DESC;

  • Examine pg_locks: pg_locks provides real-time information about locks held by transactions. Correlate PIDs from the logs with pg_locks to see what types of locks were involved.

    SELECT
    a.pid,
    a.usename,
    a.datname,
    a.client_addr,
    a.state,
    a.query,
    pg_blocking_pids(a.pid) as blocked_by,
    locktype,
    mode,
    granted,
    relname
FROM pg_stat_activity a
JOIN pg_locks pl ON a.pid = pl.pid
LEFT JOIN pg_class pc ON pl.relation = pc.oid
WHERE a.state = 'active'
AND pg_blocking_pids(a.pid) IS NOT NULL;

    This query helps identify sessions that are blocked and by whom, and what locks are involved.

2. Optimize Database Schema and Indexes

Inefficient database design or missing indexes can lead to queries holding locks for too long or locking more rows than necessary.

  • Identify Slow or Inefficient Queries: Use the STATEMENT from the deadlock log and analyze its execution plan.

    -- Example for a problematic UPDATE statement
EXPLAIN ANALYZE UPDATE accounts SET balance = balance - 100 WHERE id = 123;

-- Example for a problematic SELECT ... FOR UPDATE
EXPLAIN ANALYZE SELECT * FROM audit_logs WHERE account_id = 123 FOR UPDATE;

    [!NOTE] EXPLAIN ANALYZE actually executes the query, so use it carefully on production if it’s a modifying query. For SELECT statements, it’s generally safe.

  • Add or Optimize Indexes: Ensure appropriate indexes are in place for columns used in WHERE, JOIN, ORDER BY, GROUP BY clauses, and especially for columns involved in UPDATE or DELETE conditions. Missing indexes can cause full table scans, leading to AccessExclusiveLock conflicts or long-held RowExclusiveLocks.

    -- Example: Ensure an index on account_id if frequently used in WHERE clauses
CREATE INDEX IF NOT EXISTS idx_audit_logs_account_id ON audit_logs (account_id);

-- Example: Ensure an index on the 'id' column of the accounts table
CREATE UNIQUE INDEX IF NOT EXISTS idx_accounts_id ON accounts (id);

  • Review Table Design: Consider if table structures or relationships contribute to contention. Sometimes, denormalization or partitioning might alleviate hot spots, but these come with their own trade-offs.

3. Refactor Application Transaction Logic

This is often the most critical area for resolving deadlocks. The “transaction cancel lock conflict” specifically hints at issues exacerbated by external cancellation, which means transactions are holding locks long enough to become targets for cancellation while also being part of a deadlock cycle.

  • Consistent Lock Ordering:

    [!IMPORTANT] This is paramount for preventing deadlocks. All transactions accessing the same set of resources (tables, rows) must acquire locks in the same, predefined order. For example, if transactions often update table_A and table_B, always lock rows in table_A first, then table_B, or vice-versa, but never mix the order. This applies to SELECT ... FOR UPDATE as well.

  • Reduce Transaction Duration: Keep transactions as short as possible. Acquire locks just before they are needed and release them as soon as possible by committing or rolling back the transaction. Avoid user interaction or network calls within an active transaction.

  • Minimize Lock Contention:

    • Use SELECT ... FOR UPDATE carefully: Only lock the rows you truly need to modify.

    • Consider SKIP LOCKED: If your application can tolerate processing a subset of rows (e.g., a worker queue), SELECT ... FOR UPDATE SKIP LOCKED can be very effective in preventing waiting and thus deadlocks, by simply skipping rows currently locked by other transactions.

      -- Example: Process items from a queue without waiting for locked items
SELECT * FROM queue_items WHERE processed = false
ORDER BY created_at
FOR UPDATE SKIP LOCKED LIMIT 10;

    • Consider NOWAIT: If a transaction failing immediately is better than waiting, use FOR UPDATE NOWAIT.

  • Batch Operations: For bulk updates or inserts, break them into smaller, manageable transactions rather than one giant transaction. This reduces the total time locks are held.

  • Implement Robust Retry Logic: Client-side retry logic with exponential backoff is crucial. When PostgreSQL chooses a deadlock victim, the application should be prepared to catch the error, roll back, and retry the entire transaction. Exponential backoff helps avoid immediately re-entering the deadlock scenario.

  • Review Application Cancellation/Timeout Strategies: If your application or monitoring tools are aggressively cancelling long-running queries (e.g., using pg_cancel_backend()), ensure this is done judiciously. Understand that a query holding locks might be critical for other transactions. Shortening query timeouts might push the problem from “slow queries” to “deadlocks if cancellation fails quickly.”

4. Adjust PostgreSQL Configuration Parameters

While not a direct fix for application logic, some PostgreSQL parameters can help diagnose or manage deadlock behavior.

  • deadlock_timeout: This parameter defines how long a transaction waits for a lock before checking for a deadlock. The default is usually 1 second (1000ms).

    [!WARNING] Do NOT blindly increase deadlock_timeout to solve deadlocks. This will only make your application wait longer before detecting and resolving the deadlock, leading to worse user experience and timeouts. It should be kept relatively low. Increasing it might only mask the underlying contention.

    If you suspect specific long-running non-deadlocking queries are being cancelled, and you want to give them more time, you might slightly increase deadlock_timeout, but only after thorough analysis and understanding the consequences.

  • log_lock_waits: Set this to on to log information about any lock waits that last longer than deadlock_timeout. This is extremely useful for identifying which queries are frequently waiting for locks.

    # In postgresql.conf
log_lock_waits = on

  • log_min_duration_statement: Setting this to a value (e.g., 500ms) will log all statements that run for longer than that duration. This helps identify slow queries that might be holding locks for too long.

    # In postgresql.conf
log_min_duration_statement = 500ms

  • max_locks_per_transaction: Increases the maximum number of locks an individual transaction can hold. The default (64) is usually sufficient, but if you have extremely complex transactions, it might need adjustment. Increasing this too much can consume more shared memory.

How to change configuration parameters:

  1. Edit your postgresql.conf file. The path is typically /etc/postgresql/<version>/main/postgresql.conf.

    sudo vi /etc/postgresql/15/main/postgresql.conf

  2. After making changes, reload PostgreSQL for them to take effect (no restart needed for most postgresql.conf changes).

    sudo systemctl reload [email protected]

5. Utilize Monitoring Tools

Proactive monitoring is key to catching contention patterns before they escalate into frequent deadlocks.

  • pg_stat_statements: Enable pg_stat_statements to track execution statistics for all queries executed by your server. This helps identify the most expensive, slowest, or most frequently executed queries that might be candidates for optimization.

    -- In postgresql.conf:
# shared_preload_libraries = 'pg_stat_statements'
-- You need to restart PostgreSQL for this change to take effect.

-- After restart, connect to your database and enable it:
CREATE EXTENSION pg_stat_statements;

-- Then query it:
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

  • Prometheus/Grafana with pg_exporter: Deploy pg_exporter to collect PostgreSQL metrics and visualize them in Grafana. Key metrics to monitor include:

    • pg_stat_activity metrics (number of active queries, waiting queries).
    • Lock wait events.
    • Transaction duration.
    • Deadlock count.

By systematically applying these steps, focusing on both database-level optimizations and critically, application-level transaction design, you can effectively resolve PostgreSQL deadlocks caused by “transaction cancel lock conflicts” and significantly improve your database’s stability and performance.