Master DevOps Tools & Best Practices - DevOps Knowledge Hub Master DevOps Tools & Best Practices - DevOps Knowledge Hub

How to Identify and Resolve PostgreSQL Deadlocks and Lock Contention

Master PostgreSQL lock contention and deadlocks. Learn to use `pg_locks` to identify blocking sessions, analyze common deadlock scenarios, and discover practical techniques like consistent transaction ordering and optimized queries to prevent and resolve these critical database issues. Ensure smoother, more efficient PostgreSQL operations.

How to Identify and Resolve PostgreSQL Deadlocks and Lock Contention

PostgreSQL deadlocks and lock contention usually show up as an application that suddenly feels stuck. Requests pile up, workers sit in active or idle in transaction, and the database itself may still have plenty of CPU left. The problem is not raw capacity. One session is waiting for another session to release a lock, and sometimes a whole line forms behind it.

The fastest way to handle this is to separate two cases. Lock contention means a session is waiting, but it may eventually continue. A deadlock means two or more sessions are waiting on each other in a cycle, so PostgreSQL has to cancel one transaction. You debug both with the same basic tools, but the fix is often different.

PostgreSQL Locking Basics

PostgreSQL uses locks to protect tables, rows, transactions, and other internal objects while many sessions work at the same time. It also uses MVCC, so ordinary readers and writers often do not block each other. That is why PostgreSQL can handle high concurrency well, but it is also why lock problems can be confusing: the issue is usually a specific statement pattern, not "too many users" in general.

Types of Locks

PostgreSQL utilizes various lock levels, each offering a different degree of protection. Understanding these is key to diagnosing issues:

  • AccessShareLock: Used by ordinary SELECT. It conflicts mainly with ACCESS EXCLUSIVE, which is why many reads can run while writes are happening.
  • RowExclusiveLock: Common for INSERT, UPDATE, and DELETE on a table. The name is easy to misread; it does not mean every row in the table is exclusively locked.
  • ShareUpdateExclusiveLock: Used by operations such as VACUUM without FULL, ANALYZE, and some index operations. It allows normal reads and writes but conflicts with several maintenance operations.
  • ShareLock / ShareRowExclusiveLock / ExclusiveLock: Stronger table-level modes used by specific DDL and constraint-related operations.
  • AccessExclusiveLock: The most restrictive table lock. ALTER TABLE, DROP TABLE, TRUNCATE, and VACUUM FULL can take this kind of lock. It blocks ordinary reads as well as writes.

Row-level locks are separate from table-level lock modes. An UPDATE takes a table-level RowExclusiveLock and row locks on the rows it changes. When people say "this row is locked," they usually mean another transaction has modified or selected that row FOR UPDATE and has not committed yet.

Lock Modes

Lock modes indicate the type of access a transaction requires. They are often represented by names like RowExclusiveLock, ShareLock, ExclusiveLock, etc.

Identifying Lock Contention and Blocking Sessions

Lock contention occurs when multiple transactions are waiting for locks held by other transactions. This can significantly slow down your application. The pg_locks system view is your primary tool for diagnosing these issues.

Using pg_locks

The pg_locks view provides information about all active locks in the database system. It's invaluable for understanding which sessions are holding locks and which are waiting.

Here's a common query to identify blocking sessions:

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocked_locks.locktype AS blocked_locktype,
    blocked_locks.virtualtransaction AS blocked_vtx,
    blocked_locks.mode AS blocked_mode,
    blocked_activity.query AS blocked_statement,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocking_locks.locktype AS blocking_locktype,
    blocking_locks.virtualtransaction AS blocking_vtx,
    blocking_locks.mode AS blocking_mode,
    blocking_activity.query AS blocking_statement
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.offset IS NOT DISTINCT FROM blocked_locks.offset AND
    blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND
    blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
JOIN
    pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE
    NOT blocked_locks.granted
    AND blocking_locks.pid != blocked_locks.pid;

Explanation of the query:

  • We join pg_locks with pg_stat_activity twice: once for the blocked process and once for the blocking process.
  • The WHERE NOT blocked_locks.granted clause filters for locks that are currently being waited for.
  • The blocking_locks.pid != blocked_locks.pid ensures we don't report a session blocking itself.
  • The join conditions on pg_locks match locks on the same resource.

Interpreting the Output

  • blocked_pid / blocking_pid: The process IDs (PIDs) of the sessions involved.
  • blocked_user / blocking_user: The users associated with these PIDs.
  • blocked_statement / blocking_statement: The SQL queries that are currently running or waiting.
  • blocked_mode / blocking_mode: The lock modes requested and held.

If this query returns rows, you have lock contention. The blocking_pid is holding a lock that the blocked_pid is waiting for.

Understanding and Resolving Deadlocks

A deadlock occurs when two or more transactions are each waiting for a lock held by another transaction in the cycle, creating a circular dependency that neither can resolve on its own. PostgreSQL detects deadlocks and automatically resolves them by aborting one of the transactions, typically the one that is causing the deadlock and has done the least work.

Common Deadlock Scenarios

  1. Two transactions updating different rows in different tables in reverse order:

    • Transaction A: Updates row X in Table 1, then attempts to update row Y in Table 2.
    • Transaction B: Updates row Y in Table 2, then attempts to update row X in Table 1. If Transaction A locks row X and Transaction B locks row Y, they will deadlock when they try to acquire the lock held by the other.

  2. UPDATE followed by SELECT ... FOR UPDATE:

    • Transaction A: Updates a row.
    • Transaction B: Executes SELECT ... FOR UPDATE on the same row. If the UPDATE is still holding a row-exclusive lock when the SELECT FOR UPDATE tries to acquire a share lock, and other dependencies exist, a deadlock can occur.

Detecting Deadlocks

PostgreSQL logs deadlock information to its server log. You'll typically see messages like:

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 5679.
Process 5679 waits for ExclusiveLock on tuple (0,1) of relation 12345; blocked by process 1234.
HINT:  See server log for detail.

PostgreSQL automatically chooses one transaction to abort so the others can continue. Do not rely on pg_stat_activity after the fact for the full story; by the time you look, the canceled statement may be gone. The server log is usually the best record of the cycle.

Resolving Deadlocks

When a deadlock is detected and PostgreSQL resolves it by aborting a transaction:

  1. Identify the Victim: Check the PostgreSQL logs for the deadlock detected message. It will specify which process was aborted.
  2. Retry the Aborted Transaction: The application receiving SQLSTATE 40P01 (deadlock_detected) should retry the whole transaction when the operation is safe to retry. Retrying only the last statement can leave application state inconsistent.
  3. Analyze the Cause: The key to resolution is preventing future deadlocks. This involves understanding why the deadlock occurred (as described in common scenarios) and adjusting the application logic or database design.

Techniques to Prevent Lock Contention and Deadlocks

Prevention is always better than cure. Implementing strategies to minimize lock contention and avoid deadlock situations is crucial for a high-performance PostgreSQL database.

1. Consistent Transaction Ordering

  • Rule: Always access and modify resources (tables, rows) in the same order across all transactions. If multiple transactions need to update TableA and TableB, ensure they always update TableA before TableB, or vice-versa, in a consistent manner.
  • Example: If a transaction needs to update records in users and orders, always perform operations on users first, then orders. Avoid scenarios where one transaction updates users then orders, while another updates orders then users.

2. Minimize Transaction Duration

  • Rule: Keep transactions as short as possible. The longer a transaction is open, the more locks it holds, increasing the chance of contention.
  • Action: Perform only necessary database operations within a transaction. Move non-database-related work (e.g., external API calls, complex calculations not dependent on transaction state) outside the transaction boundary.

3. Use Appropriate Isolation Levels

  • Rule: Understand and choose the correct transaction isolation level. PostgreSQL offers:
    • READ UNCOMMITTED (simulated by READ COMMITTED in PostgreSQL)
    • READ COMMITTED (default)
    • REPEATABLE READ
    • SERIALIZABLE
  • Action: The default READ COMMITTED provides good performance while preventing dirty reads. REPEATABLE READ and SERIALIZABLE offer stronger consistency but can lead to more serialization_failure errors (which are essentially deadlocks for snapshot isolation) and potentially more lock contention. Use them only when absolutely necessary.

4. Optimize Queries and Indexes

  • Rule: Slow queries hold locks for longer. Ensure your queries are efficient and well-indexed.
  • Action: Use EXPLAIN ANALYZE to identify slow queries. Add appropriate indexes to speed up data retrieval, especially for WHERE clauses and JOIN conditions.

5. Use SELECT ... FOR UPDATE Sparingly

  • Rule: SELECT ... FOR UPDATE locks rows for the duration of the transaction. This is powerful for preventing race conditions but can also be a major source of contention.
  • Action: Only use it when you truly need to lock rows to prevent them from being modified by other transactions before your transaction completes its work. Consider if advisory locks might be a better fit for certain scenarios.

6. Advisory Locks

  • Rule: For application-level locking or more complex synchronization needs that don't map directly to database object locks, PostgreSQL's advisory locks can be a powerful tool.
  • Action: Use functions like pg_advisory_lock(), pg_advisory_lock_shared(), and pg_advisory_unlock() to implement custom locking mechanisms. These locks are not automatically detected by the deadlock detection mechanism, so application logic must manage them carefully.

7. Batching Operations

  • Rule: Instead of issuing many individual UPDATE or DELETE statements, consider batching them into a single statement or using COPY for bulk loading/updating where possible.
  • Action: A single UPDATE statement might acquire locks more efficiently than a loop of individual UPDATEs. Analyze the locking behavior of your batch operations.

A Practical Triage Flow

When an incident is active, start with the waiting sessions, not the oldest theory in your head:

SELECT
    now() - a.query_start AS waiting_for,
    a.pid,
    a.usename,
    a.state,
    a.wait_event_type,
    a.wait_event,
    pg_blocking_pids(a.pid) AS blocked_by,
    a.query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY waiting_for DESC;

If one blocking PID appears again and again, inspect it:

SELECT
    pid,
    usename,
    state,
    now() - xact_start AS transaction_age,
    now() - query_start AS query_age,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE pid = 12345;

The phrase to watch for is idle in transaction. That session is not actively doing useful database work, but it may still be holding locks. This often comes from application code that opens a transaction, performs a query, calls an external API, and only commits after the API returns. Move the external call outside the transaction if you can.

Cancel carefully. SELECT pg_cancel_backend(pid) asks the current query to stop. SELECT pg_terminate_backend(pid) kills the session and rolls back its open transaction. In a production incident, terminating the blocker may be the right call, but capture the query and transaction age first so you can fix the code path later.

One more habit helps after the incident: save the blocking query, the blocked query, and the transaction boundaries from application logs. The SQL statement alone is often not enough. A harmless UPDATE users SET last_seen_at = now() can become a blocker if it sits inside a transaction that also waits on a payment API. Deadlock fixes usually live at the transaction-flow level, not inside one isolated statement.

The durable fixes are usually plain: keep transactions short, touch shared resources in a consistent order, add indexes that keep updates from scanning too much, and make application retries handle 40P01 and serialization failures cleanly. PostgreSQL can detect a deadlock, but it cannot redesign the transaction pattern for you. That part belongs in the application and schema review after the incident is calm.