Locking

Chapter 37: Locking & Concurrency Control

Managing Concurrent Access

---

37.1 Lock Types in PostgreSQL

PostgreSQL uses various lock types to manage concurrent access to data.

                    Lock Types Overview
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                      PostgreSQL Locks                                 │
    │                                                                      │
    │   Table-Level Locks:                                                 │
    │   ─────────────────                                                 │
    │   • ACCESS SHARE - SELECT, vacuum                                    │
    │   • ROW SHARE - SELECT FOR UPDATE                                    │
    │   • ROW EXCLUSIVE - INSERT, UPDATE, DELETE                          │
    │   • SHARE UPDATE EXCLUSIVE - VACUUM (non-FULL), ANALYZE           │
    │   • SHARE - CREATE INDEX                                           │
    │   • SHARE ROW EXCLUSIVE - ALTER TABLE, CREATE TRIGGER              │
    │   • EXCLUSIVE - REFRESH MATERIALIZED VIEW                          │
    │   • ACCESS EXCLUSIVE - DROP TABLE, TRUNCATE, VACUUM FULL          │
    │                                                                      │
    │   Row-Level Locks:                                                   │
    │   ─────────────────                                                 │
    │   • FOR UPDATE - Prevents other transactions from modifying        │
    │   • FOR NO KEY UPDATE - Similar but weaker                        │
    │   • FOR SHARE - Allows reads, blocks modifications                 │
    │   • FOR KEY SHARE - Weakest row lock                               │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

---

37.2 Explicit Table Locks

-- Lock a table
BEGIN;
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;
-- Do operations
COMMIT;

-- Different lock modes
LOCK TABLE my_table IN SHARE MODE;
LOCK TABLE my_table IN ROW EXCLUSIVE MODE;

-- View current locks
SELECT
    l.locktype,
    l.relation::regclass,
    l.mode,
    l.granted,
    l.pid,
    a.usename,
    a.query
FROM pg_locks l
LEFT JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation IS NOT NULL;

---

37.3 Row-Level Locks

-- SELECT FOR UPDATE (blocks other updates)
BEGIN;
SELECT * FROM accounts
WHERE account_id = 1
FOR UPDATE;

-- Update will wait until lock is released
UPDATE accounts SET balance = balance - 100
WHERE account_id = 1;

COMMIT;

-- SELECT FOR SHARE (allows reads, blocks updates)
BEGIN;
SELECT * FROM accounts WHERE account_id = 1 FOR SHARE;
-- Other transactions can read but not update
COMMIT;

-- SKIP LOCKED (skip locked rows)
SELECT * FROM orders
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 10;

-- NOWAIT (fail immediately if locked)
SELECT * FROM accounts
WHERE account_id = 1
FOR UPDATE NOWAIT;

---

37.4 Advisory Locks

Application-controlled locks that don’t relate to specific data.

-- Acquire advisory lock
SELECT pg_advisory_lock(12345);

-- Check if lock is held
SELECT pg_advisory_lock_status(12345);

-- Release lock
SELECT pg_advisory_unlock(12345);

-- Lock with key
SELECT pg_advisory_lock(1, 2);  -- Two-part key

-- Try to acquire (non-blocking)
SELECT pg_try_advisory_lock(12345);

-- Use in function
CREATE OR REPLACE FUNCTION process_batch(p_batch_id INTEGER)
RETURNS BOOLEAN AS $$
BEGIN
    -- Try to acquire lock
    IF NOT pg_try_advisory_lock(p_batch_id) THEN
        RETURN FALSE;  -- Already processing
    END IF;

    -- Do work
    -- ...

    -- Release lock
    PERFORM pg_advisory_unlock(p_batch_id);
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

---

Summary

Lock Mode	Purpose
ACCESS EXCLUSIVE	DDL, DROP
ROW EXCLUSIVE	DML operations
ROW SHARE	SELECT FOR UPDATE
SHARE	CREATE INDEX

---

Next: Chapter 38: Table Partitioning Basics