Master PostgreSQL 17 Locks: From Fundamentals to Advanced Monitoring & Optimization

Background

In high‑concurrency production environments PostgreSQL relies on a sophisticated lock manager to guarantee data consistency and prevent deadlocks. Understanding lock types, their interaction with MVCC, and the tools for monitoring and tuning them is essential for reliable operation.

Prerequisites

Basic Linux command knowledge

Familiarity with SQL and transaction concepts

Understanding of ACID properties

Core Concepts

Lock Classification

Locks are categorized by the object they protect (table, row, page, advisory, transaction) and by mode (Exclusive, Share, Update, etc.). Table‑level locks are used for DDL and bulk operations, while row‑level locks protect individual tuples during UPDATE/DELETE.

MVCC Interaction

Multi‑Version Concurrency Control (MVCC) provides snapshot isolation without blocking reads. Writes acquire row‑level locks to prevent concurrent modifications, and DDL acquires exclusive table locks to serialize schema changes. MVCC and locks together ensure isolation and consistency.

Table‑Level Locks

Common modes (shown by pg_locks) include:

ACCESS SHARE   – SELECT (concurrent reads)
ROW SHARE      – SELECT … FOR SHARE / FOR KEY SHARE
ROW EXCLUSIVE  – INSERT, UPDATE, DELETE
SHARE UPDATE EXCLUSIVE – VACUUM, ANALYZE, CREATE INDEX
SHARE          – CREATE INDEX (blocks writes)
EXCLUSIVE      – Blocks all concurrent access
ACCESS EXCLUSIVE – DDL operations (DROP, ALTER, LOCK TABLE)

Typical queries to inspect locks:

SELECT l.locktype, d.relname, l.mode, l.granted, l.pid,
       a.usename, a.query, a.query_start, a.state
FROM pg_locks l
LEFT JOIN pg_database d ON d.oid = l.database
LEFT JOIN pg_class c ON c.oid = l.relation
LEFT JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation IS NOT NULL
  AND d.datname = current_database()
ORDER BY l.pid, l.mode;

Explicit acquisition uses LOCK TABLE … or advisory functions; NOWAIT or lock_timeout can abort quickly if the lock cannot be obtained.

Row‑Level Locks

Lock modes for rows:

FOR UPDATE               – Exclusive row lock (updates)
FOR UPDATE NOWAIT       – Fail immediately if locked
FOR SHARE                – Shared lock (allows concurrent reads)
FOR SHARE NOWAIT        – Fail immediately if locked
FOR KEY SHARE            – Protects indexed keys

Example queries:

SELECT l.locktype, c.relname AS table_name, l.mode, l.granted,
       l.pid, a.usename, a.query, a.query_start, a.state
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.locktype = 'tuple'
   OR (l.locktype IN ('transactionid','virtualxid') AND l.pid IS NOT NULL);

-- PostgreSQL 17 adds a richer view
SELECT * FROM pg_locks_with_info WHERE locktype = 'tuple';

Deadlock example:

-- Transaction 1
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- locks row 1
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;  -- waits for row 2

-- Transaction 2 (runs concurrently)
BEGIN;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;  -- locks row 2
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- waits for row 1 → deadlock!

PostgreSQL automatically detects the cycle, aborts one victim, and logs the event. Enable detailed logging:

# postgresql.conf
log_lock_waits = on
deadlock_timeout = '1s'

Inspect logs with grep -i "deadlock" $PGDATA/log/postgresql-*.log.

Advisory Locks

Application‑level locks that are not tied to database objects. They must be explicitly acquired and released.

-- Acquire (blocks)
SELECT pg_advisory_lock(12345);
SELECT pg_advisory_lock(42, 7);

-- Try‑acquire (non‑blocking)
SELECT pg_try_advisory_lock(12345);
SELECT pg_try_advisory_lock(42, 7);

-- Release
SELECT pg_advisory_unlock(12345);
SELECT pg_advisory_unlock_all();

-- View current advisory locks
SELECT * FROM pg_locks WHERE locktype = 'advisory';

-- PostgreSQL 17 adds shared advisory locks
SELECT pg_advisory_lock_shared(12345);
SELECT pg_try_advisory_lock_shared(12345);

Typical use‑case – queue processing:

CREATE OR REPLACE FUNCTION process_task(task_id BIGINT) RETURNS BOOLEAN AS $$
DECLARE lock_acquired BOOLEAN;
BEGIN
    lock_acquired := pg_try_advisory_lock(task_id);
    IF NOT lock_acquired THEN
        RAISE NOTICE 'Task % already being processed', task_id;
        RETURN FALSE;
    END IF;
    -- business logic here
    PERFORM pg_advisory_unlock(task_id);
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

Lock Waits & Performance

Lock wait occurs when a transaction requests a lock held by another uncommitted transaction. Long waits degrade throughput.

-- Sessions currently waiting for a lock
SELECT a.datname, c.relname AS table_name, l.locktype, l.mode,
       a.pid, a.usename, a.query, a.query_start,
       EXTRACT(EPOCH FROM (now() - a.query_start))::integer AS wait_seconds
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE l.granted = false
ORDER BY wait_seconds DESC;

Configuration to bound wait time:

# postgresql.conf
lock_timeout = '2s'               -- abort after 2 seconds of waiting
statement_timeout = '60s'        -- abort statements running longer than 60 seconds
idle_in_transaction_session_timeout = '10min'  -- end idle‑in‑transaction sessions
log_lock_waits = on

Deadlock Detection & Handling

PostgreSQL builds a wait‑graph every deadlock_timeout interval. If a cycle is found, the victim is chosen based on fewest locks, youngest transaction, or smallest work done.

# postgresql.conf
deadlock_timeout = '1s'
log_lock_waits = on

Simple script to alert when deadlocks occur (bash example):

#!/bin/bash
PSQL="psql -U postgres -d postgres -t -A -F'|'"
DEADLOCKS=$(psql -U postgres -d postgres -c "SELECT deadlocks FROM pg_stat_database WHERE datname = current_database();")
if [ "$DEADLOCKS" -gt 0 ]; then
    echo "CRITICAL: $DEADLOCKS deadlock events detected"
    # integrate with monitoring/alerting here
fi

Monitoring & Diagnosis

Key system views: pg_locks – all lock information pg_locks_with_info (PostgreSQL 17) – adds session details pg_blocking_pids(pid) – returns PIDs blocking the given PID pg_stat_activity – session state and query text

Comprehensive monitoring script (bash) generates a report with overall wait statistics, sessions waiting >10 s, top lock holders, lock contention per table, deadlock counts, and idle‑in‑transaction sessions. It also raises an alert if any wait exceeds 60 seconds.

Troubleshooting Scenarios

Long Transaction Holding Locks

-- Find waiting sessions
SELECT l.pid, a.usename, a.query, a.query_start, c.relname AS table_name, l.mode
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE l.granted = false;

-- Find sessions that hold locks on the same table
SELECT l.pid, a.usename, a.query, a.query_start, l.mode
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE l.granted = true AND c.relname = 'users';

-- Examine the holding session (replace 12345 with the actual PID)
SELECT pid, usename, query, xact_start, state FROM pg_stat_activity WHERE pid = 12345;

Remedies: wait for the transaction, cancel it with SELECT pg_cancel_backend(12345);, or terminate it with SELECT pg_terminate_backend(12345);. Optimize the offending query via pg_stat_statements.

DDL Blocking

-- Detect ACCESS EXCLUSIVE lock waits
SELECT l.pid, a.usename, a.query, a.state, c.relname, l.mode, l.granted
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE l.mode = 'AccessExclusiveLock' OR (l.locktype = 'relation' AND NOT l.granted);

Solutions: use CREATE INDEX CONCURRENTLY, set a short lock_timeout before DDL, or terminate the blocking session after careful validation.

Frequent Deadlocks

# Enable detailed deadlock logging
ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();

Typical fixes: acquire locks in a consistent order, use NOWAIT to fail fast, keep transactions short, or switch to optimistic concurrency where appropriate.

Row‑Level Hot‑Spot Contention

-- Count row‑level locks per table
SELECT c.relname AS table_name, l.mode, COUNT(*) AS lock_count
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
WHERE l.locktype = 'tuple' AND c.relkind = 'r'
GROUP BY c.relname, l.mode
ORDER BY lock_count DESC;

-- Check HOT update ratio
SELECT relname, n_tup_upd, n_tup_hot_upd,
       ROUND(n_tup_hot_upd::numeric / NULLIF(n_tup_upd,0) * 100,2) AS hot_ratio
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY hot_ratio DESC;

Mitigations: avoid updating indexed columns on hot rows, tune autovacuum_vacuum_scale_factor, run manual VACUUM (VERBOSE, ANALYZE), or partition the table to isolate hot keys.

Optimization Best Practices

Application Design

Keep transactions short and contain only necessary statements.

Avoid long‑running work (network I/O, heavy computation) inside a transaction.

Prefer the default READ COMMITTED isolation; use SERIALIZABLE only when required.

Use NOWAIT or SKIP LOCKED to prevent indefinite blocking.

Avoid explicit LOCK TABLE unless absolutely necessary.

Database Configuration

# postgresql.conf – lock‑related settings
max_connections = 200
shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 64MB
maintenance_work_mem = 2GB

# Timeouts
lock_timeout = '2s'
statement_timeout = '60s'
idle_in_transaction_session_timeout = '10min'
deadlock_timeout = '1s'

# Logging
log_lock_waits = on
log_min_duration_statement = '1s'

# Autovacuum
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = '1min'
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.01

Monitoring & Alerting

#!/bin/bash
PSQL="psql -U postgres -d postgres -t -A -F'|'"
LOCK_WAIT_THRESHOLD=60   # seconds
IDLE_TX_THRESHOLD=600    # seconds

# Sessions waiting longer than the threshold
WAITING=$(psql -U postgres -d postgres -c "SELECT pid, usename, query, now()-query_start AS wait FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid WHERE l.granted = false AND now()-a.query_start > interval '${LOCK_WAIT_THRESHOLD} seconds';")
if [ -n "$WAITING" ]; then
    echo "CRITICAL: lock waits exceeding ${LOCK_WAIT_THRESHOLD}s"
    # send alert (email, webhook, etc.)
fi

# Idle‑in‑transaction sessions
IDLE=$(psql -U postgres -d postgres -c "SELECT pid, usename, now()-state_change AS idle FROM pg_stat_activity WHERE state='idle in transaction' AND now()-state_change > interval '${IDLE_TX_THRESHOLD} seconds';")
if [ -n "$IDLE" ]; then
    echo "WARNING: idle‑in‑transaction sessions exceeding ${IDLE_TX_THRESHOLD}s"
    # send alert
fi

Summary

PostgreSQL’s lock subsystem is the backbone of data consistency under concurrency. Table‑level locks protect DDL and bulk operations, row‑level locks protect individual tuple modifications, and advisory locks enable custom application‑level coordination. Effective monitoring (via pg_locks, pg_stat_activity, pg_blocking_pids) combined with sensible timeout settings ( lock_timeout, statement_timeout, idle_in_transaction_session_timeout) prevents lock‑related performance regressions. By keeping transactions short, using appropriate lock modes, tuning autovacuum, and regularly reviewing lock statistics, you can maintain high availability and predictable performance in PostgreSQL production clusters.