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

Try these on a later review. Do not open the answers first.

1. A updates 20 rows of t and does not commit. B runs SELECT ... FOR SHARE on one of these rows. Which pg_locks entries does B have? Which entry tells you which row is involved?
2. A DBA wants to lower max_locks_per_transaction. The reason: “our jobs lock millions of rows and use too much lock table memory”. Is the reason correct?
3. A report runs SELECT ... FOR UPDATE on every row of a read-mostly table, to “be safe”. The table gets no writes from other sessions. What extra cost does the report cause?

Answers

1. B has a relation lock on t (RowShareLock, granted). B also has a transactionid lock on A’s xid (ShareLock, not granted). No entry names the row. The row appears only in the row header.
2. No. Row locks do not use lock table slots. Only table locks, transaction id locks, and advisory locks use them. The memory cost of row locks is writes to table pages.
3. The report writes to every table page. The pages become dirty and the changes go to the write-ahead log. A read becomes a write, even with no conflict.

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.

Quiz

Asked on 2026-10-05 after the lesson. All three answers were correct.

Quiz (2026-10-05)

Q1. A transaction runs SELECT ... FOR UPDATE on 1,000,000 rows of one table. Which statement is true?

• One lock table entry per locked row

• One lock table entry per locked page

• Lock marks in the row headers

• Lock marks in shared memory only

Answer and reasoning

Your answer: Lock marks in the row headers (correct). Correct answer: Lock marks in the row headers.

The row lock is a mark in the row header, so the lock table stays small. The first option is the probe misconception: one lock table entry per row. The second option moves the same error to pages. The fourth option puts the marks in shared memory, but row locks write to the table pages.

Q2. Session B runs an UPDATE and is stuck. pg_stat_activity shows wait_event = transactionid. What does B wait for?

• Another transaction to end

• A row lock entry to leave pg_locks

• A table lock to be released

• A write-ahead log flush to finish

Answer and reasoning

Your answer: Another transaction to end (correct). Correct answer: Another transaction to end.

A waiter cannot wait on a row, because the row has no lock entry. It waits on the transaction id of the holder. The second option is the “row entry in pg_locks” model. The third option is the table lock model. The fourth option is a wrong link to the write-ahead log.

Q3. Why does pg_locks not show which rows are locked?

• It shows only locks that block a session

• It shows only table locks by design

• Row locks end before the transaction ends

• Row locks are marks, not lock table entries

Answer and reasoning

Your answer: Row locks are marks, not lock table entries (correct). Correct answer: Row locks are marks, not lock table entries.

pg_locks shows the shared memory lock table. Row locks are not in it. The first and second options give wrong ideas about what the view filters and covers. The third option gives a wrong idea about when row locks end.

My reasoning

The probe miss on 2026-10-05 is closed. 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. After the lesson, the user picked the right model on all three quiz questions: marks in row headers, waiting on a transaction id, and why pg_locks hides 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.
• 2026-10-05 — Quiz passed (3 of 3). Level 2. See 2026-10-05 Databases - Postgres Locking.