Mastering PostgreSQL Locks: Troubleshooting and Performance Insights

In a busy PostgreSQL database, multiple transactions might try to access the same resources simultaneously. Locks are PostgreSQL's way of handling concurrency, ensuring data consistency and preventing conflicts. This article dives into PostgreSQL lock monitoring, explaining the provided scripts, troubleshooting techniques, and how to optimize performance when lock contention occurs.

Sample Code from Command Line

 1SELECT
 2  t.schemaname,
 3  t.relname,
 4  -- l.database, -- id number is less useful, take schemaname from join instead
 5  l.locktype,
 6  page,
 7  virtualtransaction,
 8  pid,
 9  mode,
10  granted
11FROM
12  pg_locks l,
13  --pg_stat_user_tables t
14  pg_stat_all_tables t
15WHERE
16  l.relation = t.relid
17ORDER BY
18  relation ASC;
19
20SELECT
21  relation::regclass AS relation_regclass,
22  *
23FROM
24  pg_locks
25WHERE
26  NOT granted;

Notes List PostgreSQL Locks. Tested on PostgreSQL 8.4, 9.x, 10.x, 11.x, 12.x, 13.0

Code Breakdown:

Script 1:

• Joins: Combines pg_locks (detailed lock info) with pg_stat_all_tables (context on affected tables).
• Output:
  • schemaname, relname: Identifies the locked table.
  • locktype: Type of lock (e.g., row-level, table-level).
  • mode: Lock strength (e.g., "Access Share," "Exclusive").
  • granted: If the lock was successfully acquired.
  • pid: Process ID holding the lock.

Script 2:

• Focus on Conflicts: Isolates locks not granted, likely causing blocking.
• relation::regclass: User-friendly table name output.

Purpose

• Active Lock Visibility: Script 1 gives you a complete overview of all locks in your database.
• Pinpointing Blockage: Script 2 reveals specific conflicts where transactions are waiting to acquire locks.

Insights and Explanations

• Lock Types and Conflicts: Understanding lock types and their hierarchy is crucial for lock troubleshooting. See the PostgreSQL docs for details: https://www.postgresql.org/docs/current/explicit-locking.html.
• Blocking vs. Waiting: A blocked transaction can't proceed, while a waiting one might acquire its lock later.
• Deadlocks: Happen when two or more transactions mutually wait for each other to release locks.

References:

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

pg_locks view: https://www.postgresql.org/docs/current/view-pg-locks.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.sql