Troubleshooting PostgreSQL Deadlocks & Transaction Cancellation on CentOS Stream / Rocky Linux

Resolve critical PostgreSQL deadlocks and transaction cancellations on CentOS Stream and Rocky Linux. Dive into root causes and apply step-by-step fixes to restore database stability and performance.

A PostgreSQL database experiencing deadlocks can severely impact application performance and availability, leading to frustrating user experiences and data inconsistencies. On CentOS Stream or Rocky Linux, these issues manifest as transactions being canceled with "deadlock detected" messages in the PostgreSQL logs. This guide provides a deep dive into diagnosing and resolving such critical database contention.

Symptom & Error Signature

Users typically observe slow application responses, failed operations, or intermittent errors indicating that a database transaction could not complete. From a system perspective, the PostgreSQL logs (/var/lib/pgsql/data/log/postgresql-*.log or journalctl -u postgresql) will show entries similar to these:

2023-10-26 10:30:05 UTC [12345]: [2-1] user=app_user,db=mydb,app_name=my_app LOG:  deadlock detected
2023-10-26 10:30:05 UTC [12345]: [3-1] user=app_user,db=mydb,app_name=my_app DETAIL:  Process 12345 waits for ShareLock on transaction 67890; blocked by process 54321.
    Process 54321 waits for ShareLock on transaction 67891; blocked by process 12345.
2023-10-26 10:30:05 UTC [12345]: [4-1] user=app_user,db=mydb,app_name=my_app HINT:  See server log for query details.
2023-10-26 10:30:05 UTC [12345]: [5-1] user=app_user,db=mydb,app_name=my_app CONTEXT:  while updating tuple (0,21) in relation "mytable"
2023-10-26 10:30:05 UTC [12345]: [6-1] user=app_user,db=mydb,app_name=my_app STATEMENT:  UPDATE mytable SET status = 'processed' WHERE id = 123;
2023-10-26 10:30:05 UTC [12345]: [7-1] user=app_user,db=mydb,app_name=my_app ERROR:  canceling statement due to deadlock

The key indicators are LOG: deadlock detected, DETAIL: showing the circular wait, and ERROR: canceling statement due to deadlock.

Root Cause Analysis

A PostgreSQL deadlock occurs when two or more transactions are waiting for each other to release a lock, creating a circular dependency. PostgreSQL's deadlock detector identifies these situations and arbitrarily picks one transaction (the "deadlock victim") to terminate, rolling back its changes to allow the other transaction(s) to proceed.

The underlying causes can be multifaceted:

1. Poor Application Logic and Transaction Design:

   • Inconsistent Lock Ordering: Applications acquiring locks on database rows/tables in different orders across concurrent transactions. For example, Transaction A locks row X then row Y, while Transaction B locks row Y then row X.
   • Long-Running Transactions: Holding locks for extended periods, increasing the window for contention.
   • Explicit Locking Issues: Incorrect use of SELECT ... FOR UPDATE or LOCK TABLE statements, leading to contention.
   • Lack of Retries: Applications failing to implement retry logic for deadlock detected errors, leading to immediate failure instead of eventual success.

2. Inefficient Database Schema and Query Design:

   • Missing or Inefficient Indexes: Queries performing full table scans or using inefficient indexes can lead to more rows being locked than necessary, increasing the likelihood of contention.
   • Update Hot Spots: Frequently updated rows or small tables can become contention points.
   • Incorrect Isolation Levels: While READ COMMITTED (the default) generally prevents read-write deadlocks, REPEATABLE READ or SERIALIZABLE can increase the chance of deadlocks due to stricter locking rules.

3. High Concurrency and Resource Contention:

   • A large number of concurrent transactions attempting to modify the same data can inherently lead to increased contention and deadlocks.
   • Insufficient system resources (CPU, RAM, I/O) can slow down transactions, prolonging lock hold times.

4. Race Conditions: Specific timing of concurrent operations that are difficult to reproduce but can consistently create deadlock scenarios under load.

Step-by-Step Resolution

Addressing PostgreSQL deadlocks requires a systematic approach, often involving changes at both the database and application levels.

1. Identify the Deadlock Victim and Queries

The first step is to precisely identify which queries and transactions are involved in deadlocks. PostgreSQL's logging is invaluable here.

a. Configure PostgreSQL Logging: Edit postgresql.conf (typically /var/lib/pgsql/data/postgresql.conf) to ensure detailed deadlock information is logged.

# Log configuration for better deadlock insights
log_min_duration_statement = 0       # Log all statements if trying to catch rare deadlocks, or a higher value (e.g., 500ms) for slow queries.
log_lock_waits = on                  # Log when a session waits for a lock for more than deadlock_timeout.
log_statement = 'ddl'                # Log DDL statements (CREATE, ALTER, DROP).
log_timezone = 'UTC'                 # Standardize timezone for logs.

Setting log_min_duration_statement = 0 can generate a very high volume of logs, impacting I/O and disk space. Use this sparingly for targeted debugging, then revert to a higher value or off.

After modifying postgresql.conf, reload the PostgreSQL service:

sudo systemctl reload postgresql

b. Analyze Logs: Regularly review your PostgreSQL logs for the deadlock detected signature. The DETAIL: section will provide the process IDs (pid) of the transactions involved and the type of lock contention.

sudo journalctl -u postgresql | grep "deadlock detected" -A 10
# OR if logging to file:
sudo grep -i "deadlock detected" /var/lib/pgsql/data/log/postgresql-*.log -A 10

2. Review and Optimize Application Transaction Logic

This is often the most critical area for resolving deadlocks.

a. Standardize Lock Acquisition Order: Ensure that whenever multiple rows or resources are locked within a transaction, they are always locked in the same order across all concurrent transactions. For example, if transactions involve table_A and table_B, always lock table_A first, then table_B. If locking specific rows, consider locking them by their primary key in ascending order.

b. Minimize Transaction Duration: Keep transactions as short as possible. Acquire locks just before they are needed and release them as soon as possible. Avoid user interaction or external API calls within active transactions.

c. Implement SELECT ... FOR UPDATE Correctly: When you need to lock rows for modification, SELECT ... FOR UPDATE is appropriate. However, ensure it's used consistently and on necessary rows only. Example of problematic vs. improved locking:

-- Problematic: Two transactions could try to update different rows of 'accounts'
-- but acquire locks in different orders.
-- Txn A:
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- locks row 1
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; -- waits for row 2 if Txn B has it
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;

-- Txn B:
BEGIN;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; -- locks row 2
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- waits for row 1 if Txn A has it
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
COMMIT;

To avoid this, ensure a consistent order:

-- Improved: Always lock rows by ID in ascending order
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE; -- Locks both rows 1 and 2 in order
-- ... perform updates ...
COMMIT;

d. Implement Retry Logic: Since PostgreSQL automatically cancels one transaction in a deadlock, the application should be prepared to catch the deadlock detected error and retry the entire transaction (with a backoff delay).

# Example Python pseudo-code
import time
from psycopg2 import OperationalError, errorcodes

def execute_transaction_with_retry(cursor, query_func, max_retries=5):
    for attempt in range(max_retries):
        try:
            cursor.execute("BEGIN")
            query_func(cursor) # This function contains your SQL operations
            cursor.execute("COMMIT")
            return
        except OperationalError as e:
            if e.pgcode == errorcodes.DEADLOCK_DETECTED:
                print(f"Deadlock detected, retrying (attempt {attempt+1}/{max_retries})...")
                cursor.execute("ROLLBACK")
                time.sleep(0.1 * (2 ** attempt)) # Exponential backoff
            else:
                cursor.execute("ROLLBACK")
                raise
        except Exception as e:
            cursor.execute("ROLLBACK")
            raise
    raise Exception("Transaction failed after multiple retries due to deadlock.")

3. Optimize Database Schema and Queries

a. Add Missing Indexes: Indexes can significantly reduce the number of rows a query has to scan and, consequently, the number of locks it might acquire. Analyze EXPLAIN ANALYZE output for frequently deadlocking queries.

-- Example: If 'status' and 'user_id' are often filtered/updated
CREATE INDEX idx_mytable_status_user_id ON mytable (status, user_id);

While indexes improve SELECT performance, they can add overhead to INSERT, UPDATE, and DELETE operations. Add them judiciously after profiling.

b. Tune deadlock_timeout: This parameter (default 1s) defines how long a transaction waits for a lock before checking for deadlocks. If your transactions are genuinely long-running and not deadlocked, increasing this can reduce false positives. However, decreasing it can make deadlocks detected sooner, freeing resources faster. For most cases, the default is reasonable; adjust only if you understand the implications.

Edit postgresql.conf:

deadlock_timeout = 500ms # Default is 1s, can be reduced to detect deadlocks faster

Then reload: sudo systemctl reload postgresql.

c. Review Isolation Levels: Ensure your application is using an appropriate transaction isolation level. READ COMMITTED (default) is usually sufficient. Higher isolation levels like REPEATABLE READ or SERIALIZABLE offer stronger guarantees but are more prone to deadlocks.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- Use with caution and only if truly needed

4. Monitor and Analyze pg_stat_activity & pg_locks

These system views provide real-time information about database activity and locks.

a. pg_stat_activity: Shows information about active sessions, including the query they are running, their state, and transaction ID.

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

b. pg_locks: Provides details on all locks currently held or awaited in the database. This is crucial for understanding lock contention.

SELECT
    pa.pid,
    pa.usename,
    pa.application_name,
    pa.query,
    pl.relation::regclass,
    pl.locktype,
    pl.mode,
    pl.granted,
    pl.fastpath
FROM pg_locks pl
JOIN pg_stat_activity pa ON pl.pid = pa.pid
WHERE NOT pl.granted
ORDER BY pl.pid, pl.relation::regclass;

Looking for granted = 'f' (false) indicates a session is waiting for a lock. Correlate this with pg_stat_activity to find out what query is waiting and what it's waiting for.

5. Consider pg_repack for Table/Index Bloat

Over time, frequently updated tables can suffer from "bloat," which can indirectly contribute to deadlocks by making queries less efficient. pg_repack (a PostgreSQL extension) can rebuild tables and indexes online without requiring exclusive locks for extended periods, reducing bloat.

a. Install pg_repack:

sudo dnf install -y postgresql-contrib
# For specific PostgreSQL version (e.g., pg15)
# sudo dnf install -y postgresql15-contrib

Then, connect to your database as a superuser and create the extension:

psql -U postgres -d your_database
CREATE EXTENSION pg_repack;

b. Use pg_repack:

pg_repack -d your_database -t your_table_name
# Or to repack all tables and indexes in a database
# pg_repack -d your_database

Running pg_repack on very large tables can be resource-intensive. Schedule it during off-peak hours and monitor system resources.

6. Upgrade PostgreSQL (if applicable)

Newer versions of PostgreSQL often include performance improvements, better query planner optimizations, and sometimes even enhancements to deadlock detection or prevention mechanisms. While not a direct fix for application-level deadlocks, it can generally improve overall database health and resilience.

a. Check current version:

psql -V

b. Plan for upgrade: Consult the official PostgreSQL documentation for upgrade paths specific to your version and OS. This typically involves backing up your database, installing the new PostgreSQL package, initializing a new data directory, and migrating data using pg_upgrade.

Database upgrades are critical operations. Always perform a full backup and test the upgrade process thoroughly in a staging environment before applying it to production.

By diligently applying these steps, focusing on both application code and database configuration, you can effectively diagnose and mitigate PostgreSQL deadlocks on your CentOS Stream or Rocky Linux systems, leading to a more stable and performant database environment.