Fix Postgres Lock Contention and Deadlocks (2026)

Postgres is refusing to acquire locks, leading to transactions getting stuck or outright deadlocking. This is happening because the database’s internal lock manager is overwhelmed by concurrent requests, preventing new transactions from proceeding.

Common Causes and Fixes

Long-Running Transactions Holding Locks: A single transaction that runs for an extended period can block many others, creating a cascade of lock waits.
- Diagnosis:
```
SELECT pid, age(clock_timestamp(), query_start), query, state
FROM pg_stat_activity
WHERE state = 'active' AND query_start < clock_timestamp() - interval '5 minutes'
ORDER BY query_start;
```
  Look for query_start values that are significantly in the past.
- Fix: Identify the long-running pid from the query output and, if safe, terminate it:
```
SELECT pg_terminate_backend(PID_FROM_ABOVE);
```
  This forcefully ends the transaction, releasing its locks.
- Why it works: The pg_terminate_backend command signals the PostgreSQL backend process associated with the given pid to shut down, which in turn rolls back any active transaction and releases all held locks.
Excessive SELECT FOR UPDATE or SELECT FOR SHARE: These explicitly lock rows, and if not managed carefully, can lead to contention.
- Diagnosis:
```
SELECT
    locktype, relation::regclass, mode, granted,
    pid, granted,
    pg_blocking_pids(pid) AS blocked_by
FROM pg_locks
WHERE NOT granted AND pid IN (SELECT pid FROM pg_stat_activity WHERE state = 'active');
```
  Look for locktype = 'relation' or locktype = 'tuple' where mode is ExclusiveLock (for FOR UPDATE) or ShareLock (for FOR SHARE) and granted is false.
- Fix: Optimize queries to acquire locks only when necessary and for the shortest duration. Consider using SKIP LOCKED if applicable, or redesigning transactions to avoid holding locks across multiple operations.
```
-- Example: Adding SKIP LOCKED
SELECT * FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED;
```
  This tells Postgres to ignore rows that are already locked, preventing the current transaction from waiting.
- Why it works: SKIP LOCKED instructs the database to move on to the next row if the current one is locked, avoiding the wait that would otherwise cause contention.
Deadlocks Due to Circular Waiting: Two or more transactions are waiting for locks held by each other.
- Diagnosis: Check the PostgreSQL logs for deadlock detection messages. These usually contain lines like:
```
ERROR: deadlock detected
DETAIL: Process PID_A waits for ShareLock on transaction 123; blocked by process PID_B.
Process PID_B waits for ExclusiveLock on tuple (1,2) of relation 12345; blocked by process PID_A.
```
  You can also use pg_locks as shown above, looking for processes that are blocked_by each other.
- Fix: PostgreSQL automatically detects and resolves deadlocks by rolling back one of the involved transactions. Ensure your application has robust error handling to retry failed transactions. Review transaction order to minimize the chance of deadlocks; always acquire locks in a consistent order across all transactions.
- Why it works: Postgres’s internal deadlock detector identifies circular dependencies and aborts one transaction to allow others to proceed, thus breaking the cycle.
Insufficient max_connections: While not directly a lock contention issue, a full connection pool can indirectly lead to perceived lock issues as new connections fail to establish, preventing work from starting.
- Diagnosis:
```
SHOW max_connections;
SELECT count(*) FROM pg_stat_activity;
```
  If count(*) is close to max_connections, your pool is full.
- Fix: Increase max_connections in postgresql.conf and restart PostgreSQL.
```
# postgresql.conf
max_connections = 200
```
  (Default is often 100) Then restart the PostgreSQL service.
- Why it works: A higher max_connections limit allows more concurrent client sessions to connect to the database, reducing the likelihood of connection refusal errors that can be mistaken for lock issues.
Inefficient Queries Causing Table/Row Scans: Queries that scan large portions of tables without proper indexing can acquire many row locks, increasing contention.
- Diagnosis:
```
EXPLAIN ANALYZE SELECT ... FROM your_table WHERE ...;
```
  Look for Seq Scan or Append operations on large tables without a Bitmap Heap Scan or Index Scan.
- Fix: Add appropriate indexes to speed up queries and reduce the number of rows scanned and locked.
```
CREATE INDEX idx_your_table_column ON your_table (column_name);
```
  This creates an index on column_name, allowing the database to quickly find specific rows without scanning the entire table.
- Why it works: An index allows the database to locate required rows much faster, often by directly accessing specific pages or tuples, thereby minimizing the number of rows that need to be locked and the duration of the lock.
Autovacuum Not Keeping Up: Autovacuum’s role is to clean up dead tuples and prevent transaction ID wraparound. If it’s not running frequently enough or is overwhelmed, it can lead to increased lock contention and performance degradation.
- Diagnosis:
```
SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
```
  Look for tables with a high number of n_dead_tup and last_autovacuum timestamps that are old.
- Fix: Tune autovacuum parameters in postgresql.conf and potentially increase autovacuum_max_workers.
```
# postgresql.conf
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.1
autovacuum_max_workers = 5
```
  These values make autovacuum run more aggressively on tables that have seen a moderate number of changes.
- Why it works: More frequent and aggressive vacuuming reclaims space from dead tuples, reduces table bloat, and helps prevent transactions from blocking each other due to outdated row versions.

After fixing these, you’ll likely encounter errors related to connection pool exhaustion if your application isn’t properly managing its pool size, or perhaps missing indexes on newly created tables.