PostgreSQL Lock Troubleshooting: Unlocking Blocked Processes

In a busy PostgreSQL environment, transactions can sometimes become blocked, waiting for other transactions to release locks. This can slow performance and even cause stalls. This article dissects a powerful PostgreSQL script designed to expose blocked processes, offering insights to help you troubleshoot and optimize performance.

Sample Code from Command Line

 1SELECT
 2  blocked_locks.pid         AS blocked_pid,
 3  blocked_activity.usename  AS blocked_user,
 4  blocking_locks.pid        AS blocking_pid,
 5  blocking_activity.usename AS blocking_user,
 6  blocked_activity.query    AS blocked_statement,
 7  blocking_activity.query   AS current_statement_in_blocking_process
 8FROM
 9  pg_catalog.pg_locks AS blocked_locks
10JOIN
11  pg_catalog.pg_stat_activity AS blocked_activity
12ON
13  blocked_activity.pid = blocked_locks.pid
14JOIN
15  pg_catalog.pg_locks AS blocking_locks
16ON
17  blocking_locks.locktype = blocked_locks.locktype
18  AND blocking_locks.database       IS NOT DISTINCT FROM blocked_locks.database
19  AND blocking_locks.relation       IS NOT DISTINCT FROM blocked_locks.relation
20  AND blocking_locks.page           IS NOT DISTINCT FROM blocked_locks.page
21  AND blocking_locks.tuple          IS NOT DISTINCT FROM blocked_locks.tuple
22  AND blocking_locks.virtualxid     IS NOT DISTINCT FROM blocked_locks.virtualxid
23  AND blocking_locks.transactionid  IS NOT DISTINCT FROM blocked_locks.transactionid
24  AND blocking_locks.classid        IS NOT DISTINCT FROM blocked_locks.classid
25  AND blocking_locks.objid          IS NOT DISTINCT FROM blocked_locks.objid
26  AND blocking_locks.objsubid       IS NOT DISTINCT FROM blocked_locks.objsubid
27  AND blocking_locks.pid != blocked_locks.pid
28JOIN
29  pg_catalog.pg_stat_activity blocking_activity
30ON
31  blocking_activity.pid = blocking_locks.pid
32WHERE
33  NOT blocked_locks.granted;

Notes List PostgreSQL locks blocked. Requires PostgreSQL 9.2+. Tested on PostgreSQL 9.2+, 10.x, 11.x, 12.x, 13.0.

Code Breakdown:

• Strategic Joins: Links multiple PostgreSQL system views:
  • pg_catalog.pg_locks: Core lock information.
  • pg_catalog.pg_stat_activity: Details on active processes.
• Sophisticated Filtering: Joins use numerous conditions to ensure we're seeing relevant lock conflicts.
• Output:
  • blocked_pid, blocking_pid: Process IDs of blocked and blocking transactions.
  • blocked_user, blocking_user: Associated usernames.
  • blocked_statement: Query the blocked process was trying to execute.
  • current_statement_in_blocking_process: Query the blocking process is currently running.

Purpose This script's primary purpose is to give you the precise details needed to investigate the cause of blocked processes in your PostgreSQL database.

Insights and Explanations

• What's Blocking What: Understand which processes are holding resources others need, creating stalls.
• Query Analysis: Examine the queries involved in a lock conflict. This can offer clues for optimization or restructuring logic.
• Troubleshooting Starting Point: This isn't a fix, but rather an essential diagnostic tool. You'll then need to decide if killing long-running queries, changing isolation levels, etc. is needed. Insights and Explanations
• Blocked vs. Blocking: Demystify the relationship between blocked and blocking processes.
• Targeted Visibility: Pinpoint the specific transactions causing blockages.
• Query Clues: See active queries involved in lock conflicts.
• Troubleshooting Techniques: Gain strategies to address locked scenarios.

References:

PostgreSQL on Locks: https://www.postgresql.org/docs/current/explicit-locking.html

pg_stat_activity view: https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW

Lock Monitoring: https://wiki.postgresql.org/wiki/Lock_Monitoring

Source:

https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_locks_blocked.sql