PostgreSQL Lock Wait Analysis: Diagnose Lock Contention with Built-in Logging

Quick Answer

• Conclusion: Enable PostgreSQL's built-in log_lock_waits parameter to capture lock wait events in the database log, then analyze those logs to identify problematic queries and transactions causing contention.
• First checks: Verify your PostgreSQL version is 8.3 or later, ensure you have filesystem read access to the PostgreSQL log directory, and confirm sufficient disk space for increased log volume.
• Minimal configuration: Set log_lock_waits = on and deadlock_timeout = '1000ms' (or your preferred threshold) in postgresql.conf, then reload configuration with SELECT pg_reload_conf();.
• Applicable environment: Self-hosted PostgreSQL instances where you control the configuration file and have direct access to log files. Cloud-managed databases (RDS, Cloud SQL) require alternative log export methods.

What Problem It Solves

Lock contention is a common cause of performance degradation in high-concurrency PostgreSQL systems. When multiple transactions compete for the same row, table, or other database object, some sessions must wait. Without logging, you have no historical record of these waits—you can only see current locks via pg_locks or pg_stat_activity.

Enabling log_lock_waits transforms PostgreSQL's log into a diagnostic tool for lock-related performance issues. Every time a session waits longer than deadlock_timeout to acquire a lock, the database writes a log entry containing:

• The waiting process ID
• The lock type (ShareLock, ExclusiveLock, etc.)
• The blocked transaction ID
• The wait duration in milliseconds

This data lets you reconstruct lock contention patterns after the fact, identify problematic queries, and correlate waits with application behavior.

When This Error or Setup Appears

You should consider enabling lock wait logging when you observe any of these symptoms:

• Unexplained query slowdowns: Queries that normally complete in milliseconds suddenly take seconds, especially under load.
• Application timeouts: Users report "connection timeout" or "query timeout" errors during peak hours.
• Increasing connection pool usage: More database connections remain active than expected, suggesting sessions are stuck waiting.
• Deadlock errors: PostgreSQL already logs deadlocks automatically, but log_lock_waits captures the near-deadlock situations that degrade performance without causing a deadlock error.

Typical environments that benefit most:

• OLTP systems with many concurrent writes to the same tables
• Applications using long-running transactions or explicit SELECT ... FOR UPDATE
• Systems with complex foreign key relationships that trigger implicit locks
• Any PostgreSQL deployment where performance troubleshooting is a regular activity

Root Cause Analysis

Lock waits occur when two or more sessions need conflicting access to the same database resource. PostgreSQL uses several lock modes, but the most common contention scenarios involve:

1. Row-level locks: SELECT ... FOR UPDATE, UPDATE, and DELETE acquire row-level exclusive locks. Two sessions trying to modify the same row will cause one to wait.

2. Table-level locks: ALTER TABLE, VACUUM FULL, and explicit LOCK TABLE statements can block concurrent DML operations.

3. Foreign key locks: When you modify a referenced row, PostgreSQL may lock the referencing table to maintain referential integrity.

4. Advisory locks: Application-level locks acquired via pg_advisory_lock() can cause waits if not managed carefully.

The log_lock_waits parameter captures all of these. Each log entry tells you:

• Which process is waiting
• What lock type it needs
• Which transaction holds the conflicting lock
• How long it has been waiting

With this information, you can trace the waiting process back to its SQL query (using log_statement or application logs) and identify the blocking transaction.

Minimal Working Configuration

Enable lock wait logging with these two parameters. The configuration is entirely server-side—no extensions or external tools required.

Step 1: Edit postgresql.conf

INI
# Enable lock wait logging
log_lock_waits = on

# Set the threshold for logging (milliseconds)
deadlock_timeout = 1000

The deadlock_timeout value determines the minimum wait duration that triggers a log entry. A shorter value (e.g., 500ms) captures more events but generates more log volume. A longer value (e.g., 5s) reduces noise but may miss short but frequent waits.

Step 2: Reload Configuration

SQL
-- Apply changes without restarting the server
SELECT pg_reload_conf();

Verify the change took effect:

SQL
SHOW log_lock_waits;
SHOW deadlock_timeout;

Step 3: Monitor Log Output

After enabling, check your PostgreSQL log for entries like:

LOG:  process 1234 still waiting for ShareLock on transaction 567 after 1000.123 ms
DETAIL:  Process holding the lock: 1230. Wait queue: 1234, 1235.

Step 4: Analyze with Your Own Tools

The log format is consistent and parseable. You can:

• Use grep and awk to extract wait events from log files
• Feed logs into log analysis tools like pgBadger
• Write a simple script to aggregate wait counts by query or table
• Integrate with monitoring systems (Prometheus, Grafana) via log exporters

Parameters and Environment Variables

Important Notes

• deadlock_timeout serves dual purpose: it controls both deadlock detection frequency and lock wait logging threshold. Setting it very low (e.g., 100ms) increases deadlock detection overhead in addition to log volume.
• These parameters are dynamic—you can change them with ALTER SYSTEM and pg_reload_conf() without a server restart.
• Only superusers or users with ALTER SYSTEM privilege can modify these settings.

Common Errors and Fixes

Error: log_lock_waits parameter not found

Cause: PostgreSQL version older than 8.3, or the parameter name is misspelled.

Fix: Verify your PostgreSQL version with SELECT version();. If version ≥ 8.3, check for typos in postgresql.conf. Use SHOW log_lock_waits; to confirm the parameter is recognized.

Error: Permission denied reading PostgreSQL log file

Cause: The user running your analysis tool lacks read access to the log directory (typically /var/log/postgresql/ or /var/lib/postgresql/*/log/).

Fix:

• Add your user to the postgres group: sudo usermod -aG postgres your_user
• Or change log directory permissions: sudo chmod 755 /var/log/postgresql
• Or configure log_directory in postgresql.conf to a world-readable path

Error: Excessive log output after enabling log_lock_waits

Cause: deadlock_timeout is set too low for your workload, causing every brief lock wait to generate a log entry.

Fix:

• Increase deadlock_timeout (try 2000ms or 5000ms)
• Configure log rotation: set log_rotation_age = 1d and log_rotation_size = 100MB
• Use log_min_duration_statement to filter out short queries from logs
• Monitor disk space and set up log cleanup cron jobs

Error: Connection timeout when connecting to PostgreSQL

Cause: Network issues, firewall rules, or incorrect authentication configuration.

Fix:

• Verify network connectivity: ping <db-host>
• Check pg_hba.conf allows connections from your IP
• Ensure the user exists and has correct password
• Test with psql before using automated tools

FAQ

Q: How do I enable log_lock_waits without restarting the database?

A: Use ALTER SYSTEM commands as a superuser:

SQL
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1000ms';
SELECT pg_reload_conf();

This writes the settings to postgresql.auto.conf and applies them immediately. No restart needed.

Q: What does the lock wait log entry look like, and how do I parse it?

A: A typical log entry:

2025-01-15 10:23:45.123 UTC [1234] LOG:  process 1234 still waiting for ShareLock on transaction 567 after 1000.123 ms
2025-01-15 10:23:45.123 UTC [1234] DETAIL:  Process holding the lock: 1230. Wait queue: 1234, 1235.

Key fields to extract:

• Process ID: 1234 (the waiting session)
• Lock type: ShareLock
• Blocked transaction: 567
• Wait duration: 1000.123 ms
• Blocking process: 1230

A simple grep command to count waits by lock type:

BASH
grep "still waiting for" /var/log/postgresql/postgresql.log | grep -oP '\w+Lock' | sort | uniq -c

Q: Can I use this on cloud-managed PostgreSQL (AWS RDS, Google Cloud SQL)?

A: Yes, but with limitations. Cloud databases typically don't provide direct filesystem access to log files. Solutions:

1. AWS RDS: Enable log export to CloudWatch Logs, then download logs via AWS CLI or SDK. The log format is the same, so parsing works identically.
2. Google Cloud SQL: Use the gcloud logging command to read database logs.
3. Alternative approach: Query pg_stat_activity and pg_locks in real-time for current lock information, though you lose historical data.

For self-hosted PostgreSQL, this approach is simpler and more reliable.