The Lock Manager: How PostgreSQL Coordinates Concurrent Access

The one thing to understand first

MVCC removes the need to lock for ordinary reads, but PostgreSQL still needs locks to coordinate DDL, conflicting writes, and access to shared memory structures. There are three distinct mechanisms, each for a different purpose:

• Heavyweight locks (lock manager, lock.c) — table and row-level locks visible in pg_locks.
• Lightweight locks (LWLocks) — short-duration locks protecting in-memory structures like buffer headers and the WAL.
• Spinlocks — the lowest level, a few instructions guarding an LWLock's own state.

Readers and writers do not block each other — but DDL blocks everyone. The whole art of safe operations comes from one fact in the conflict table: ACCESS EXCLUSIVE (taken by most ALTER TABLE) collides with even a plain SELECT, so a careless schema change can freeze the entire application.

Heavyweight lock modes and the conflict table

Table-level locks come in eight modes, from ACCESS SHARE (taken by SELECT) to ACCESS EXCLUSIVE (taken by DROP, most ALTER TABLE, VACUUM FULL). The rules for which modes can coexist live in a static conflict table in src/backend/storage/lmgr/lock.c. The key facts:

• ACCESS SHARE (read) conflicts only with ACCESS EXCLUSIVE. So readers and most writers coexist.
• ROW EXCLUSIVE (taken by INSERT/UPDATE/DELETE) lets concurrent writers proceed — row conflicts are handled separately.
• ACCESS EXCLUSIVE conflicts with everything, including plain SELECT — which is why a careless ALTER TABLE can freeze an entire application.

-- See current heavyweight locks and what they block
SELECT l.locktype, l.mode, l.granted,
       c.relname, a.state, a.query
FROM   pg_locks l
LEFT   JOIN pg_class c ON c.oid = l.relation
LEFT   JOIN pg_stat_activity a ON a.pid = l.pid
ORDER  BY l.granted, c.relname;

Row-level locks live in the tuple, not the lock table

PostgreSQL could not store a separate lock object for every locked row — millions of locks would exhaust memory. Instead, a row lock is recorded in the tuple itself: the locking transaction writes its XID into the tuple's t_xmax with infomask flags indicating a lock rather than a delete. To wait on a locked row, a transaction waits on the holder's XID using the heavyweight lock manager. This is why SELECT ... FOR UPDATE on many rows is cheap on memory but still serialises writers per row.

Multixacts: many lockers, one row

When several transactions lock the same row in shared mode (FOR SHARE) or a mix of share/update, a single xmax cannot name them all. PostgreSQL allocates a MultiXactId — an entry in pg_multixact listing all the member transactions — and stores that in t_xmax. Multixacts have their own wraparound horizon and their own vacuuming needs; runaway multixact usage is a real (if rare) operational hazard.

Deadlock detection

PostgreSQL does not prevent deadlocks; it detects them. When a backend has waited for a lock longer than deadlock_timeout (default 1s), it runs DeadLockCheck() (src/backend/storage/lmgr/deadlock.c), which builds a wait-for graph among waiting processes and searches for a cycle. If it finds one, the cheapest victim is chosen and its transaction is aborted with 40P01 deadlock_detected. The other transactions then proceed. The timeout is deliberately set so most lock waits resolve before the relatively expensive cycle check even runs.

LWLocks: the in-memory workhorses

Access to shared structures — buffer headers, the ProcArray, WAL insertion slots — is protected by LWLocks (lwlock.c), which support shared and exclusive modes but have no deadlock detection (the code is written to always acquire them in a safe order). When you see wait events like LWLock:WALInsert or LWLock:BufferContent in pg_stat_activity, you are looking at contention on these internal locks, not user-level table locks.

Layer 3 — Watch it happen on your own database

-- Who is blocking whom, right now
SELECT waiting.pid       AS waiting_pid,
       blocking.pid      AS blocking_pid,
       waiting.query     AS waiting_query
FROM   pg_stat_activity waiting
JOIN   LATERAL unnest(pg_blocking_pids(waiting.pid)) AS bp(pid) ON true
JOIN   pg_stat_activity blocking ON blocking.pid = bp.pid
WHERE  waiting.wait_event_type = 'Lock';

To see it live: in session A run BEGIN; ALTER TABLE accounts ADD COLUMN x int; and leave it open; in session B run a plain SELECT * FROM accounts;. Session B hangs, and this query names session A as the blocking_pid — the ACCESS EXCLUSIVE lock from the DDL is blocking an ordinary read. pg_blocking_pids() is the fastest way to find the head of a lock queue during an incident.

Layer 4 — The levers this hands you

• Run schema changes with a lock timeout so a blocked ALTER fails fast instead of queueing every query behind it: SET lock_timeout = '2s';
• Acquire locks in a consistent order across your application to avoid deadlocks by construction.
• Diagnose blocking with pg_blocking_pids() to see exactly which PID holds the lock a query is waiting on.
• Watch wait events to tell the difference between heavyweight contention (a user-level lock) and LWLock contention (an internal hotspot such as WALInsert or BufferContent).
• **Tune deadlock_timeout carefully.** It is the delay before the cycle check runs, not a correctness setting; lowering it makes detection faster but adds overhead.

Layer 5 — What an Oracle DBA should expect vs what they get

Locking philosophy is similar, but the storage of row locks and the failure modes differ sharply:

• Row locks live in the tuple, not a lock table. Oracle records row locks in the block (ITL slots); PostgreSQL writes the locker's XID into the tuple's t_xmax with infomask flags. Both avoid a giant in-memory lock table, but Postgres has no "maximum number of locks" to size — and no initrans/maxtrans tuning.
• Deadlocks are detected, then one victim aborts. Like Oracle (ORA-00060), Postgres raises 40P01 deadlock_detected after deadlock_timeout and rolls back the cheapest victim. Your app needs the same retry discipline.
• Multixacts are a Postgres-only concept. When many transactions share-lock one row, Postgres allocates a MultiXactId with its own wraparound horizon and vacuum needs — an operational hazard with no Oracle analogue.
• DDL is the real danger, not reads. In Oracle many DDLs are online or use different concurrency; in Postgres ACCESS EXCLUSIVE DDL blocks even SELECT, so lock_timeout + careful migration patterns (the Operations pathway) matter far more.

Key takeaway

PostgreSQL coordinates concurrency with three tiers — heavyweight locks (in lock.c, governed by the mode conflict table), LWLocks for in-memory structures, and spinlocks beneath them. Row locks are stored in the tuple's t_xmax, deadlocks are detected via a wait-for graph after deadlock_timeout, and the one rule that saves you in production is that ACCESS EXCLUSIVE conflicts with everything. Always run DDL with a lock_timeout, and use pg_blocking_pids() to find the head of any lock queue.