Row Lock Modes

Concrete problem

A shop has a customers table and an orders table. Each order points to one customer through a foreign key.

Request 1 inserts a new order for customer 7.
Request 2 updates last_login of customer 7.

These two requests do not interfere. Request 1 needs customer 7 to exist. Request 2 does not delete customer 7 and does not change its id. Postgres must let both requests run without waiting. Which lock modes make this possible?

A second problem comes from application code. The code reads stock, computes a new value, and writes it back. Two sessions run this code. Which lock mode makes the code safe?

Background terms

Lock mode

A lock mode is the name of a kind of lock. A transaction asks for a lock in one mode. The mode decides which other transactions must wait.

Conflict table

The conflict table lists every pair of lock modes. For each pair it says “these two modes conflict” or “these two modes do not conflict”. When two modes conflict, the later transaction waits. The conflict table is the real content of a locking system. The mode names are only labels.

Foreign key

A foreign key is a column in a child table that points to a row in a parent table. Example: orders.customer_id points to customers.id. The database rejects a child row if its parent row does not exist.

Key columns

Key columns are the columns that a foreign key can point to. They are the columns of a unique index, such as the primary key. An UPDATE that changes a key column is more dangerous than an UPDATE that does not, because child rows may point to the old key value.

Intuition

A transaction can need one of two things from a row.

Change it. No one else may change it or rely on it.
Keep it. The row must stay as it is. Many transactions may need this at the same time.

This gives two modes. Foreign keys expose a third fact. A “keep it” need can be small. A foreign key check only needs the parent row to exist and to keep its key. The check does not care about other columns. A “change it” need can also be small. An UPDATE of a non-key column does not delete the row and does not change its key.

So each need splits into a big case and a small case:

	Big (whole row)	Small (key only)
Keep it	`FOR SHARE`	`FOR KEY SHARE`
Change it	`FOR UPDATE`	`FOR NO KEY UPDATE`

Here the word “big” means that the mode protects or changes the whole row, including the key. The word “small” means that the mode only protects the key or only changes non-key columns.

Each mode makes a promise. A conflict means that two promises cannot hold together.

Mode	The holder says	Taken by
`FOR KEY SHARE`	”This row will still exist, with the same key.”	Foreign key check on child insert
`FOR SHARE`	”This row will not change at all.”	`SELECT ... FOR SHARE`
`FOR NO KEY UPDATE`	”I will change non-key columns of this row.”	`UPDATE` that does not change a key column; `SELECT ... FOR NO KEY UPDATE`
`FOR UPDATE`	”I will delete this row or change its key.”	`DELETE`; `UPDATE` that changes a key column; `SELECT ... FOR UPDATE`

Use the promises to find each conflict:

FOR UPDATE may delete the row. It breaks every other promise. It conflicts with all four modes.
FOR NO KEY UPDATE changes non-key columns. It breaks “will not change at all” (FOR SHARE). It breaks “I will change” from another writer. It does not break “still exists with the same key” (FOR KEY SHARE).
FOR SHARE says “will not change at all”. It conflicts with any writer: FOR NO KEY UPDATE and FOR UPDATE. It does not conflict with another reader.
FOR KEY SHARE says “still exists, same key”. Only FOR UPDATE breaks this promise.

A plain SELECT makes no promise and asks for no lock. It conflicts with nothing.

Numeric example

Row 1 is locked by A. B asks for a lock on the same row. Read the table with A’s mode in the row and B’s mode in the column.

A holds \ B asks	`KEY SHARE`	`SHARE`	`NO KEY UPDATE`	`UPDATE`
`KEY SHARE`	ok	ok	ok	wait
`SHARE`	ok	ok	wait	wait
`NO KEY UPDATE`	ok	wait	wait	wait
`UPDATE`	wait	wait	wait	wait

Count the conflicts row by row. The row for KEY SHARE has 1 conflict. The row for SHARE has 2. The row for NO KEY UPDATE has 3. The row for UPDATE has 4. The total is $1 + 2 + 3 + 4 = 10$ of 16 cells.

The table is symmetric. If A holding X blocks B asking for Y, then A holding Y blocks B asking for X.

Now apply the table to the concrete problem.

Request 1 (insert order) takes KEY SHARE on customer 7.
Request 2 (update last_login) takes NO KEY UPDATE on customer 7.
The cell KEY SHARE versus NO KEY UPDATE is ok. Nobody waits.

If the table had only FOR SHARE, the cell would be wait. This is the reason for the split.

Visual

The four modes sit on a line from weakest to strongest. A stronger mode conflicts with everything that a weaker mode conflicts with, and with more.

graph LR
  K["FOR KEY SHARE<br/>conflicts with: UPDATE"] --> S["FOR SHARE<br/>conflicts with: UPDATE, NO KEY UPDATE"]
  S --> N["FOR NO KEY UPDATE<br/>conflicts with: UPDATE, NO KEY UPDATE, SHARE"]
  N --> U["FOR UPDATE<br/>conflicts with: all four"]

Notation

Postgres has four ways to take a row lock.

SELECT ... FOR UPDATE (or FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE). This is an explicit lock request. The statement locks every row that it returns.
UPDATE and DELETE. These take a row lock by themselves. DELETE takes FOR UPDATE. UPDATE takes FOR NO KEY UPDATE, unless it changes a key column. Then it takes FOR UPDATE.
Foreign key checks. Postgres takes FOR KEY SHARE on the parent row.
SELECT without a lock clause. No lock.

All row locks last until COMMIT or ROLLBACK.

A safe read-modify-write has this shape:

BEGIN;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- the application computes new_stock
UPDATE products SET stock = :new_stock WHERE id = 1;
COMMIT;

The SELECT ... FOR UPDATE waits if another transaction holds a conflicting lock. When it returns, the application holds the lock. No one else can change the row until COMMIT. If the application does not change a key column, FOR NO KEY UPDATE is enough and blocks fewer foreign key checks.

Plain-English translation

“FOR UPDATE” means “I plan to delete this row or change its key.”
“FOR NO KEY UPDATE” means “I plan to change this row, but not its key.”
“FOR SHARE” means “No one may change this row while I work. Others may also look at it with FOR SHARE.”
“FOR KEY SHARE” means “This row must not disappear and must keep its key.”

Why FOR SHARE is a trap for read-modify-write code: two sessions both take FOR SHARE on the same row. These two locks do not conflict, so both succeed. Then each session runs UPDATE. Each UPDATE needs a lock that conflicts with the other session’s FOR SHARE. Each session waits for the other. This is a deadlock. Postgres aborts one of them.

Equation

Number the modes in strength order: $1 =$ KEY SHARE, $2 =$ SHARE, $3 =$ NO KEY UPDATE, $4 =$ UPDATE. Two modes $i$ and $j$ conflict exactly when their numbers add up to at least 5:

modes i, j conflict ⟺ i + j \geq 5

Check the rule on the table:

$(1, 4)$ has sum 5. It conflicts.
$(2, 3)$ has sum 5. It conflicts.
$(1, 3)$ has sum 4. It does not conflict.
$(2, 2)$ has sum 4. It does not conflict.
$(4, 4)$ has sum 8. It conflicts.

The rule gives the right answer for all 16 pairs. The rule is a memory aid. Postgres does not store the table this way.

What changes when parameters change

A plain SELECT joins in. It never conflicts. Nothing changes.
UPDATE changes a key column. The UPDATE takes FOR UPDATE, not FOR NO KEY UPDATE. Now a child insert (FOR KEY SHARE) waits.
The table has no foreign keys. FOR KEY SHARE is never used. FOR NO KEY UPDATE and FOR UPDATE then behave almost the same.
Several readers use FOR SHARE. They all hold the lock at once. A writer waits for all of them.
A transaction asks for a second lock on a row it already holds. It never conflicts with itself.

Practice problem

A holds FOR NO KEY UPDATE on row 5. For each request from B, say “wait” or “ok”.

SELECT ... FOR KEY SHARE on row 5.
SELECT ... FOR SHARE on row 5.
UPDATE of a non-key column of row 5.
Plain SELECT of row 5.
DELETE of row 6.

Answers: 1 ok, 2 wait, 3 wait, 4 ok, 5 ok.

Demo

Each scenario opens real concurrent sessions against a local Postgres. The log shows every statement with a time stamp. The folder is 02-row-lock-modes.

Scenario	What it shows
01-row-lock-matrix	The 4 by 4 conflict table, tested on all 16 pairs.
02-fk-key-share	Why `FOR KEY SHARE` exists. A non-key `UPDATE` does not block a child insert.
03-for-update-read-modify-write	Ten workers. A plain `SELECT` ends at 99. `FOR UPDATE` ends at 90.
04-for-share-deadlock	The `FOR SHARE` trap. Two sessions deadlock.

To run one scenario, start Postgres with ./up.sh. Then run go run ./02-row-lock-modes/01-row-lock-matrix in the repository. To run all scenarios of this concept, use ./run.sh 02-row-lock-modes.

Quiz

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

Q1. A holds FOR SHARE on row 1. Which request from B waits for A?

SELECT ... FOR KEY SHARE on row 1
UPDATE of a non-key column of row 1 (your answer, correct)
SELECT ... FOR SHARE on row 1
Plain SELECT of row 1

Why: that UPDATE takes FOR NO KEY UPDATE. FOR SHARE promises “this row will not change at all”. FOR NO KEY UPDATE breaks that promise, so the two modes conflict. The other three requests do not conflict with FOR SHARE.

Q2. Why does Postgres have FOR KEY SHARE and not only FOR SHARE?

So that a foreign key check does not read the parent row without a lock
So that an UPDATE of a non-key column does not block a plain SELECT of the parent row
So that FOR SHARE can be used on tables without a primary key
So that an UPDATE of a non-key column does not block an insert into a child table (your answer, correct)

Why: a foreign key check needs only the key of the parent row to stay. With FOR KEY SHARE, an UPDATE of a non-key column does not conflict with it. The second option is wrong because a plain SELECT never blocks, in any case.

Q3. Application code reads stock, computes a new value, and writes it back. Two sessions run the code at the same time. Which change to the read makes the code safe?

Use SELECT ... FOR UPDATE (your answer, correct)
Use SELECT ... FOR SHARE
Use SELECT ... FOR KEY SHARE
Keep plain SELECT, and run the code in one transaction

Why: FOR UPDATE conflicts with itself, so the second session waits and then reads the new value. FOR SHARE does not conflict with itself. Both sessions get it, and then both UPDATEs wait for each other. This is a deadlock. FOR KEY SHARE also does not conflict with itself, so it gives no protection. A plain SELECT takes no lock, even inside a transaction, so the lost update can still happen.

My reasoning

Probe on 2026-10-05. The user expected that SELECT ... FOR UPDATE “blocks all other sessions from reading the row”. This is the same misconception as in Write-Write Conflict and Row Lock. A lock stops other writers and lockers. It never stops a plain reader.

Session history

2026-10-05 — First taught. See 2026-10-05 Databases - Postgres Locking.
2026-10-05 — Demo section added. See 02-row-lock-modes.

Ohara

Explorer

Row Lock Modes

Concrete problem

Background terms

Intuition

Numeric example

Visual

Notation

Plain-English translation

Equation

What changes when parameters change

Practice problem

Demo

Quiz

My reasoning

Session history

Graph View

Table of Contents

Backlinks