07. Locking Mechanics & Concurrency Hazards¶

PostgreSQL manages concurrency using a three-tier lock hierarchy: CPU spinlocks for nanosecond pointer updates, Lightweight Locks (LWLock) for shared-memory structures, and Heavyweight Locks (LOCK) for user-level table and row synchronization.

1. The Three-Tier Lock Architecture¶

1. Spinlocks (Hardware atomic instructions: CAS / TAS)
   └── Duration: Microseconds. Used to protect primitive counters. Non-interruptible.

2. Lightweight Locks (LWLocks)
   └── Duration: Duration of an operation (e.g. reading an 8KB buffer).
   └── Modes: LW_SHARED (multiple readers), LW_EXCLUSIVE (single writer).
   └── Protects: Shared Buffers, WAL buffers, lock tables.

3. Heavyweight Locks (Regular Locks)
   └── Duration: Held until transaction COMMIT or ROLLBACK.
   └── Managed by: Lock Manager hash tables in shared memory.
   └── Participates in: Deadlock detection.

Fast-Path Locks¶

Checking the shared-memory lock table requires acquiring an LWLock on the lock hash bucket. To avoid this under high read volume, PostgreSQL allocates a Fast-Path Lock array (16 slots) per backend. When acquiring a weak lock (such as ACCESS SHARE), the backend registers it in its local fast-path array without touching shared memory!

2. Table-Level Lock Modes & Conflict Matrix¶

PostgreSQL defines eight distinct table lock modes arranged in increasing strength:

Lock Mode	SQL Examples	Conflicts With
ACCESS SHARE	`SELECT`	`ACCESS EXCLUSIVE`
ROW SHARE	`SELECT ... FOR UPDATE`	`EXCLUSIVE`, `ACCESS EXCLUSIVE`
ROW EXCLUSIVE	`INSERT`, `UPDATE`, `DELETE`	`SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`
SHARE UPDATE EXCLUSIVE	`VACUUM (standard)`, `ANALYZE`, `CREATE INDEX CONCURRENTLY`	`SHARE UPDATE EXCLUSIVE` through `ACCESS EXCLUSIVE`
SHARE	`CREATE INDEX (standard)`	`ROW EXCLUSIVE` through `ACCESS EXCLUSIVE`
SHARE ROW EXCLUSIVE	Rare / custom triggers	`ROW EXCLUSIVE` through `ACCESS EXCLUSIVE`
EXCLUSIVE	`REFRESH MATERIALIZED VIEW`	`ROW SHARE` through `ACCESS EXCLUSIVE`
ACCESS EXCLUSIVE	`ALTER TABLE`, `DROP TABLE`, `TRUNCATE`, `VACUUM FULL`	ALL LOCK MODES (including `SELECT`)

3. The Head-of-Line (HoL) Blocking Hazard¶

A common production outage occurs when a migration runs a DDL command such as ALTER TABLE users ADD COLUMN age int;:

sequenceDiagram
    participant Q1 as Long Read (SELECT)
    participant DDL as Migration (ALTER TABLE)
    participant Q2 as New Reads (Incoming Web Traffic)

    Note over Q1: Transaction holds ACCESS SHARE
    DDL->>DDL: Requests ACCESS EXCLUSIVE
    Note over DDL: Blocked behind Q1. Enters Lock Queue!
    Q2->>Q2: Requests ACCESS SHARE
    Note over Q2: BLOCKED! Because DDL is ahead in queue!
    Note over Q2: Entire API connection pool exhausts!

Because ACCESS EXCLUSIVE conflicts with ACCESS SHARE, the migration waits for the long-running read $Q_1$ to finish.
The Lock Queue Priority Rule: To prevent starvation of write transactions, all incoming queries $Q_2$ queue behind the migration request.
Result: Even lightweight SELECT queries are blocked, causing connection pool exhaustion and application downtime!

Prevention: Aggressive Lock Timeouts¶

Always set lock_timeout before executing any DDL:

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE users ADD COLUMN age integer;
COMMIT;
-- If the lock cannot be acquired within 2 seconds, the transaction aborts instantly
-- rather than queueing and taking down the API!

4. Row-Level Locks & MultiXactIds¶

Unlike table locks, row locks are not stored in shared memory (which would exhaust memory with millions of locks). Instead, row locks are recorded directly in the tuple header (t_xmax):

Exclusive Row Lock (FOR UPDATE, FOR NO KEY UPDATE): The locking backend writes its own XID into the row's t_xmax and sets HEAP_XMAX_EXCL_LOCK in t_infomask.
Shared Row Lock (FOR SHARE, FOR KEY SHARE): If multiple transactions lock the same row simultaneously, PostgreSQL allocates a MultiXactId in $PGDATA/pg_multixact/ and stores the MultiXact ID in t_xmax.

5. Deadlock Detection Engine¶

PostgreSQL runs a background deadlock detector when a transaction waits longer than deadlock_timeout (default 1000ms): 1. Builds a directed Wait-For Graph of blocked transactions. 2. Runs a cycle detection algorithm (Tarjan's strongly connected components). 3. If a cycle is found, aborts the youngest transaction in the cycle with error 40P01 (deadlock_detected).

6. High-Throughput Queue Pattern: `SKIP LOCKED`¶

Building background task queues in PostgreSQL can cause catastrophic row lock serialization if workers block on the same rows.

Using FOR UPDATE SKIP LOCKED allows backends to skip over locked rows without waiting:

-- Atomically claim the top 5 available jobs without lock contention:
WITH claimed_jobs AS (
    SELECT id 
    FROM job_queue
    WHERE status = 'pending'
    ORDER BY priority DESC, created_at ASC
    LIMIT 5
    FOR UPDATE SKIP LOCKED
)
UPDATE job_queue q
SET status = 'processing',
    locked_at = NOW(),
    worker_id = 'worker-node-01'
FROM claimed_jobs c
WHERE q.id = c.id
RETURNING q.*;

7. Production Diagnostics: Root Blocker Detection¶

-- Identify the root blocker PID and statement stalling the database:
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.query_start AS blocked_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.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    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;