Lock modes and the conflict matrix · PostgreSQL

Locks are where PostgreSQL’s “readers never block writers” guarantee meets the operations that genuinely do need exclusivity. The system is small — eight table-level modes and one matrix — and knowing it converts “the migration caused an outage” into “the migration took ACCESS EXCLUSIVE, which we should have expected”.

The eight modes

Ordered from weakest to strongest. Every ordinary statement takes one of these on every table it touches.

Mode	Taken by
`ACCESS SHARE`	`SELECT`
`ROW SHARE`	`SELECT … FOR UPDATE/SHARE`, foreign key checks
`ROW EXCLUSIVE`	`INSERT`, `UPDATE`, `DELETE`, `MERGE`
`SHARE UPDATE EXCLUSIVE`	`VACUUM`, `ANALYZE`, `CREATE INDEX CONCURRENTLY`, most `ALTER TABLE … SET`
`SHARE`	`CREATE INDEX` (non-concurrent)
`SHARE ROW EXCLUSIVE`	`CREATE TRIGGER`, some `ALTER TABLE` forms
`EXCLUSIVE`	`REFRESH MATERIALIZED VIEW CONCURRENTLY`
`ACCESS EXCLUSIVE`	`ALTER TABLE` (most forms), `DROP TABLE`, `TRUNCATE`, `VACUUM FULL`, `CLUSTER`, `REINDEX`

The names are historical and two of them are actively misleading, which the measurements below make concrete.

The matrix, measured

Rather than restate the documented matrix, each pair below was tested by holding one mode in an open transaction and attempting the other from a second session with a two-second lock_timeout.

Read-only / Safetable-level conflicts, measured pairwise on 18.6— Each row is a real attempt. COMPATIBLE means the second lock was granted; CONFLICTS means the attempt hit the timeout.

$ psql -U postgres -c "SET lock_timeout='2s'; BEGIN; LOCK TABLE lockdemo IN <mode> MODE; COMMIT"   # against a held lock

  ACCESS SHARE             vs ACCESS SHARE             -> COMPATIBLE
ACCESS SHARE             vs ACCESS EXCLUSIVE         -> CONFLICTS
ROW EXCLUSIVE            vs ROW EXCLUSIVE            -> COMPATIBLE
ROW EXCLUSIVE            vs SHARE                    -> CONFLICTS
SHARE UPDATE EXCLUSIVE   vs SHARE UPDATE EXCLUSIVE   -> CONFLICTS
SHARE UPDATE EXCLUSIVE   vs ROW EXCLUSIVE            -> COMPATIBLE
SHARE                    vs SHARE                    -> COMPATIBLE
SHARE                    vs ROW EXCLUSIVE            -> CONFLICTS
EXCLUSIVE                vs ACCESS SHARE             -> COMPATIBLE
ACCESS EXCLUSIVE         vs ACCESS SHARE             -> CONFLICTS

Three of those deserve to be pulled out.

SHARE UPDATE EXCLUSIVE conflicts with itself. Two vacuums, or a vacuum and an ANALYZE, cannot run on the same table at the same time. This is why a long vacuum delays the analyze that would have fixed a plan, and why CREATE INDEX CONCURRENTLY on a table currently being vacuumed waits.

SHARE UPDATE EXCLUSIVE is compatible with ROW EXCLUSIVE. Vacuum does not block INSERT, UPDATE or DELETE. This is the whole reason routine vacuum is usable on production.

EXCLUSIVE is compatible with ACCESS SHARE. A lock mode called “exclusive” still permits plain SELECT.

Which DDL takes what

This is the table worth knowing before writing a migration, because the difference between two ALTER TABLE forms can be an outage.

Operation	Lock	Blocks reads?
`ADD COLUMN` with no default, or with a non-volatile default	`ACCESS EXCLUSIVE`	Yes, but very briefly
`ADD COLUMN` with a volatile default	`ACCESS EXCLUSIVE`	Yes, for a full rewrite
`DROP COLUMN`	`ACCESS EXCLUSIVE`	Yes, briefly — metadata only
`ALTER COLUMN TYPE`	`ACCESS EXCLUSIVE`	Yes, for a full rewrite
`SET NOT NULL`	`ACCESS EXCLUSIVE`	Yes, for a full scan
`ADD CONSTRAINT … NOT VALID`	`ACCESS EXCLUSIVE`	Yes, briefly
`VALIDATE CONSTRAINT`	`SHARE UPDATE EXCLUSIVE`	No
`CREATE INDEX`	`SHARE`	No — blocks writes
`CREATE INDEX CONCURRENTLY`	`SHARE UPDATE EXCLUSIVE`	No
`ALTER TABLE … SET (fillfactor = …)`	`SHARE UPDATE EXCLUSIVE`	No
`TRUNCATE`	`ACCESS EXCLUSIVE`	Yes

The pattern the safe migrations follow is visible in two rows: split a long ACCESS EXCLUSIVE operation into a brief one plus a long weak one. ADD CONSTRAINT … NOT VALID takes the strong lock for an instant and does no scanning; VALIDATE CONSTRAINT does the scanning under a lock that blocks nothing. Lesson IX-05 develops this.

A brief ACCESS EXCLUSIVE is only brief if it is granted immediately

ALTER TABLE … ADD COLUMN note text is metadata-only in modern PostgreSQL. The lock is held for microseconds.

Measured on 18.6, that same statement took 18 seconds, because a SELECT was already running and it had to wait. And while it waited, every subsequent query queued behind it — which is the subject of lesson IX-05 and the single most damaging thing about strong locks.

The duration of the operation and the duration of the outage are different numbers. The second is set by whatever was already running.

Always set a lock_timeout before DDL:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;

Failing fast and retrying is strictly better than waiting indefinitely while a queue builds behind you.

Reading pg_locks

pg_locks shows every lock held and every lock waited for. It is not readable on its own; joined to pg_stat_activity it is.

SELECT a.pid,
       a.state,
       l.locktype,
       l.mode,
       l.granted,
       CASE l.locktype
         WHEN 'relation' THEN l.relation::regclass::text
         WHEN 'transactionid' THEN l.transactionid::text
         ELSE coalesce(l.objid::text, '')
       END AS target,
       left(a.query, 60) AS query
  FROM pg_locks l
  LEFT JOIN pg_stat_activity a ON a.pid = l.pid
 WHERE l.locktype IN ('relation', 'transactionid', 'tuple')
 ORDER BY a.pid, l.granted DESC;

granted = false is the row that matters: that session is waiting.

Read-only / Safewhat a real contention picture looks like— Captured on 18.6. Three sessions: a running SELECT, an ALTER waiting for it, and a second SELECT waiting for the ALTER.

$ psql -U postgres -c "SELECT a.pid, a.state, l.mode, l.granted, left(a.query,42) AS query, pg_blocking_pids(a.pid) AS blocked_by FROM pg_locks l JOIN pg_stat_activity a ON a.pid=l.pid JOIN pg_class c ON c.oid=l.relation WHERE c.relname='lockdemo' ORDER BY a.pid"

 pid  | state  |        mode         | granted |                   query                    | blocked_by
------+--------+---------------------+---------+--------------------------------------------+------------
1615 | active | AccessShareLock     | t       | SELECT pg_sleep(20);                       | {}
1622 | active | AccessExclusiveLock | f       | ALTER TABLE lockdemo ADD COLUMN note text; | {1615}
1629 | active | AccessShareLock     | f       | SELECT count(*) FROM lockdemo;             | {1622}
(3 rows)

The locktype column distinguishes what is being locked, and the distinction matters when reading a blocking chain:

relation — a table or index. What DDL and vacuum contend on.
transactionid — a waiter attached to another transaction’s id, which is how row-level conflicts are represented.
tuple — a short-lived lock on one tuple, taken while a session works out whether it can proceed.
virtualxid — every session holds one on itself, always. Noise.

Why row locks do not appear in pg_locks, and what that means for capacity

A table with ten million rows updated by one transaction produces no pg_locks rows for those ten million locks. The lock table is a fixed size in shared memory, and ten million entries would not fit.

Instead, a row lock is recorded in the row itself, in the xmax field from lesson VII-01. Locking a row is a page write, not a shared memory allocation, so it costs the same regardless of how many rows are locked.

When another session meets a locked row, it reads xmax, finds the transaction id, and then takes a real lock — on that transaction id, not on the row. It sleeps until the transaction ends. This is why wait_event shows transactionid for row contention, and why pg_locks shows a transactionid row rather than a row-level entry.

Three consequences worth carrying.

Row-level contention does not consume lock table memory. You cannot exhaust max_locks_per_transaction by locking rows.

You can exhaust it with relations. The limit is on relation-level locks, and the shared table is sized as max_locks_per_transaction × (max_connections + max_prepared_transactions). A query touching two thousand partitions takes two thousand relation locks in one transaction, which is why heavily partitioned schemas hit ERROR: out of shared memory with the hint You might need to increase max_locks_per_transaction. It is a postmaster parameter, so raising it needs a restart.

A transaction holding row locks releases them only at commit or abort. There is no way to release one early, because there is no lock object to release — the release is the transaction ending.

What to take from this

Eight modes, one matrix. Most statements take the weakest one that works.
“ACCESS” in a mode name is about plain SELECT. EXCLUSIVE allows reads; ACCESS EXCLUSIVE does not.
SHARE UPDATE EXCLUSIVE conflicts with itself but not with writes, which is what makes vacuum usable.
A brief strong lock is only brief if it is granted immediately. Set lock_timeout before DDL.
Row locks live in the row, not in pg_locks. Relation locks are the ones that can exhaust shared memory.

Cross-course references

Linux for Production Sysadmins — Part LXXIX (Troubleshooting methodology) covers working from a matrix rather than from a guess, which is the habit this lesson is training.
Git, CI/CD & GitOps — Part CVI (Change management) covers getting the lock a migration will take stated in the change record, so the window is sized before the statement runs.