Inspect PostgreSQL Lock Management Settings with pg_settings

Inspect PostgreSQL Lock Management Settings

Where pg_locks shows the locks held right now, pg_settings shows the rules that govern them: how long a session waits before PostgreSQL checks for a deadlock, how big the shared lock table is, and when a statement gives up waiting. The query below lists the lock settings with their current values, where each value came from, and what it takes to change it.

Purpose and Overview

Lock problems show up as queries stuck waiting, deadlock errors, or transactions that run out of lock-table space. Before changing anything, you need to know what the server is actually running with — not what the configuration file says, since a setting can also come from the command line or a SET in the session.

pg_settings answers that from inside the database. It has one row per configuration parameter, and its category column is the "logical group of the parameter." PostgreSQL's Lock Management group holds five parameters: deadlock_timeout, max_locks_per_transaction, max_pred_locks_per_transaction, max_pred_locks_per_relation and max_pred_locks_per_page.

One lock setting is not in that group. lock_timeout is documented under Client Connection Defaults, so a filter on the category alone misses it. The query adds it by name.

Sample Code

 1SELECT
 2    name,
 3    setting,
 4    unit,
 5    context,
 6    source,
 7    boot_val,
 8    reset_val,
 9    pending_restart,
10    short_desc
11FROM pg_settings
12WHERE category ILIKE 'Lock Management%'
13   OR name = 'lock_timeout'
14ORDER BY name;

Notes: Runs on PostgreSQL 9.5 and later. On 9.4 and earlier, remove pending_restart, which those versions do not have. The sourcefile column, not selected here, is null for roles without pg_read_all_settings.

Code Breakdown

The query reads the settings view, keeps the lock rows, and orders them by name.

The filter

category ILIKE 'Lock Management%' returns the five Lock Management parameters. OR name = 'lock_timeout' adds the one lock-related setting that lives in another group.

What each column tells you

The pg_settings documentation defines the columns:

• setting and unit — the current value and its "implicit unit," so 1000 with unit ms is one second.
• context — the "context required to set the parameter's value." A postmaster setting "can only be applied when the server starts, so any change requires restarting the server"; a sighup setting can be changed in postgresql.conf and applied by reloading.
• source — the "source of the current parameter value," such as the default, the configuration file, or a per-session SET.
• boot_val — the "parameter value assumed at server startup if the parameter is not otherwise set."
• reset_val — the "value that RESET would reset the parameter to in the current session."
• pending_restart — "true if the value has been changed in the configuration file but needs a restart."
• short_desc — "a brief description of the parameter."

Reading the result

Compare setting with boot_val to see what has been changed from the default, and look at source to see where the change was made. A row with pending_restart = true means someone has edited the file but the server is still running the old value.

Key Lock Management Parameters

The descriptions below come from the Lock Management and Client Connection Defaults chapters.

deadlock_timeout

"The amount of time to wait on a lock before checking to see if there is a deadlock condition." The default is one second, "which is probably about the smallest value you would want in practice." The check is relatively expensive, so raising the value cuts wasted checks but "slows down reporting of real deadlock errors." It also sets how long a wait lasts before log_lock_waits writes a log message. Only superusers and roles with the SET privilege on it can change it.

max_locks_per_transaction

The shared lock table has room for this many objects, such as tables, per server process or prepared transaction. The default is 64. It limits the average, not each transaction: one transaction can lock more as long as everything fits. It is "not the number of rows that can be locked; that value is unlimited." It can only be set at server start, and a standby must use the same or a higher value than its primary, or queries are not allowed on the standby.

Predicate lock settings

max_pred_locks_per_transaction (default 64, server start only) sizes the shared predicate lock table used by serializable transactions. max_pred_locks_per_relation (default -2) sets how many pages or tuples of one relation can be predicate-locked before the lock is promoted to the whole relation; a negative value means max_pred_locks_per_transaction divided by its absolute value. max_pred_locks_per_page (default 2) sets how many rows on one page can be locked before the lock covers the page.

lock_timeout

Aborts "any statement that waits longer than the specified amount of time while attempting to acquire a lock." The limit applies to each lock attempt separately, and to both explicit and implicit locks. The default, zero, disables it. If statement_timeout is set to the same or a lower value, lock_timeout never fires. The documentation advises against setting it in postgresql.conf, "because it would affect all sessions."

Practical Applications

Investigating deadlock errors

When the log reports deadlocks, check deadlock_timeout and log_lock_waits first. Lowering deadlock_timeout for a while makes lock waits show up in the log sooner — the documentation suggests a shorter value when you are "trying to investigate locking delays."

Transactions that touch many tables

A transaction that touches many tables — the documentation's example is a "query of a parent table with many children" — can run out of lock-table space. The query shows the current max_locks_per_transaction and that its context is postmaster, so raising it means a planned restart.

Planning a standby

Before building or promoting a standby, run the query on the primary and the standby. If max_locks_per_transaction is lower on the standby, queries will not be allowed there.

Protecting migrations from long lock waits

For a schema change that may have to wait for locks, set lock_timeout in the migration session — not server-wide — so the change fails fast and can be retried, and confirm with the query that the server-wide value is still zero.

Version Compatibility

The five Lock Management parameters and lock_timeout are documented in every currently supported PostgreSQL release, and pg_settings has been part of PostgreSQL for many versions. The pending_restart column first appears in the PostgreSQL 9.5 documentation; the 9.4 documentation does not list it, so drop it from the select list on older servers.

If a future release moves a parameter to another group, the category filter will miss it; adding parameters by name, as the query does for lock_timeout, avoids depending on the category text.

Best Practices

• Check source before you change anything — the value in force may not be the one in postgresql.conf.
• Plan restarts for postmaster settings — max_locks_per_transaction and max_pred_locks_per_transaction only change at server start.
• Keep standby lock settings at or above the primary's — or queries on the standby will be refused.
• Set lock_timeout per session, not in postgresql.conf — the documentation warns it would affect all sessions.
• Leave deadlock_timeout at one second or higher in production — lower it only for a short investigation.

References

• PostgreSQL: pg_settings — the view's columns and the meaning of each context value
• PostgreSQL: Lock Management — deadlock_timeout, max_locks_per_transaction and the predicate lock settings
• PostgreSQL: Client Connection Defaults — lock_timeout and its interaction with statement_timeout
• PostgreSQL: Explicit Locking — lock modes and deadlocks
• HariSekhon/SQL-scripts: postgres_settings_locking.sql — original seed script