Row locks versus table locks · PostgreSQL

Table locks arbitrate between statements. Row locks arbitrate between writers touching the same data, and PostgreSQL has four of them for a reason that becomes obvious once you see the foreign key case.

The four modes

Weakest to strongest.

Mode	Taken by	Intent
`FOR KEY SHARE`	Foreign key checks	“Do not change this row’s key”
`FOR SHARE`	`SELECT … FOR SHARE`	“Do not change this row at all”
`FOR NO KEY UPDATE`	`UPDATE` of non-key columns	“I am changing this row, but not its key”
`FOR UPDATE`	`SELECT … FOR UPDATE`, `DELETE`, key `UPDATE`	“I am changing or removing this row”

The matrix, measured

Read-only / Saferow lock conflicts, measured pairwise on 18.6— First mode held in an open transaction; second attempted from another session with lock_timeout 2s.

$ psql -U postgres -c "SET lock_timeout='2s'; BEGIN; SELECT id FROM lockdemo WHERE id=1 FOR <mode>; COMMIT"   # against a held row lock

  FOR KEY SHARE      vs  FOR KEY SHARE      ->  COMPATIBLE
FOR KEY SHARE      vs  FOR UPDATE         ->  BLOCKS
FOR SHARE          vs  FOR NO KEY UPDATE  ->  BLOCKS
FOR NO KEY UPDATE  vs  FOR NO KEY UPDATE  ->  BLOCKS
FOR NO KEY UPDATE  vs  FOR KEY SHARE      ->  COMPATIBLE
FOR UPDATE         vs  FOR KEY SHARE      ->  BLOCKS

The two COMPATIBLE rows are the interesting ones. FOR KEY SHARE and FOR NO KEY UPDATE coexist: one session may be updating a row’s non-key columns while another holds it against key changes. Everything else in this matrix conflicts.

Why that pair exists

A foreign key check is a read that must remain true until the transaction commits. Inserting a child row requires the parent row to exist, and it must go on existing.

The naive implementation locks the parent row against all changes, which means every insert into a busy child table blocks every update of the parent. FOR KEY SHARE is the refinement: it forbids only changes to the key, which is the only thing the check depends on.

Read-only / Safea child insert, held open, and what it locks on the parent— Captured on 18.6. Table-level locks held by the inserting session.

$ psql -U postgres -c "SELECT locktype, mode, granted, relation::regclass FROM pg_locks WHERE relation IN ('parent'::regclass,'child'::regclass) ORDER BY relation::regclass::text, mode"

 locktype |       mode       | granted | relation
----------+------------------+---------+----------
relation | RowExclusiveLock | t       | child
relation | RowShareLock     | t       | parent
(2 rows)

Now the two updates of that parent row, with the child insert still uncommitted.

Read-only / Safeupdating a non-key column of the referenced row— Captured on 18.6. Not blocked.

$ psql -U postgres -c "SET lock_timeout='3s'; UPDATE parent SET label='changed' WHERE id=1"

UPDATE 1
Time: 4.931 ms

Service impact possibleupdating the key of the same referenced row— Real error from 18.6. Same open child insert, same parent row.

$ psql -U postgres -c "SET lock_timeout='3s'; UPDATE parent SET id=99 WHERE id=2"

ERROR:  canceling statement due to lock timeout
Time: 3001.345 ms (00:03.001)

4.9 ms against a timeout. Same table, same row, same open transaction on the other side — the only difference is whether the key changed.

This is why 'foreign keys cause locking problems' is usually wrong

The belief is widespread and it was true before PostgreSQL 9.3, when a foreign key check took a lock that blocked all updates of the parent. Advice from that era survives, and teams still drop foreign keys to “reduce locking”.

The measurement above is what actually happens now: the parent row is freely updatable except for its key, and keys are rarely updated.

Where foreign keys genuinely do cause contention:

The key really is updated. A natural key that changes, or a DELETE of a parent with many children.
The referenced column is not indexed on the child side. PostgreSQL indexes the parent’s key automatically as part of the primary key or unique constraint, but not the child’s referencing column. Every parent DELETE or key UPDATE then scans the child table in full, under a lock. This is a real and common performance defect, and the fix is an index on the child column rather than removing the constraint.
Multixacts. Many concurrent lockers on one row allocate a multixact, discussed below.

Locking clauses in practice

-- read-modify-write: lock what you read
BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
UPDATE accounts SET balance = balance - 30 WHERE id = 42;
COMMIT;

Note, from lesson VII-04, that this particular example is better written without the lock, as UPDATE accounts SET balance = balance - 30. Use FOR UPDATE when the application genuinely must decide something between the read and the write.

FOR NO KEY UPDATE is the weaker version, when you will change the row but not its key. It permits concurrent foreign key checks, which matters on a parent row that children are constantly being added to.

FOR SHARE prevents anyone from changing the row while you hold it, without claiming it for yourself. Multiple sessions can hold it at once.

FOR KEY SHARE is what you want when the invariant is “this row must still exist with this identity”, which is what a foreign key check needs and occasionally what application logic needs too.

SKIP LOCKED and NOWAIT: when waiting is the wrong answer

Two modifiers change what happens when the row is already locked.

NOWAIT raises an error instead of waiting:

SELECT * FROM jobs WHERE id = 42 FOR UPDATE NOWAIT;
-- ERROR:  could not obtain lock on row in relation "jobs"

Useful when a lock conflict means “someone else is doing this, do not duplicate the work” and you want to know immediately.

SKIP LOCKED silently omits locked rows:

-- a work queue that scales to many concurrent workers
WITH next_job AS (
  SELECT id FROM jobs
   WHERE status = 'pending'
   ORDER BY created_at
   LIMIT 1
   FOR UPDATE SKIP LOCKED
)
UPDATE jobs SET status = 'running', started_at = now()
  FROM next_job WHERE jobs.id = next_job.id
RETURNING jobs.*;

This is the standard PostgreSQL work queue, and SKIP LOCKED is what makes it work. Without it, every worker asks for the same oldest job and they serialise into a single-threaded queue with extra steps.

The trade to be explicit about: SKIP LOCKED means the query does not see a consistent set of rows. That is exactly right for a queue and exactly wrong for a report.

Multixacts: what happens when several sessions lock the same row

xmax holds one transaction id. When two sessions lock the same row concurrently — say two foreign key checks against the same popular parent — one field cannot name both.

PostgreSQL allocates a multixact id: an entry in pg_multixact listing the transactions and the mode each holds. xmax then stores that id, with a header bit marking it as a multixact rather than a plain transaction id.

Four operational consequences.

Multixacts have their own wraparound. Their own 32-bit space, their own autovacuum_multixact_freeze_max_age (400,000,000), their own age function mxid_age(). Lesson VIII-05 covers this; the point here is where they come from.

They consume disk in $PGDATA/pg_multixact, and on a workload that generates them heavily that directory can become surprisingly large.

They make row locking measurably more expensive. Allocating a multixact and later reading it back to determine visibility is more work than checking a single transaction id.

They are generated by a recognisable workload shape: a small number of frequently-referenced parent rows, many children being inserted concurrently. A countries table referenced by a high-volume orders table is the archetype.

Watch for it:

SELECT datname,
       age(datfrozenxid)    AS xid_age,
       mxid_age(datminmxid) AS mxid_age
  FROM pg_database
 ORDER BY mxid_age(datminmxid) DESC;

If mxid_age is growing faster than xid_age, row-level lock concurrency is the reason, and the referenced rows are worth finding.

What to take from this

Four row lock modes exist so a foreign key check does not block ordinary updates of the referenced row.
Measured: non-key update of a referenced row, 4.9 ms. Key update of the same row, timeout.
“Foreign keys cause locking problems” is pre-9.3 advice. The real defect is usually a missing index on the child’s referencing column.
SKIP LOCKED is what makes a PostgreSQL work queue scale. It trades consistency for concurrency, deliberately.
Concurrent row locks allocate multixacts, which have their own wraparound and their own disk.

Cross-course references

Observability for Production Sysadmins — Part LIX (Database observability) covers exporting lock waits, and Part XX (Alert quality) covers why an alert on lock count is noise while an alert on wait duration is not.
Linux for Production Sysadmins — Part XXXVII (Resource management) covers the shared-memory limits that a relation-lock explosion eventually reaches.