Optimizing PostgreSQL chevron_right Diagnosing Locking and Contention with pg_locks and pg_stat_activity

Diagnosing Locking and Contention with pg_locks and pg_stat_activity

calendar_today schedule 6 min read

Learning Objectives: By the end of this lesson, you will be able to:

  • Explain how PostgreSQL locking works and why contention occurs
  • Use pg_locks and pg_stat_activity to identify blocked and blocking queries
  • Simulate and resolve locking scenarios using practical tools and techniques

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Have you ever run a query that seemed to “hang” for no obvious reason? What do you think happens when two transactions want to update the same row at the same time?

Lesson Overview

  • Duration: 90 minutes
  • Prerequisites: Basic SQL and PostgreSQL usage, understanding of transactions
  • Materials Needed: PostgreSQL instance (preferably a test/dev environment), psql or GUI client

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

  1. How PostgreSQL locking works and why contention happens
  2. Exploring lock types and lock modes
  3. Using pg_locks and pg_stat_activity to diagnose issues
  4. Simulating and resolving lock contention
  5. Best practices and troubleshooting

Success Criteria:

By the end of this session, you should be able to confidently identify and resolve locking issues in PostgreSQL using system views and practical techniques.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

What is Locking and Contention?

Locking is how PostgreSQL ensures data consistency when multiple transactions access the same data. Contention occurs when transactions wait for each other, causing slowdowns or timeouts.

Real-World Analogy: Imagine two people trying to edit the same document at once—one has to wait for the other to finish to avoid conflicts.

Why This Matters: Lock contention can cause slow queries, application timeouts, and even deadlocks if not managed properly.

Lock Types and Modes

  • Row-level locks: Acquired when updating or deleting specific rows
  • Table-level locks: Acquired for operations like ALTER TABLE
  • Lock modes: AccessShare, RowShare, RowExclusive, ShareUpdateExclusive, Share, Exclusive, AccessExclusive

Key Terminology

  • Blocking: When a transaction holds a lock needed by another
  • Blocked: When a transaction is waiting for a lock
  • Deadlock: When two or more transactions are waiting for each other, causing a standstill
  • lock_timeout: Parameter to limit how long a query waits for a lock

4. Detailed Explanation and Examples (25-30 minutes)

Understanding Locking in PostgreSQL

  • PostgreSQL uses Multi-Version Concurrency Control (MVCC) to minimize locking, but some locks are unavoidable
  • Locks are held until a transaction commits or rolls back
  • Long-running transactions can block others, causing contention

Practical Applications

  • Diagnosing slow queries due to locks
  • Preventing application timeouts
  • Resolving deadlocks and improving concurrency

Common Misconceptions

  1. Misconception: “SELECT queries never block.” Reality: SELECTs can block if they request explicit locks or are blocked by DDL operations.
  2. Misconception: “VACUUM never blocks.” Reality: VACUUM can block or be blocked by other operations, especially VACUUM FULL.

5. Live Coding Demonstration (20-25 minutes)

Example 1: Simulating a Locking Scenario

-- Session 1
BEGIN;
UPDATE demo_table SET value = 'A' WHERE id = 1;
-- Do not commit yet

-- Session 2 (in another terminal)
BEGIN;
UPDATE demo_table SET value = 'B' WHERE id = 1;
-- This will block until Session 1 commits or rolls back

Explanation:

  • Session 2 waits for Session 1 to release the lock on the row

Example 2: Diagnosing Locks with pg_locks and pg_stat_activity

-- See all current locks
SELECT pid, relation::regclass, mode, granted FROM pg_locks WHERE NOT granted IS FALSE;

-- See blocked and blocking queries
SELECT a.pid AS blocked_pid, a.query AS blocked_query, b.pid AS blocking_pid, b.query AS blocking_query
FROM pg_stat_activity a
JOIN pg_locks la ON la.pid = a.pid AND NOT la.granted
JOIN pg_locks lb ON lb.locktype = la.locktype AND lb.database IS NOT DISTINCT FROM la.database AND lb.relation IS NOT DISTINCT FROM la.relation AND lb.page IS NOT DISTINCT FROM la.page AND lb.tuple IS NOT DISTINCT FROM la.tuple AND lb.transactionid IS NOT DISTINCT FROM la.transactionid AND lb.classid IS NOT DISTINCT FROM la.classid AND lb.objid IS NOT DISTINCT FROM la.objid AND lb.objsubid IS NOT DISTINCT FROM la.objsubid AND lb.pid != la.pid
JOIN pg_stat_activity b ON b.pid = lb.pid
;

Key Points:

  • pg_locks shows all locks and their status
  • The join query above shows which queries are blocking others

Example 3: Using lock_timeout

SET lock_timeout = '2s';
-- Now, queries will fail with an error if they wait more than 2 seconds for a lock

Debugging Demonstration

Show a common mistake:

-- Forgetting to commit or rollback a transaction
BEGIN;
UPDATE demo_table SET value = 'A' WHERE id = 1;
-- Walk away, leaving the transaction open
-- Other queries will block indefinitely!

-- Corrected version
COMMIT;

6. Hands-On Practice (30-40 minutes)

Exercise 1: Basic Practice (10 minutes)

Objective: Simulate and observe a blocking scenario

Instructions:

  1. Open two sessions
  2. In Session 1, begin a transaction and update a row
  3. In Session 2, try to update the same row
  4. Use pg_stat_activity and pg_locks to observe the blocking

Starter Code:

-- Session 1
BEGIN;
UPDATE demo_table SET value = 'A' WHERE id = 1;

-- Session 2
BEGIN;
UPDATE demo_table SET value = 'B' WHERE id = 1;

-- In a third session
SELECT * FROM pg_stat_activity WHERE state = 'active';
SELECT * FROM pg_locks WHERE NOT granted IS FALSE;

Solution:

-- Session 2 will be blocked until Session 1 commits or rolls back

Exercise 2: Intermediate Challenge (15 minutes)

Objective: Find and resolve a blocking query

Requirements:

  • Identify the blocking and blocked queries
  • Cancel the blocking query (using pg_terminate_backend)
  • Verify that the blocked query proceeds

Hints:

  • Use the join query above to find blocking PIDs
  • Use SELECT pg_terminate_backend(<pid>); to cancel

Exercise 3: Creative Application (15 minutes)

Objective: Tune lock_timeout for your workload

Challenge:

  1. Set lock_timeout to a low value
  2. Simulate contention
  3. Observe how queries fail fast instead of hanging

Extension Ideas:

  • Research deadlock_timeout and its effect
  • Experiment with different lock modes (e.g., ACCESS EXCLUSIVE)

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

  1. Problem: Leaving transactions open Cause: Forgetting to commit/rollback Solution: Always close transactions promptly

  2. Problem: Not monitoring for blocked queries Cause: Lack of visibility Solution: Regularly check pg_stat_activity and set up alerts

Debugging Tips

  • Use pg_stat_activity to see all running and waiting queries
  • Use pg_locks to see lock details
  • Set lock_timeout to prevent indefinite waits

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

  • Keep transactions short and commit/rollback quickly
  • Monitor for blocking and deadlocks in production
  • Use lock_timeout and deadlock_timeout wisely

Next Steps in Your Learning Journey

  • Learn about deadlock detection and resolution
  • Explore advanced lock types and advisory locks
  • Set up monitoring and alerting for lock contention

9. Summary and Review (5 minutes)

Key Takeaways

  1. Locking is essential for consistency but can cause contention
  2. Use system views to diagnose and resolve blocking
  3. Keep transactions short and monitor for issues

Quick Review Questions

  1. What is the difference between blocking and blocked queries?
  2. How can you find which query is blocking another?
  3. What does lock_timeout do?

For Instructors

Teaching Tips

  • Use live demos with multiple sessions to show blocking
  • Encourage students to experiment in a safe environment
  • Discuss the impact of long transactions on concurrency

Assessment Ideas

  • Have students simulate and resolve a blocking scenario
  • Quiz on lock types and system views
  • Peer review of lock_timeout tuning proposals

Adaptation Notes

  • For beginners: Focus on row-level locks and basic blocking
  • For advanced: Explore deadlocks, lock escalation, and monitoring
  • Online: Use cloud PostgreSQL or Docker for safe experimentation