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: