Transactions and MVCC

Locking and deadlocks

Reads and writes rarely block each other, but every statement still takes a table lock, and DDL needs the strongest one. Locks are granted in queue order, which is how one waiting ALTER TABLE can stall a whole application.

In plain words

Thanks to MVCC, readers and writers do not block each other. Still, every statement takes a lock on the table to say "I am using this, don't drop it under me". A SELECT takes the weakest lock; an ALTER TABLE or DROP takes the strongest, which conflicts with everything.

Waiting requests form a queue, and newcomers line up behind it. So if a long report holds a weak lock and an ALTER TABLE starts waiting for it, every following query, even a plain SELECT, waits behind the ALTER. A one-second schema change can turn into minutes of downtime. The cure is lock_timeout: let the ALTER give up quickly and retry later.

Rows are locked too, when they are updated, deleted or selected FOR UPDATE. If two transactions each hold a row the other needs, neither can continue: a deadlock. PostgreSQL notices after deadlock_timeout (1 second) and cancels one of them.

For DBAs and operators

Table lock modes (strongest last)

Mode	Taken by	Conflicts with
ACCESS SHARE	SELECT	ACCESS EXCLUSIVE
ROW SHARE	SELECT FOR UPDATE/SHARE	EXCLUSIVE, ACCESS EXCLUSIVE
ROW EXCLUSIVE	INSERT, UPDATE, DELETE, MERGE	SHARE and stronger
SHARE UPDATE EXCLUSIVE	VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY, some ALTER TABLE	itself and stronger
SHARE	CREATE INDEX	ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE and stronger
SHARE ROW EXCLUSIVE	CREATE TRIGGER, ADD FOREIGN KEY	ROW EXCLUSIVE and stronger, itself
EXCLUSIVE	REFRESH MATERIALIZED VIEW CONCURRENTLY	ROW SHARE and stronger
ACCESS EXCLUSIVE	DROP, TRUNCATE, VACUUM FULL, CLUSTER, most ALTER TABLE, LOCK TABLE	everything

Table locks live in the shared lock table, sized by max_locks_per_transaction × (max_connections + …). Touching thousands of partitions in one transaction can end in ERROR: out of shared memory HINT: You might need to increase "max_locks_per_transaction".

The lock queue in practice

Reproduced on PostgreSQL 18.6: session 1020 runs a long transaction after SELECT count(*) FROM acc, 1024 runs ALTER TABLE acc ADD COLUMN x int, 1033 runs SELECT * FROM acc:

SELECT pid, pg_blocking_pids(pid), wait_event_type, wait_event, left(query,40)
  FROM pg_stat_activity WHERE datname = current_database();
 pid  | pg_blocking_pids | wait_event_type | wait_event
 1020 | {}               | Timeout         | PgSleep
 1024 | {1020}           | Lock            | relation
 1033 | {1024}           | Lock            | relation

SELECT locktype, relation::regclass, mode, granted, pid
  FROM pg_locks WHERE relation = 'acc'::regclass;
 relation | acc | AccessShareLock     | t | 1020
 relation | acc | AccessShareLock     | f | 1033
 relation | acc | AccessExclusiveLock | f | 1024

Note that 1033 is blocked by the waiting ALTER, not by 1020. Safe DDL pattern: SET lock_timeout = '2s'; then the DDL, in a retry loop. On timeout the statement fails with ERROR: canceling statement due to lock timeout. Long-running and idle-in-transaction sessions are the usual root cause; idle_in_transaction_session_timeout limits the latter.

Row locks and multixacts

Row locks are not kept in shared memory: the locker's XID is written into the tuple's xmax with lock bits. A second writer sees it and waits on that transaction (locktype = transactionid, wait event Lock:transactionid), holding a tuple lock meanwhile to keep its place in line (later waiters queue on that lock: Lock:tuple). When several transactions share-lock the same row (FOR SHARE, FOR KEY SHARE taken by foreign key checks), xmax holds a MultiXact ID whose member list lives in pg_multixact. Heavy FK traffic can therefore consume multixacts quickly; they also need freezing (see wraparound).

Deadlocks

A waiting backend sleeps deadlock_timeout (1s) and then searches the wait-for graph. If it finds a cycle, it cancels itself. Real output from PostgreSQL 18.6:

ERROR: deadlock detected DETAIL: Process 963 waits for ShareLock on transaction 1155; blocked by process 974. Process 974 waits for ShareLock on transaction 1154; blocked by process 963. HINT: See server log for query details. CONTEXT: while updating tuple (0,2) in relation "acc"

The server log additionally lists each process's query. Fix deadlocks in the application: touch rows in a consistent order (for example ORDER BY id with FOR UPDATE). With log_lock_waits = on every wait longer than deadlock_timeout is logged as LOG: process 1024 still waiting for AccessExclusiveLock on relation 16390 of database 16384 after 1000.012 ms. New in PostgreSQL 18: log_lock_failures logs failed NOWAIT attempts.

For developers: inside the source

Where it lives

lock.c: the heavyweight lock manager (shared lock table, fast path, the LockConflicts[] matrix).
lmgr.c: per-object wrappers such as LockRelationOid(), LockTuple(), XactLockTableInsert(), XactLockTableWait().
proc.c (sleeping, timeouts, log_lock_waits messages) and deadlock.c (the wait-for graph search).
README (LWLocks vs heavyweight locks, fast path, group locking for parallel query) and README.tuplock (row lock modes, tuple locks, multixacts). Lock modes are defined in lockdefs.h.

Key data structures

LOCKTAG: what is locked; locktag_type (LOCKTAG_RELATION, LOCKTAG_TUPLE, LOCKTAG_TRANSACTION, LOCKTAG_ADVISORY…) is pg_locks.locktype.
LOCK: one per locked object, with grantMask, waitMask, the waitProcs queue and per-mode granted[]/requested[] counts. PROCLOCK: one per (lock, backend), with its holdMask.
LOCALLOCK: a backend-local hash, so re-taking a lock the backend already holds never touches shared memory.
Fast path: weak relation locks (EligibleForRelationFastPath(): mode below ShareUpdateExclusiveLock) are stored in the backend's own PGPROC under fpInfoLock. New in PostgreSQL 18, the slot count scales with max_locks_per_transaction: InitializeFastPathLocks() sets FastPathLockGroupsPerBackend groups of FP_LOCK_SLOTS_PER_GROUP (16), so the default 64 gives 64 slots. A strong request bumps FastPathStrongRelationLocks and moves other backends' entries into the shared table with FastPathTransferRelationLocks(). pg_locks.fastpath shows which is which.

Call flow

LockAcquireExtended(): local hash → fast path → partition LWLock (LockHashPartitionLock(), 16 partitions) → SetupLockInTable(). If the mode conflicts with the waitMask (earlier waiters) or with granted locks (LockCheckConflicts()), it calls JoinWaitQueue(), then WaitOnLock() → ProcSleep(). The waitMask test is the code behind the lock-queue trap. When the DEADLOCK_TIMEOUT timer fires, ProcSleep() calls CheckDeadLock(), which takes all partition locks and runs DeadLockCheck(); a soft deadlock is fixed by reordering wait queues (TopoSort()), a hard one ends in DeadLockReport(). LockRelease() wakes waiters through ProcLockWakeup().

Row locks: every transaction holds ExclusiveLock on its own XID (XactLockTableInsert()). heap_lock_tuple()/heap_update() find a locker in xmax, take the tuple lock with heap_acquire_tuplock(), then sleep in XactLockTableWait() (a ShareLock on the other XID, hence "waits for ShareLock on transaction") or MultiXactIdWait().

Observe and test

pg_locks = GetLockStatusData(); pg_blocking_pids() = GetBlockerStatusData(). Contrib pgrowlocks decodes row locks from xmax.
Isolation specs in src/test/isolation/specs: deadlock-simple, deadlock-hard, deadlock-soft, lock-nowait, timeouts, tuplelock-*, fk-deadlock; regression tests lock.sql, advisory_lock.sql.
trace_locks, trace_userlocks, trace_lwlocks and debug_deadlocks exist only in builds compiled with LOCK_DEBUG.

Parameters that shape this

lock_timeout deadlock_timeout log_lock_waits log_lock_failures max_locks_per_transaction statement_timeout idle_in_transaction_session_timeout