Understanding PostgreSQL Locks: A Comprehensive Guide

PostgreSQL is a powerful database system that uses sophisticated locking mechanisms to maintain data integrity while allowing concurrent access. Understanding how these locks work is essential for database administrators and application developers who want to build high-performance, scalable systems. This comprehensive guide explores PostgreSQL's locking system with practical examples and techniques you can use in production.

Introduction to Locking in PostgreSQL

Locks in PostgreSQL are fundamental for concurrency control—they prevent multiple sessions from making conflicting changes to the same data. While we typically think of locks in terms of shared locks (reader locks) and exclusive locks (writer locks), PostgreSQL implements a more nuanced approach with various lock modes at different levels.

Understanding these mechanisms helps in:

• Diagnosing performance bottlenecks and slowdowns
• Preventing and resolving deadlocks
• Designing applications that scale under high concurrency
• Ensuring data integrity without sacrificing performance

Lock Levels in PostgreSQL

PostgreSQL implements locking at four primary levels:

1. Table-level locks - Affect entire tables
2. Row-level locks - Target specific rows
3. Page-level locks - Operate on individual data pages (8KB blocks)
4. Advisory locks - Application-defined locks that don't correspond to any database object

Setting Up for Lock Observation

First, let's create a test environment and helper views to observe lock behavior:

-- Create test database
CREATE DATABASE lock_test;
\c lock_test

-- Create test table
CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    account_number VARCHAR(20) UNIQUE,
    balance NUMERIC(15,2) NOT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO accounts (account_number, balance)
VALUES
('ACC-1001', 5000.00),
('ACC-1002', 7500.00),
('ACC-1003', 12000.00),
('ACC-1004', 3200.00),
('ACC-1005', 9800.00);

Creating a Lock Monitoring View

PostgreSQL provides pg_locks and pg_stat_activity system views. Let's create a helper view for easier monitoring:

CREATE OR REPLACE VIEW lock_monitor AS
SELECT
    l.locktype,
    l.relation::regclass AS table_name,
    l.page,
    l.tuple,
    l.virtualxid,
    l.transactionid::text,
    l.mode,
    l.granted,
    a.application_name,
    a.query,
    a.pid,
    a.usename,
    a.state,
    a.xact_start
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.pid <> pg_backend_pid()
ORDER BY l.pid, l.relation;

Table-Level Locks

Table-level locks affect an entire table and come in eight different modes, each with different behaviors and conflict patterns.

1. ACCESS SHARE Lock

This is the least restrictive lock, acquired by SELECT operations. It only conflicts with ACCESS EXCLUSIVE locks.

-- Terminal 1
BEGIN;
SELECT * FROM accounts;
-- Keep transaction open

-- Terminal 2 - Check locks
SELECT * FROM lock_monitor WHERE table_name = 'accounts';

2. ROW SHARE Lock

Acquired by SELECT FOR UPDATE and SELECT FOR SHARE:

-- Terminal 1
BEGIN;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;

-- Terminal 2
SELECT * FROM lock_monitor WHERE table_name = 'accounts';

3. ROW EXCLUSIVE Lock

Acquired by UPDATE, INSERT, and DELETE operations:

-- Terminal 1
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- Terminal 2
SELECT * FROM lock_monitor WHERE table_name = 'accounts';

4. SHARE UPDATE EXCLUSIVE Lock

Acquired by VACUUM, ANALYZE, and CREATE INDEX CONCURRENTLY:

BEGIN;
ANALYZE accounts;
-- Check locks in another session

5. SHARE Lock

Acquired by CREATE INDEX (non-concurrent):

BEGIN;
CREATE INDEX test_index ON accounts(balance);

6. SHARE ROW EXCLUSIVE Lock

Acquired by CREATE TRIGGER and some forms of ALTER TABLE.

7. EXCLUSIVE Lock

Acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY.

8. ACCESS EXCLUSIVE Lock

The most restrictive lock, acquired by DROP TABLE, TRUNCATE, VACUUM FULL, REINDEX, CLUSTER:

BEGIN;
TRUNCATE accounts;
-- This blocks all operations on the table

Table-Level Lock Conflict Matrix

Row-Level Locks

Row-level locks control access to individual rows. PostgreSQL provides four row-level lock modes:

FOR UPDATE

Most restrictive, blocks any operation that would modify the locked row:

-- Terminal 1
BEGIN;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;

-- Terminal 2 - This will wait
BEGIN;
UPDATE accounts SET balance = balance + 300 WHERE account_id = 1;

FOR NO KEY UPDATE

Similar to FOR UPDATE but doesn't block FOR KEY SHARE:

-- Terminal 1
BEGIN;
SELECT * FROM accounts WHERE account_id = 2 FOR NO KEY UPDATE;

-- Terminal 2 - This should not wait
BEGIN;
SELECT * FROM accounts WHERE account_id = 2 FOR KEY SHARE;

FOR SHARE

Allows concurrent FOR SHARE but blocks modifications:

-- Terminal 1
BEGIN;
SELECT * FROM accounts WHERE account_id = 3 FOR SHARE;

-- Terminal 2 - This should not wait
BEGIN;
SELECT * FROM accounts WHERE account_id = 3 FOR SHARE;

-- Terminal 3 - This will wait
BEGIN;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 3;

FOR KEY SHARE

Least restrictive, only blocks key modifications:

BEGIN;
SELECT * FROM accounts WHERE account_id = 4 FOR KEY SHARE;

Row-Level Lock Conflict Matrix

Detecting and Resolving Deadlocks

Deadlocks occur when two or more transactions are waiting for each other to release locks. PostgreSQL automatically detects and resolves deadlocks by aborting one transaction.

Creating a Deadlock Scenario

-- Terminal 1
BEGIN;
UPDATE accounts SET balance = balance + 700 WHERE account_id = 1;

-- Terminal 2
BEGIN;
UPDATE accounts SET balance = balance + 800 WHERE account_id = 2;
-- Now try to update account_id = 1 (locked by Terminal 1)
UPDATE accounts SET balance = balance + 900 WHERE account_id = 1;

-- Terminal 1 (continuing)
-- Try to update account_id = 2 (locked by Terminal 2)
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;
-- Deadlock detected! One transaction will be aborted

Monitoring Deadlocks

-- View deadlock_timeout setting
SHOW deadlock_timeout;

-- Check for blocking queries
SELECT blocked_activity.pid AS blocked_pid,
       blocked_activity.query AS blocked_query,
       blocking_activity.pid AS blocking_pid,
       blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks AS blocked_locks
JOIN pg_catalog.pg_stat_activity AS blocked_activity
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks AS blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity AS blocking_activity
    ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Advisory Locks

Advisory locks are application-defined locks that don't correspond to any database object. They're perfect for distributed coordination without external tools.

For a detailed guide on advisory locks with Go implementation examples, check out my article: A Practical Guide to Using Advisory Locks in Your Application.

Session-Level Advisory Lock

-- Terminal 1
SELECT pg_advisory_lock(1001);

-- Terminal 2 - This will wait
SELECT pg_advisory_lock(1001);

-- Terminal 1 - Release the lock
SELECT pg_advisory_unlock(1001);

Transaction-Level Advisory Lock

-- Terminal 1
BEGIN;
SELECT pg_advisory_xact_lock(2001);
-- Lock released automatically on COMMIT or ROLLBACK
COMMIT;

Practical Applications

1. Monitoring Lock Contention

CREATE OR REPLACE VIEW lock_contention AS
SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement,
       blocking_activity.query AS blocking_statement,
       NOW() - blocked_activity.xact_start AS blocked_duration,
       NOW() - blocking_activity.xact_start AS blocking_duration
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
    ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Query the view
SELECT * FROM lock_contention;

2. Finding Long-Running Transactions

SELECT pid,
       usename,
       application_name,
       state,
       query_start,
       NOW() - query_start AS duration,
       query
FROM pg_stat_activity
WHERE state != 'idle'
  AND NOW() - query_start > INTERVAL '5 minutes'
ORDER BY duration DESC;

3. Identifying Lock-Heavy Queries

SELECT l.relation::regclass AS table_name,
       l.mode,
       COUNT(*) AS lock_count,
       STRING_AGG(DISTINCT a.query, E'\n---\n') AS queries
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation IS NOT NULL
GROUP BY l.relation, l.mode
ORDER BY lock_count DESC
LIMIT 10;

Best Practices

1. Keep Transactions Short

Short transactions reduce lock contention and improve concurrency:

-- Bad: Long transaction
BEGIN;
SELECT * FROM accounts WHERE balance > 1000;
-- Wait for user input or external process
UPDATE accounts SET balance = balance * 1.05;
COMMIT;

-- Good: Quick transaction
BEGIN;
UPDATE accounts SET balance = balance * 1.05 WHERE balance > 1000;
COMMIT;

2. Use Appropriate Isolation Levels

-- For read-only operations
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- For operations needing repeatable reads
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- For full serialization
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

3. Use FOR UPDATE SKIP LOCKED for Queue Processing

BEGIN;
-- Get the first unlocked row
SELECT * FROM task_queue
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 1;

-- Process the task
UPDATE task_queue SET status = 'processing'
WHERE task_id = (
    SELECT task_id FROM task_queue
    WHERE status = 'pending'
    FOR UPDATE SKIP LOCKED
    LIMIT 1
);
COMMIT;

4. Monitor VACUUM Operations

-- Check if VACUUM is blocked
SELECT a.query, l.mode, l.granted
FROM pg_stat_activity a
JOIN pg_locks l ON a.pid = l.pid
WHERE a.query LIKE '%VACUUM%'
ORDER BY l.granted;

5. Handle Deadlocks Gracefully in Application Code

In Go, you can implement retry logic for deadlock scenarios:

func executeWithRetry(db *sql.DB, maxRetries int, fn func(*sql.Tx) error) error {
    for attempt := 0; attempt < maxRetries; attempt++ {
        tx, err := db.Begin()
        if err != nil {
            return err
        }

        err = fn(tx)
        if err != nil {
            tx.Rollback()

            // Check if it's a deadlock error
            if isDeadlockError(err) && attempt < maxRetries-1 {
                time.Sleep(time.Millisecond * time.Duration(100*(attempt+1)))
                continue
            }
            return err
        }

        if err := tx.Commit(); err != nil {
            if isDeadlockError(err) && attempt < maxRetries-1 {
                time.Sleep(time.Millisecond * time.Duration(100*(attempt+1)))
                continue
            }
            return err
        }

        return nil
    }
    return fmt.Errorf("max retries exceeded")
}

func isDeadlockError(err error) bool {
    return strings.Contains(err.Error(), "deadlock detected")
}

Performance Impact and Optimization

CREATE INDEX vs CREATE INDEX CONCURRENTLY

-- Standard CREATE INDEX (acquires SHARE lock, blocks writes)
CREATE INDEX balance_idx ON accounts(balance);

-- Concurrent index creation (acquires SHARE UPDATE EXCLUSIVE lock)
CREATE INDEX CONCURRENTLY balance_idx ON accounts(balance);

VACUUM vs VACUUM FULL

-- VACUUM (acquires SHARE UPDATE EXCLUSIVE lock)
VACUUM accounts;

-- VACUUM FULL (acquires ACCESS EXCLUSIVE lock, blocks all operations)
VACUUM FULL accounts;

Conclusion

PostgreSQL's locking mechanisms provide a sophisticated system for maintaining data consistency while maximizing concurrency. Key takeaways:

• Understand the different lock levels and their conflict patterns
• Monitor lock contention regularly using system views
• Keep transactions short to minimize lock duration
• Use appropriate lock modes for your use case
• Implement deadlock handling in your application code
• Consider using advisory locks for distributed coordination
• Use SKIP LOCKED for queue-like operations
• Prefer CREATE INDEX CONCURRENTLY for production systems

By mastering PostgreSQL's locking system, you can build high-performance applications that handle concurrent operations gracefully while maintaining data integrity.

References

• PostgreSQL Official Documentation: Explicit Locking
• Everything You Need To Know About PostgreSQL Locks: Practical Skills You Need by Mohit Mishra
• A Practical Guide to Using Advisory Locks in Your Application