Comprehensive Guide to PostgreSQL Lock Mechanisms: From Basics to Advanced Practices

Background and Overview

In production environments, concurrent control is essential for data consistency and system stability. PostgreSQL’s lock mechanism directly determines performance under high concurrency.

The author, a veteran operations engineer, has witnessed numerous lock‑related incidents such as long‑running transactions, timeouts, and deadlocks, motivating a systematic exploration of PostgreSQL 17 lock internals.

Prerequisites

Linux command line basics

Basic database operation experience

Understanding of ACID transaction properties

Experimental Environment

PostgreSQL 17.3 (compiled from source)

CentOS Stream 9 or Ubuntu 24.04 LTS

Test data generated with

pgbench

1. Lock Fundamentals

1.1 Why Locks Are Needed

Without proper concurrency control, databases suffer from dirty reads, non‑repeatable reads, phantom reads, and lost updates. PostgreSQL prevents these anomalies by combining locks with Multi‑Version Concurrency Control (MVCC).

1.2 Classification of PostgreSQL Locks

Locks can be grouped by the object they protect and by their mode:

Object‑based locks:
  Table‑level locks
  Row‑level locks
  Page‑level locks
  Advisory locks
  Transaction locks

Mode‑based locks:
  Exclusive (X)
  Share (S)
  Update (U)
  …

Understanding each lock’s granularity and applicable scenarios is the foundation for troubleshooting.

2. Table‑Level Locks

2.1 Lock Modes

Table‑level locks are visible via the pg_locks catalog. Common modes include:

Lock mode   Short   Allowed concurrent access   Typical use case
---------------------------------------------------------------
ACCESS SHARE   AS   Parallel reads   SELECT
ROW SHARE       RS   Read rows   SELECT FOR UPDATE/FOR SHARE
ROW EXCLUSIVE   RE   Update rows   INSERT/UPDATE/DELETE
SHARE UPDATE EXCLUSIVE   SUE   Parallel metadata updates   VACUUM, ANALYZE, CREATE INDEX
SHARE           S    Block writes   CREATE INDEX
SHARE ROW EXCLUSIVE   SRE   Block row locks   Rarely used
EXCLUSIVE       E    Block all concurrent access   Refresh statistics
ACCESS EXCLUSIVE   AE   Serialize access   DROP/ALTER TABLE, LOCK TABLE

2.2 Compatibility Matrix

AS RS RE SUE S SRE E AE
AS    +  +  +  +  +  +  +  -
RS    +  +  +  +  +  -  -  -
RE    +  +  -  -  +  -  -  -
SUE   +  +  -  -  -  -  -  -
S     +  +  +  -  +  -  -  -
SRE   +  -  -  -  -  -  -  -
E     +  -  -  -  -  -  -  -
AE    -  -  -  -  -  -  -  -

2.3 Viewing Table‑Level Locks

SELECT
    l.locktype,
    d.relname,
    l.mode,
    l.granted,
    l.fastpath,
    l.virtualtransaction,
    l.pid,
    l.virtualxid,
    l.transactionid,
    l.classid,
    l.objid,
    l.objsubid,
    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_authid a ON a.usesysid = l.pid
WHERE l.relation IS NOT NULL
  AND d.datname = current_database()
ORDER BY l.pid, l.mode;

If the query returns no rows, it simply means no table‑level locks are currently held.

2.4 Acquiring and Releasing Table Locks

-- Automatic acquisition (e.g., SELECT acquires ACCESS SHARE)
SELECT * FROM users WHERE id = 1;

-- Explicit acquisition
LOCK TABLE users IN ACCESS EXCLUSIVE MODE;
LOCK TABLE users IN SHARE MODE;
LOCK TABLE users IN SHARE ROW EXCLUSIVE MODE;

-- Non‑blocking acquisition with timeout (PostgreSQL 17)
SET lock_timeout = '5s';
LOCK TABLE users IN ACCESS EXCLUSIVE MODE NOWAIT;

2.5 DDL Operations and Locks

DDL statements obtain an ACCESS EXCLUSIVE lock, which blocks all other activity. Before running DDL in production, verify that no long‑running transactions hold conflicting locks.

-- Example DDL that acquires ACCESS EXCLUSIVE
DROP TABLE IF EXISTS users;
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

3. Row‑Level Locks

3.1 Lock Modes

Lock mode   Short   Allowed concurrent access   Typical use case
---------------------------------------------------------------
FOR UPDATE          FX   Exclusive read‑modify‑write   SELECT FOR UPDATE
FOR UPDATE NOWAIT   FXN  Exclusive read, immediate error   SELECT … FOR UPDATE NOWAIT
FOR SHARE           FS   Shared read   SELECT FOR SHARE
FOR SHARE NOWAIT    FSN  Shared read, immediate error   SELECT … FOR SHARE NOWAIT
FOR KEY SHARE       FKS  Allow index‑key updates   SELECT … FOR KEY SHARE

3.2 Automatic Acquisition

UPDATE and DELETE statements automatically acquire a FOR UPDATE lock on the affected rows.

UPDATE users SET email = '[email protected]' WHERE id = 1;
DELETE FROM users WHERE id = 1;

3.3 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 (concurrent)
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 detects the cycle, aborts one transaction, and logs the deadlock.

# Enable deadlock logging (postgresql.conf)
log_lock_waits = on
# View deadlock logs
grep -i "deadlock" /var/lib/postgresql/17/data/log/postgresql-*.log

4. MVCC Interaction

4.1 MVCC Basics

MVCC provides each transaction with a consistent snapshot. Core concepts:

TID (Tuple ID) : physical location of a row

xmin/xmax : creating and deleting transaction IDs

Transaction snapshot : list of active transaction IDs at the start of a transaction

4.2 Visibility Rules (excerpt)

Rule 1: If a row’s xmin is in the active‑transaction list, the row is invisible.
Rule 2: If a row’s xmax is in the active‑transaction list, the row is invisible.
Rule 3: If xmin committed before the snapshot, the row is visible.
Rule 4: If xmax is NULL or not yet committed, the row is visible.
Rule 5: If the current transaction is the row’s xmax, the row is invisible (own updates are invisible).

4.3 Cooperation Between MVCC and Locks

MVCC handles read/write conflicts without blocking, while locks serialize conflicting writes, DDL, and row‑level updates. Together they guarantee isolation and consistency.

4.4 VACUUM and MVCC

Because MVCC creates multiple row versions, VACUUM reclaims space from dead tuples.

# Manual vacuum with statistics
VACUUM VERBOSE ANALYZE users;

# Identify tables with many dead tuples
SELECT schemaname, relname, n_dead_tup,
       n_live_tup,
       n_dead_tup::float / NULLIF(n_live_tup + n_dead_tup, 0) AS dead_ratio,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

5. Lock Waiting and Performance

5.1 Detecting Waiting Locks

SELECT a.datname,
       d.relname AS table_name,
       l.locktype,
       l.mode,
       l.granted,
       l.pid,
       l.virtualtransaction,
       a.query,
       a.query_start
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
LEFT JOIN pg_class d ON d.oid = l.relation
WHERE l.granted = false
ORDER BY a.query_start;

5.2 Monitoring Script (excerpt)

#!/bin/bash
# check_lock_waits.sh – basic lock‑wait monitor
PSQL="psql -U postgres -d postgres -t -A -F'|'"
# Overall waiting statistics
$PSQL -c "SELECT COUNT(*) AS total_waiting,
               COUNT(DISTINCT pid) AS waiting_sessions,
               COUNT(DISTINCT relation) AS tables_involved
        FROM pg_locks WHERE granted = false;"
# Sessions waiting >10 s
$PSQL -c "SELECT a.pid, a.usename, a.datname,
               LEFT(a.query,80) AS query_preview,
               EXTRACT(EPOCH FROM (now()-a.query_start))::int AS wait_seconds,
               l.mode,
               c.relname AS locked_table
        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
          AND EXTRACT(EPOCH FROM (now()-a.query_start)) > 10
        ORDER BY wait_seconds DESC
        LIMIT 20;"

5.3 Lock‑Wait Configuration

# Recommended timeout values (adjust per workload)
SET lock_timeout = '2s';          -- abort lock wait after 2 seconds
SET statement_timeout = '60s';    -- abort long‑running statements
SET idle_in_transaction_session_timeout = '10min';

6. Deadlock Detection and Handling

6.1 Detection Algorithm

PostgreSQL periodically builds a wait‑graph (transactions as nodes, waiting edges as edges). If a cycle exists, the system selects a victim based on:

Fewest held locks

Latest start time

Least work done

6.2 Configuration Parameters

# Default is 1 second; can be lowered in high‑contention environments
deadlock_timeout = '1s';
log_lock_waits = on;

6.3 Handling Script (excerpt)

#!/bin/bash
PSQL="psql -U postgres -d postgres -t -A -F'|'"
# Show deadlock count for the current database
DEADLOCK_COUNT=$($PSQL -c "SELECT deadlocks FROM pg_stat_database WHERE datname = current_database();")
if [ "$DEADLOCK_COUNT" -gt 0 ]; then
    echo "Warning: $DEADLOCK_COUNT deadlock events detected. Check PostgreSQL logs for details."
fi
# List potential blockers (sessions holding locks that block others)
$PSQL -c "SELECT l.pid AS blocker_pid,
               a.usename,
               a.state,
               LEFT(a.query,80) AS query_preview,
               COUNT(*) AS blocked_sessions
        FROM pg_locks l
        JOIN pg_stat_activity a ON a.pid = l.pid
        WHERE l.granted = true
          AND EXISTS (SELECT 1 FROM pg_locks l2 WHERE l2.relation = l.relation AND l2.granted = false AND l2.pid <> l.pid)
        GROUP BY l.pid, a.usename, a.state, a.query
        ORDER BY blocked_sessions DESC;"

7. Advisory Locks (Application‑Level)

7.1 Concept

Advisory locks are not tied to database objects; applications acquire and release them explicitly, enabling custom concurrency control.

7.2 Functions

-- Acquire a session‑level advisory lock (blocks)
SELECT pg_advisory_lock(12345);
SELECT pg_advisory_lock(42, 7);

-- Try to acquire without blocking (returns true/false)
SELECT pg_try_advisory_lock(12345);
SELECT pg_try_advisory_lock(42, 7);

-- Release
SELECT pg_advisory_unlock(12345);
SELECT pg_advisory_unlock(42, 7);
SELECT pg_advisory_unlock_all();

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

7.3 Usage Example – Queue Control

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 % is already being processed', task_id;
        RETURN FALSE;
    END IF;
    -- Business logic goes here
    PERFORM pg_advisory_unlock(task_id);
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

DO $$
DECLARE result BOOLEAN;
BEGIN
    result := process_task(12345);
    IF result THEN
        RAISE NOTICE 'Task succeeded';
    ELSE
        RAISE NOTICE 'Task skipped (already locked)';
    END IF;
END $$;

7.4 Semaphore Example

CREATE OR REPLACE FUNCTION acquire_semaphore(semaphore_name TEXT, max_concurrent INT)
RETURNS BOOLEAN AS $$
DECLARE
    sem_key BIGINT := hashtext(semaphore_name)::BIGINT;
    current_count INT;
BEGIN
    SELECT COUNT(*) INTO current_count
    FROM pg_locks
    WHERE locktype = 'advisory' AND objid = sem_key AND granted = true;
    IF current_count >= max_concurrent THEN
        RETURN FALSE;  -- limit reached
    END IF;
    PERFORM pg_advisory_lock(sem_key);
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION release_semaphore(semaphore_name TEXT)
RETURNS VOID AS $$
DECLARE sem_key BIGINT := hashtext(semaphore_name)::BIGINT;
BEGIN
    PERFORM pg_advisory_unlock(sem_key);
END;
$$ LANGUAGE plpgsql;

8. Lock Monitoring and Diagnosis

8.1 System Views

pg_locks

– all lock information pg_locks_with_info (PostgreSQL 17) – richer session details pg_blocking_pids(pid) – list of blocker PIDs pg_current_backend_pid() – current session PID

8.2 Comprehensive Monitoring Script (excerpt)

#!/bin/bash
REPORT="/var/log/postgresql/lock_report_$(date +%Y%m%d_%H%M).log"
PSQL="psql -U postgres -d postgres -t -A -F'|'"
# Header
echo "PostgreSQL Lock Monitoring Report" > $REPORT
echo "Generated at $(date)" >> $REPORT

# 1. Overall waiting stats
$PSQL -c "SELECT COUNT(*) AS total_waiting,
               COUNT(DISTINCT pid) AS waiting_sessions,
               COUNT(DISTINCT relation) AS tables_involved
        FROM pg_locks WHERE granted = false;" >> $REPORT

# 2. Sessions waiting >10 s
$PSQL -c "SELECT a.pid, a.usename, a.datname,
               LEFT(a.query,80) AS query_preview,
               EXTRACT(EPOCH FROM (now()-a.query_start))::int AS wait_seconds,
               l.mode,
               c.relname AS locked_table
        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
          AND EXTRACT(EPOCH FROM (now()-a.query_start)) > 10
        ORDER BY wait_seconds DESC
        LIMIT 20;" >> $REPORT

# 3. Sessions holding the most locks
$PSQL -c "SELECT a.pid, a.usename, a.state,
               COUNT(*) AS locks_held,
               SUM(CASE WHEN l.granted THEN 1 ELSE 0 END) AS granted_locks,
               SUM(CASE WHEN NOT l.granted THEN 1 ELSE 0 END) AS waiting_locks
        FROM pg_locks l
        JOIN pg_stat_activity a ON a.pid = l.pid
        GROUP BY a.pid, a.usename, a.state
        HAVING COUNT(*) > 5
        ORDER BY locks_held DESC
        LIMIT 10;" >> $REPORT

cat $REPORT

8.3 Performance Diagnosis Script (excerpt)

#!/bin/bash
PSQL="psql -U postgres -d postgres -t -A -F'|'"
# 1. Long‑running transactions
$PSQL -c "SELECT pid, usename, state,
               EXTRACT(EPOCH FROM (now()-xact_start))::int AS xact_age_sec,
               EXTRACT(EPOCH FROM (now()-query_start))::int AS query_age_sec,
               LEFT(query,100) AS query
        FROM pg_stat_activity
        WHERE state NOT IN ('idle','idle in transaction','idle in transaction (aborted)')
          AND xact_start IS NOT NULL
        ORDER BY xact_age_sec DESC
        LIMIT 10;"
# 2. Lock wait pattern analysis (waiting vs holding)
$PSQL -c "WITH waiting AS (SELECT pid, relation, mode FROM pg_locks WHERE granted = false),
               holding AS (SELECT pid, relation, mode FROM pg_locks WHERE granted = true)
        SELECT w.pid AS waiting_pid, h.pid AS holding_pid,
               c.relname AS table_name, w.mode AS waiting_mode, h.mode AS holding_mode
        FROM waiting w
        JOIN holding h ON COALESCE(w.relation,0)=COALESCE(h.relation,0)
        LEFT JOIN pg_class c ON c.oid = w.relation
        WHERE w.pid <> h.pid
        ORDER BY c.relname, w.mode;"
# 3. Estimate performance loss due to lock waits
$PSQL -c "SELECT COUNT(*) AS total_lock_waits,
               COUNT(DISTINCT pid) AS affected_sessions,
               ROUND(COUNT(*)*1.0/NULLIF(EXTRACT(EPOCH FROM (now()-MIN(a.query_start))),0),2) AS avg_waits_per_sec
        FROM pg_locks l
        JOIN pg_stat_activity a ON a.pid = l.pid
        WHERE l.granted = false;"

9. Common Lock Troubleshooting

9.1 Flowchart (textual description)

1️⃣ Detect lock wait → 2️⃣ Gather lock info (pg_locks, pg_stat_activity, pg_blocking_pids) → 3️⃣ Identify problem type (long transaction, lock contention, deadlock, DDL block) → 4️⃣ Apply mitigation (wait, terminate session, cancel/rollback transaction, refactor application).

9.2 Scenario 1 – Long Transaction

Symptoms: a query hangs for seconds/minutes waiting for a row lock.

-- 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 the holder
SELECT l.pid, a.usename, a.query, a.query_start, c.relname, 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';

Solutions: wait for the transaction to finish, pg_cancel_backend(pid) for idle transactions, or pg_terminate_backend(pid) as a last resort. Also analyse slow queries with pg_stat_statements.

9.3 Scenario 2 – DDL Block

-- Detect ACCESS EXCLUSIVE lock
SELECT l.pid, a.usename, a.query, l.mode, l.granted, c.relname
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);

-- Identify long‑running transactions that may block DDL
SELECT pid, usename, query, xact_start, state
FROM pg_stat_activity
WHERE state NOT IN ('idle','idle in transaction')
  AND xact_start IS NOT NULL
ORDER BY xact_start;

Solutions: use CREATE INDEX CONCURRENTLY, set a short lock_timeout, or terminate the blocker.

9.4 Scenario 3 – Frequent Deadlocks

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

-- View deadlock count
SELECT deadlocks FROM pg_stat_database WHERE datname = current_database();

Application‑level fixes: enforce a consistent lock acquisition order, use FOR UPDATE NOWAIT, shorten transaction duration, or switch to optimistic locking.

9.5 Scenario 4 – Row‑Level Hotspot 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(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd,0),2) AS hot_ratio
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC;

Solutions: reduce HOT updates (avoid unnecessary column changes), tune autovacuum thresholds, consider partitioning hot tables, or redesign the workload.

10. Lock Optimization Best Practices

10.1 Application Design

Keep transactions short; include only the necessary statements.

Avoid performing network I/O or heavy computation inside a transaction.

Do not hold locks inside loops; batch operations instead.

Use the appropriate isolation level (default READ COMMITTED is sufficient for most cases).

-- Good practice: short transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Bad practice: long transaction with external call
BEGIN;
SELECT * FROM remote_api();  -- blocks for a long time
-- ... other work ...
COMMIT;

10.2 Lock Usage Principles

Prefer NOWAIT or SKIP LOCKED to avoid long waits.

Use advisory locks for application‑level coordination instead of explicit table locks.

Avoid LOCK TABLE … IN ACCESS EXCLUSIVE MODE unless absolutely necessary.

-- NOWAIT example
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- If lock not available, catch exception and retry later
COMMIT;

10.3 Database Configuration

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

# Lock timeouts
lock_timeout = '2s'                # abort lock wait after 2 seconds
statement_timeout = '60s'        # abort long statements
idle_in_transaction_session_timeout = '10min'

deadlock_timeout = '1s'          # deadlock detection interval
log_lock_waits = on               # record lock waits
log_min_duration_statement = '1s'  # log slow queries

autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = '1min'
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.01

10.4 Monitoring and Alerting

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

# Detect long lock waits
WAIT_COUNT=$($PSQL -c "SELECT COUNT(*) FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid WHERE l.granted = false AND EXTRACT(EPOCH FROM (now()-a.query_start)) > $LOCK_WAIT_THRESHOLD;")
if [ "$WAIT_COUNT" -gt 0 ]; then
    echo "CRITICAL: $WAIT_COUNT sessions waiting > $LOCK_WAIT_THRESHOLD seconds"
    # Insert integration with monitoring system here
fi

# Detect idle‑in‑transaction sessions
IDLE_COUNT=$($PSQL -c "SELECT COUNT(*) FROM pg_stat_activity WHERE state='idle in transaction' AND EXTRACT(EPOCH FROM (now()-state_change)) > $IDLE_TX_THRESHOLD;")
if [ "$IDLE_COUNT" -gt 0 ]; then
    echo "WARNING: $IDLE_COUNT idle‑in‑transaction sessions > $IDLE_TX_THRESHOLD seconds"
fi

10.5 Periodic Maintenance

#!/bin/bash
PSQL="psql -U postgres -d postgres -t -A -F'|'"
LOG="/var/log/postgresql/lock_maintenance_$(date +%Y%m%d).log"

echo "=== Lock Maintenance $(date) ===" > $LOG

# Snapshot of lock statistics
$PSQL -c "SELECT locktype, mode, COUNT(*) AS cnt, COUNT(DISTINCT pid) AS sessions FROM pg_locks GROUP BY locktype, mode ORDER BY cnt DESC;" >> $LOG

# Identify tables with many dead tuples
$PSQL -c "SELECT schemaname, relname, n_dead_tup, n_live_tup,
               ROUND(100.0 * n_dead_tup / NULLIF(n_dead_tup + n_live_tup,0),2) AS dead_pct
        FROM pg_stat_user_tables
        WHERE n_dead_tup > 1000
        ORDER BY n_dead_tup DESC
        LIMIT 10;" >> $LOG

# Suggest VACUUM commands for heavily bloated tables
TABLES=$($PSQL -c "SELECT 'VACUUM VERBOSE '||schemaname||'.'||relname||';' FROM pg_stat_user_tables WHERE n_dead_tup > 10000 AND (last_vacuum IS NULL OR last_vacuum < now() - interval '7 days') LIMIT 20;")
if [ -n "$TABLES" ]; then
    echo "Suggested VACUUM commands:" >> $LOG
    echo "$TABLES" >> $LOG
fi

cat $LOG

11. Summary

11.1 Core Takeaways

PostgreSQL locks are the backbone of data consistency; table‑level locks protect DDL, row‑level locks protect concurrent row updates.

MVCC reduces read‑write conflicts, while locks serialize writes and DDL.

Advisory locks enable custom application‑level coordination.

11.2 Quick‑Reference Troubleshooting Table

Problem               Possible cause                Diagnostic SQL                                 Remedy
-----------------------------------------------------------------------------------------------
Application timeout   Long transaction holds lock   SELECT * FROM pg_stat_activity WHERE xact_start IS NOT NULL;   Cancel or wait for the transaction
DDL block             Existing long transaction    SELECT * FROM pg_locks WHERE mode='AccessExclusiveLock';   Terminate blocker or set lock_timeout
Frequent deadlocks   Inconsistent lock order      Check log_lock_waits output; examine pg_locks patterns   Reorder statements, use NOWAIT/SKIP LOCKED
Performance drop      Hot‑spot row contention    SELECT c.relname, COUNT(*) FROM pg_locks l JOIN pg_class c ON c.oid=l.relation WHERE l.locktype='tuple' GROUP BY c.relname;   Reduce hotspot, partition, tune autovacuum
Excessive lock waits  lock_timeout too low       SHOW lock_timeout;                              Increase timeout value

11.3 Recommended postgresql.conf Snippet

# === Lock Management ===
lock_timeout = '2s'                     # abort lock wait after 2 seconds
statement_timeout = '60s'             # abort long statements
idle_in_transaction_session_timeout = '10min'
deadlock_timeout = '1s'                # deadlock detection interval

# === Logging ===
log_lock_waits = on
log_min_duration_statement = '1s'

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

11.4 Further Reading

PostgreSQL official documentation – Chapter 13 “Concurrency Control”.

Source code directory src/backend/storage/lmgr/ for lock manager internals.

“PostgreSQL Database Internals” (O'Reilly) – deep dive into MVCC and locking.

11.5 Practical Advice

Reproduce lock scenarios in a staging environment before applying changes to production.

Regularly monitor pg_stat_database.deadlocks and lock‑wait metrics.

Collect and analyse lock‑wait logs to spot recurring patterns.

Build a dashboard that visualises lock counts, wait times, and blocker sessions.

By following the analysis, scripts, and best‑practice recommendations presented here, engineers can confidently diagnose, monitor, and optimise PostgreSQL lock behaviour in real‑world production systems.