Where Locks Live

Topic: Databases

Concrete problem

A batch job runs SELECT ... FOR UPDATE on one million rows. Two questions:

1. Postgres must remember one million locks. Where does it keep them? A table in memory with one million entries would use too much memory.
2. Another session wants one of these rows. It must wait. How does it know that the row is locked, and how does it wait?

Background terms

Transaction id

A transaction id (xid) is a number that Postgres gives to a transaction when it first writes. Each row version records the xid of the transaction that created it. A row version can also record the xid of the transaction that deleted, updated, or locked it.

Tuple header and xmax

A tuple is one row version on disk. The tuple header is a small block of data at the start of the tuple. The field xmax in the header holds the xid of the transaction that deleted, updated, or locked the tuple. A few flag bits in the header say which of these actions it was, and which lock mode.

MultiXact

A MultiXact is a group of transaction ids that Postgres treats as one id. If several transactions hold a lock on the same row (for example, several FOR SHARE locks), xmax holds a MultiXact id. A separate store lists the members.

Shared memory

Shared memory is a block of RAM that all Postgres server processes can read and write. Each session is a separate process. They use shared memory to share state, such as locks.

Heavyweight lock

A heavyweight lock is a lock that Postgres stores in the shared memory lock table. Table locks, transaction id locks, and advisory locks are heavyweight locks. Row locks are not.

pg_locks

pg_locks is a view. Each row shows one lock or one lock request in the shared memory lock table. The column granted is true for a held lock and false for a waiting request.

Intuition

Two kinds of resource need locks.

• Few and shared: tables, transaction ids, advisory lock names. A server has hundreds or thousands of them. A lock table in memory can hold them all.
• Many: rows. A server has billions of them. A lock table cannot hold one entry per locked row.

Postgres uses a different storage for each kind.

Kind of lock	Where it lives	Visible in pg_locks
Table lock (all 8 modes)	Shared memory lock table	yes, locktype = relation
Transaction id lock	Shared memory lock table	yes, locktype = transactionid
Advisory lock	Shared memory lock table	yes, locktype = advisory
Row lock (4 modes)	In the row header on disk (xmax and flag bits)	no

The row header answers question 1. The lock costs no space in the lock table. The lock costs a small change in each row. Postgres writes that change to the table page. The page becomes dirty. The change also goes to the write-ahead log.

Question 2 needs one more idea. A waiter finds xmax = X in the row header. The header says “transaction X holds a lock”. The waiter cannot wait on the row, because the row has no lock entry. So it waits on transaction X. This works because of a rule:

• Each transaction takes an exclusive lock on its own transaction id when it starts to write. It keeps it until it ends.

The waiter asks for a share lock on X’s transaction id. That request conflicts with X’s exclusive lock, so the waiter waits. When X commits or rolls back, X releases its lock. The waiter wakes up. This is the same rule as in Write-Write Conflict and Row Lock: the lock lasts until the end of the transaction.

When many waiters want the same row, they first queue on a short lock of type tuple. This keeps the order fair. Then they wait on the transaction id.

Numeric example

Cost of one million row locks. Assume 100 rows fit in one 8 KB page.

• Pages to change: $10^6/100=10^4$ pages.
• Memory to write: $10^4\times 8\text{ KB}=80\text{ MB}$.
• Lock table entries: about 3. These are one relation lock (ROW SHARE) and the transaction id locks. The count does not depend on the number of rows.

The cost is writes to disk pages, not memory in the lock table.

A wait, as pg_locks shows it. A updates row 1. B updates row 1.

Session	locktype	What it locks	mode	granted
A	relation	table t	RowExclusiveLock	true
A	transactionid	A’s own xid	ExclusiveLock	true
B	relation	table t	RowExclusiveLock	true
B	transactionid	A’s xid	ShareLock	false

There is no row entry for row 1. B waits on A’s transaction id. The row lock itself is in the header of row 1: xmax holds A’s xid.

Visual

graph TD
  subgraph Disk["On disk, in the table page"]
    H["Row header: xmax = A's xid + lock flag bits"]
  end
  subgraph Shm["Shared memory lock table (shown by pg_locks)"]
    T["relation lock on table t"]
    X["A's xid: ExclusiveLock, granted"]
    W["B's request: ShareLock on A's xid, waiting"]
  end
  B["Session B: UPDATE row 1"] -->|"1. reads xmax in the row header"| H
  B -->|"2. asks for a lock on that xid"| W
  W -.->|"conflicts with"| X
  A["Session A ends"] -->|"3. releases"| X

Notation

Find who blocks a session:

SELECT pid, pg_blocking_pids(pid) AS blocked_by,
       wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

A session that waits for a row lock shows wait_event_type = Lock and wait_event = transactionid. A session that waits for a table lock shows wait_event = relation.

Two names:

• pg_blocking_pids(pid) returns the pids of the sessions that block the session pid.
• The extension pgrowlocks shows row locks. pg_locks does not show them.

Plain-English translation

• “Row lock” means “a mark in the row header: this transaction holds a lock on this row”.
• “Wait on the transaction id” means “ask to share the holder’s own lock and stay blocked until the holder ends”.
• “Dirty page” means “a page in memory that differs from the page on disk, and must be written back”.

Equation

The shared memory lock table has a fixed number of slots:

$$N_{slots}\approx \text{max\_locks\_per\_transaction}\times (\text{max\_connections}+\text{max\_prepared\_transactions})$$

With the defaults $64\times (100+0)=6400$ slots. All sessions share these slots. One transaction can use more than 64. If the whole table is full, Postgres raises the error out of shared memory.

Row locks do not use these slots. So $N_{slots}$ does not limit the number of row locks.

What changes when parameters change

• More rows locked. Table pages written grows in proportion. Lock table use stays the same.
• Many tables locked in one transaction. Each table needs a slot. A transaction that touches thousands of tables (for example, many partitions) can fill the lock table.
• Several transactions share-lock the same row. xmax holds a MultiXact id. Each new locker creates or extends a MultiXact. This costs more than one lock.
• A read-only SELECT. It takes a table lock and no row lock. It writes nothing to the table pages.
• SELECT ... FOR UPDATE on a read-mostly table. The command makes table pages dirty. A read becomes a write. This surprises many teams.

Practice problem

1. A transaction runs SELECT ... FOR UPDATE on 50,000 rows in one table. How many pg_locks rows of locktype = relation does the transaction hold for that table, and how many entries per row lock?
2. Session B is stuck on an UPDATE. pg_stat_activity shows wait_event = transactionid. What does B wait for?
3. Why can pg_locks not show which rows are locked?

Answers: 1. One (at most, plus index locks), and none per row lock. 2. B waits for a transaction to end. pg_blocking_pids gives its pid. 3. Row locks are marks in row headers, not entries in the lock table.

Demo

Each scenario opens real concurrent sessions against a local Postgres. The log shows every statement with a time stamp. The folder is 05-where-locks-live.

Scenario	What it shows
01-pg-locks-wait-chain	A waiting UPDATE in pg_stat_activity, pg_locks, and pg_blocking_pids.
02-row-lock-in-tuple-header	The row lock as xmax and flag bits, seen with pageinspect.
03-row-lock-cost	Locking 100000 rows. pg_locks stays small. The WAL grows.

To run one scenario, start Postgres with ./up.sh. Then run go run ./05-where-locks-live/01-pg-locks-wait-chain in the repository. To run all scenarios of this concept, use ./run.sh 05-where-locks-live.

My reasoning

(Fill in after the quiz.) Probe on 2026-10-05: the user expected “a special system table that has one row per lock” for row locks. That is close to true for table locks and transaction id locks. It is not true for row locks.

Session history

• 2026-10-05 — First taught. See 2026-10-05 Databases - Postgres Locking.
• 2026-10-05 — Demo section added. See 05-where-locks-live.