Lock Manager: How PostgreSQL Coordinates Concurrent Access

One thing 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.

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;

locktype  |      mode       | granted |              relname              | state  |                      query                      
------------+-----------------+---------+-----------------------------------+--------+-------------------------------------------------
 relation   | AccessShareLock | t       | pg_authid                         | active | 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;
 relation   | AccessShareLock | t       | pg_authid_oid_index               | active | 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;
 relation   | AccessShareLock | t       | pg_authid_rolname_index           | active | SELECT l.locktype, l.mode, l.granted,

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 serializes writers per row.

Multixacts: many lockers, one row

When several transactions participate in one tuple's lock/update state, xmax can hold a MultiXactId. It maps to member XIDs and per-member statuses in the pg_multixact/offsets and pg_multixact/members SLRUs; members can include lockers and an updater, not only shared lockers.

Deadlock detection

PostgreSQL detects rather than prevents deadlocks. After a backend waits beyond deadlock_timeout, DeadLockCheck() traverses hard edges from conflicting holders and soft edges from earlier queue members. It may reorder a soft conflict; for a hard cycle, the backend whose check resolves it raises an error in its own transaction. There is no cheapest-transaction score, so applications must not rely on which session is aborted.

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.

Naming the layer before you tune it

• Run schema changes with a lock timeout so a blocked ALTER fails fast instead of queuing 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.