Row Lock Modes

Topic: Databases

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.

1. Change it. No one else may change it or rely on it.
2. 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.

1. 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.
2. 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.
3. Foreign key checks. Postgres takes FOR KEY SHARE on the parent row.
4. 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:

$$\text{modes }i,j\text{ conflict}⟺i+j\ge 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

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

Table parent(id, name) has a primary key id. Table child(parent_id) has a foreign key to parent(id).

1. A runs UPDATE parent SET name = 'x' WHERE id = 1 and does not commit. B runs INSERT INTO child (parent_id) VALUES (1). Does B wait?
2. A runs UPDATE parent SET id = 99 WHERE id = 1 and does not commit. B runs the same INSERT as in problem 1. Does B wait? Why is this different from problem 1?
3. Two sessions each run SELECT ... FOR SHARE on row 1. Then both try an UPDATE of a non-key column of row 1. What happens?

Answers

1. No. The UPDATE takes FOR NO KEY UPDATE. The foreign key check takes FOR KEY SHARE. These two modes do not conflict.
2. Yes. A key change takes FOR UPDATE. FOR UPDATE conflicts with FOR KEY SHARE. The parent key may disappear, so the child row must wait.
3. A deadlock. Each session holds FOR SHARE. The UPDATE needs FOR NO KEY UPDATE, which conflicts with FOR SHARE of the other session. Each waits for the other. Postgres aborts one session.

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.

Quiz (2026-10-05)

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

• SELECT ... FOR SHARE on row 1

• Plain SELECT of row 1

Answer and reasoning

Your answer: UPDATE of a non-key column of row 1 (correct). Correct answer: UPDATE of a non-key column of row 1.

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

Answer and reasoning

Your answer: So that an UPDATE of a non-key column does not block an insert into a child table (correct). Correct answer: So that an UPDATE of a non-key column does not block an insert into a child table.

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

• Use SELECT ... FOR SHARE

• Use SELECT ... FOR KEY SHARE

• Keep plain SELECT, and run the code in one transaction

Answer and reasoning

Your answer: Use SELECT ... FOR UPDATE (correct). Correct answer: Use SELECT ... FOR UPDATE.

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.