PostgreSQL Lock Contention Diagnosis

This skill guides investigation and resolution of lock contention issues in PostgreSQL using pgtuner-mcp tools.

When to Use This Skill

Use this skill when the user:

• Reports queries being blocked or timing out
• Sees "Lock" wait events in pg_stat_activity
• Experiences deadlocks (logged in PostgreSQL error log)
• Reports idle in transaction sessions causing problems
• Asks "why are my queries waiting?" or "what is blocking this query?"
• Sees application-level timeout errors due to lock waits
• Reports slow response times during peak concurrent usage

MCP Resources Available

• pgtuner://health/locks -- Quick lock health check (lightweight, targeted)
• pgtuner://health/connections -- Connection state overview
• pgtuner://settings/connections -- Connection and timeout configuration

Related MCP Prompt

This skill extends the lock analysis portions of the `health_check` MCP Prompt (which checks locks at a surface level).

Prerequisites

• The pgtuner-mcp server must be connected with a valid DATABASE_URI
• Access to pg_stat_activity, pg_locks system views

Agent Decision Tree

What is the user's symptom?
  |
  +--> "Queries are being blocked / timing out"
  |    --> Start with Step 1 (Active Queries) to find blockers
  |
  +--> "Deadlock errors in logs"
  |    --> Start with Step 2 (Wait Events) then Step 1 with include_idle
  |
  +--> "idle in transaction problems"
  |    --> Start with Step 1 (include_idle: true, min_duration_seconds: 60)
  |
  +--> "General lock performance concerns"
  |    --> Start with Step 3 (Health Check for locks) then Step 2
  |
  +--> "Lock waits during maintenance (VACUUM, REINDEX, etc.)"
       --> Start with Step 1 then check Step 4 (Configuration)

Lock Investigation Workflow

Step 1: Identify Active Queries and Blockers

Tool: get_active_queries
Parameters:
  include_idle: true
  include_system: false
  min_duration_seconds: 0

What to look for:

Agent reasoning for blocking chains:

The tool output includes information about blocked queries and their blockers. Build a blocking chain:

Blocker (PID: 1234, idle in transaction, started 10 min ago)
  |
  +--> Blocked (PID: 2345, waiting for RowExclusiveLock on orders)
  |
  +--> Blocked (PID: 3456, waiting for AccessShareLock on orders)

The root blocker is the session that is NOT waiting on any lock itself. This is usually the target for resolution.

IF root blocker is `idle in transaction`: The application opened a transaction but never committed/rolled back.

IF root blocker is `active` with a long-running query: The query is holding locks while executing. May need optimization.

Step 2: Analyze Wait Events

Tool: analyze_wait_events
Parameters:
  active_only: false

Note: Using active_only: false to include idle-in-transaction sessions in the wait event analysis.

Lock-related wait events:

IF many `Lock:tuple` waits: Multiple transactions are competing for the same rows. Recommend:

• Shorter transactions
• Access rows in consistent order to avoid deadlocks
• Consider SELECT ... FOR UPDATE SKIP LOCKED for queue-like patterns

IF many `Lock:relation` waits: A DDL or maintenance operation is blocking normal queries:

• VACUUM FULL takes AccessExclusiveLock
• ALTER TABLE takes AccessExclusiveLock
• CREATE INDEX (without CONCURRENTLY) takes ShareLock

Step 3: Targeted Lock Health Check

For a quick initial assessment, read the MCP resource first:

Read resource: pgtuner://health/locks

For a deeper check:

Tool: check_database_health
Parameters:
  include_recommendations: true
  verbose: true

Focus on the "locks" dimension score:

• Score >= 90: Lock contention is minimal
• Score 70-89: Some contention, investigate
• Score < 70: Significant contention, urgent

Step 4: Review Lock-Related Configuration

Tool: review_settings
Parameters:
  category: "connections"
  include_all_settings: false

Key lock-related settings:

Step 5: Correlate with Query Patterns

If specific queries are repeatedly involved in lock contention:

Tool: get_slow_queries
Parameters:
  limit: 20
  min_calls: 5
  order_by: "mean_time"

Look for UPDATE/DELETE queries with high mean time -- they may be holding locks longer than necessary.

Then analyze the lock-holding query:

Tool: analyze_query
Parameters:
  query: "<the lock-holding query>"
  analyze: true
  buffers: true
  verbose: false

IF the query is slow due to missing indexes, adding an index will reduce lock hold time (because the query completes faster).

Common Lock Contention Patterns and Solutions

Pattern 1: Long-Running Transaction Blocks Everything

Symptoms: Multiple sessions waiting on Lock:transactionid. One session in idle in transaction state for minutes.

Root cause: Application opens transaction, does work outside the DB (API call, file processing), then comes back to commit.

Solution:

-- Set idle_in_transaction timeout
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

-- To immediately terminate the idle session (use with caution):
SELECT pg_terminate_backend(<blocker_pid>);

Pattern 2: Deadlocks Between Concurrent Updates

Symptoms: deadlock detected errors in PostgreSQL logs.

Root cause: Two transactions update the same rows in different order:

• Transaction A: UPDATE row 1, then UPDATE row 2
• Transaction B: UPDATE row 2, then UPDATE row 1

Solution:

• Ensure transactions always access rows in a consistent order (e.g., by primary key)
• Use SELECT ... FOR UPDATE to acquire locks upfront in a deterministic order
• Keep transactions as short as possible

Pattern 3: DDL Blocking DML

Symptoms: ALTER TABLE or CREATE INDEX blocks all queries on the table.

Solution:

-- Use CONCURRENTLY for index operations
CREATE INDEX CONCURRENTLY idx_name ON table_name (column);
REINDEX INDEX CONCURRENTLY idx_name;

-- For ALTER TABLE, consider:
SET lock_timeout = '5s';  -- Fail fast if lock not acquired
ALTER TABLE ...;           -- Retry during low-traffic period

Pattern 4: VACUUM FULL Blocking Queries

Symptoms: VACUUM FULL holds AccessExclusiveLock, blocking all access.

Solution:

• Use regular VACUUM instead (only takes ShareUpdateExclusiveLock, does not block reads/writes)
• Use pg_repack for online table rewriting without exclusive locks
• Schedule VACUUM FULL only during maintenance windows

Pattern 5: Lock Queue Amplification

Symptoms: One DDL statement queued behind a long-running query, then all subsequent queries queue behind the DDL.

Explanation: PostgreSQL lock queue is FIFO. If an ALTER TABLE (needs AccessExclusiveLock) waits for a long SELECT, all new queries also wait behind the ALTER.

Solution:

-- Set a lock_timeout before DDL
SET lock_timeout = '3s';
ALTER TABLE ...;
-- If it fails, retry later. Don't let the DDL queue block everyone.

Output Format

Lock Contention Report

Current Blocking Chains:

Root Blocker: PID 1234 | State: idle in transaction | Duration: 12 min
  Query: BEGIN; UPDATE orders SET ... WHERE id = 42;
  |
  +--> Blocked: PID 2345 | Waiting: 3 min | Lock: RowExclusiveLock on orders
  |    Query: UPDATE orders SET status = 'shipped' WHERE id = 42;
  |
  +--> Blocked: PID 3456 | Waiting: 2 min | Lock: AccessShareLock on orders
       Query: SELECT * FROM orders WHERE id = 42;

Wait Event Distribution:

Recommendations:

1. Immediate: Terminate PID 1234 (idle in transaction for 12 min)
2. Configuration: Set idle_in_transaction_session_timeout = '60s'
3. Application: Fix transaction handling in the orders update flow

Iterative Verification

After resolving lock contention:

1. Re-run get_active_queries with include_idle: true to confirm no more blocking chains
2. Re-run analyze_wait_events to confirm Lock wait events have decreased
3. Monitor over the next hour to ensure the pattern doesn't recur

When to Stop and Ask the User

• Before terminating a backend: Always ask: "PID XXXX is blocking N other sessions and has been idle in transaction for X minutes. Should I provide the command to terminate it?"
• If the blocker is a known batch job: Ask: "The blocking session appears to be a batch operation. Is this expected? Should we wait for it to complete?"
• If lock contention is application-level: "The lock contention is caused by application transaction patterns. This requires application code changes, not database tuning. Would you like me to describe the recommended changes?"

PostgreSQL Version Notes

Production Safety

• All diagnostic tools used here are read-only
• pg_cancel_backend() attempts a graceful cancel (query stops, transaction stays open)
• pg_terminate_backend() forcefully terminates the session (use as last resort)
• Never terminate the autovacuum launcher unless absolutely necessary
• Setting lock_timeout before DDL is a safe practice to prevent queue amplification