Technologies/PostgreSQL/postgresql.locks

PostgreSQLMetric

postgresql.locks

Active locks

Dimensions:None

Available on:

Datadog (1)

OpenTelemetry (1)

Prometheus (1)

Interface Metrics (3)

Datadog

postgresql.locks

Enabled with `relations`. The number of locks active for this database. This metric is tagged with db, lock_mode, lock_type, schema, table, granted.

Dimensions:None

OpenTelemetry

postgresql.database.locks

The number of database locks.

Dimensions:None

Prometheus

locks_count

Number of locks

Dimensions:None

Sources

postgresql.locksgithub.com

postgresql.database.locksgithub.com

locks_countgithub.com

Technical Annotations (50)

Configuration Parameters (5)

FP_LOCK_SLOTS_PER_BACKENDrecommended: 16

Default fast path lock slots per backend process - not user-configurable

statement_timeoutrecommended: 30s

prevents indefinite lock waits by canceling statements

idle_in_transaction_session_timeoutrecommended: 1-5min

timeout for idle transactions to prevent lock holding

hot_standby_feedbackrecommended: off (if replica freshness not critical)

Prevents replica queries from blocking primary VACUUM, trading off potential replication lag

lock_timeoutrecommended: 5s

Prevents queries from waiting indefinitely for locks

CLI Commands (21)

SELECT n.nspname AS schema, c.relname AS table, l.locktype, l.mode, l.fastpath FROM pg_locks l JOIN pg_class c ON l.relation = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid

diagnostic

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid;diagnostic

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid WHERE pl.granted = false;diagnostic

SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx ON pl.virtualtransaction = '-1/' || ppx.transaction;diagnostic

pg_terminate_backend(pid)remediation

SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid, blocked_activity.usename AS blocked_user, blocking_activity.usename AS blocking_user, now() - blocked_activity.xact_start AS blocked_transaction_duration, now() - blocking_activity.xact_start AS blocking_transaction_duration, concat(blocked_activity.wait_event_type,':',blocked_activity.wait_event) AS blocked_wait_event, concat(blocking_activity.wait_event_type,':',blocking_activity.wait_event) AS blocking_wait_event, blocked_activity.state AS blocked_state, blocking_activity.state AS blocking_state, blocked_locks.locktype AS blocked_locktype, blocking_locks.locktype AS blocking_locktype, blocked_activity.query AS blocked_statement, 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.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.GRANTED;

diagnostic

SET statement_timeout = '30s';remediation

SELECT pg_try_advisory_lock(table_id) FROM my_table;remediation

SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction'diagnostic

SELECT pid, usename, pg_blocking_pids(pid) AS blocked_by, query AS waiting_query, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event_type = 'Lock';

diagnostic

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid IN (SELECT unnest(pg_blocking_pids(waiting_pid)));remediation

SELECT pg_cancel_backend(pid);remediation

WITH RECURSIVE lock_tree AS ( SELECT pid, pg_blocking_pids(pid) AS blockers, query, 0 AS depth FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0 UNION ALL SELECT a.pid, pg_blocking_pids(a.pid), a.query, lt.depth + 1 FROM pg_stat_activity a JOIN lock_tree lt ON a.pid = ANY(lt.blockers) WHERE lt.depth < 5 ) SELECT * FROM lock_tree ORDER BY depth;

diagnostic

SELECT * FROM tasks WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 1;remediation

SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;remediation

SELECT pid, locktype, relation::regclass, mode, granted, query FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid WHERE relation IS NOT NULL;

diagnostic

SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query, blocking.pid AS blocking_pid, blocking.query AS blocking_query, blocked.wait_event_type, blocked.wait_event FROM pg_stat_activity blocked JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype AND blocked_locks.relation = blocking_locks.relation AND blocked_locks.pid != blocking_locks.pid JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid WHERE NOT blocked_locks.granted AND blocking_locks.granted;

diagnostic

SELECT pg_terminate_backend(blocking_pid);remediation

SELECT pg_cancel_backend(blocking_pid);remediation

SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_query FROM pg_locks blocked_locks JOIN pg_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype AND blocked_locks.relation = blocking_locks.relation AND blocked_locks.pid <> blocking_locks.pid JOIN pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid WHERE NOT blocked_locks.granted;

diagnostic

SELECT pid, relname, state, query_start, query FROM pg_stat_activity WHERE query LIKE '%autovacuum%';diagnostic

Technical References (24)

pg_lockscomponentungranted locksconceptlock contentionconceptlock managercomponentLWLockcomponentfast path lockingconceptPGPROCcomponentpartition pruningconceptAccessShareLockcomponentpg_blocking_pids()componentpg_stat_activitycomponentpg_prepared_xactscomponentprepared transactionsconcepttransactionidconceptrow-level locksconceptpg_advisory_lockcomponentsession-level locksconceptadvisory lock ID spaceconceptwait_eventcomponentwait_event_typecomponentpg_blocking_pidscomponentlock_tree CTEconceptFOR UPDATE SKIP LOCKEDconceptFOR UPDATE NOWAITconcept

Related Insights (20)

Ungranted locks on a relation indicate contention among database clientswarning

▸

Lock contention affects overall database performancewarning

▸

Fast path lock exhaustion causes lock manager contention during read operationswarning

▸

Partitioned table scan without pruning exhausts fast path lockswarning

▸

Lock contention visible in pg_locks indicates waiting processeswarning

▸

Prepared transactions hold locks indefinitely until commit or rollbackcritical

▸

Row-level locks not visible in pg_locks during normal operationinfo

▸

Advisory locks accumulate when queries acquire locks on rows not returned by LIMIT clausewarning

▸

Advisory lock conflicts from unintended shared lock ID space between application componentswarning

▸

Blocked sessions waiting for advisory locks held by other sessionswarning

▸

Deadlocks causing transaction failures and timeoutswarning

▸

Long-running queries cause lock contention and block other operationscritical

▸

Idle transactions hold locks and block operationscritical

▸

Long-running queries on read replica with hot_standby_feedback cause periodic CPU spikes on primarywarning

▸

Queries blocked by lock contention wait indefinitely without timeoutwarning

▸

Lock blocking chains create cascading query delayswarning

▸

Queue-like patterns cause lock contention on pending taskswarning

▸

Excessive exclusive locks indicate lock contention blocking querieswarning

▸

Blocking locks cause query delays and timeoutscritical

▸

Long-running autovacuum workers block operationswarning

▸