Row locks

MVCC means readers and writers never block each other — so who does block? Writers competing for the same rows. Every UPDATE and DELETE takes a row lock, and you can take one explicitly with SELECT ... FOR UPDATE and friends. This page shows what those locks do, which ones coexist, and the row locks you're taking without knowing it.

FOR UPDATE blocks writers — and only writers

A

B

SELECT … FOR UPDATE (locks the row)

SELECT balance→ 100 (readers don't block)

UPDATE -10→ ⏳ waits

COMMIT (releases the lock)

⏵ UPDATE -10→ completes

A> BEGIN;
BEGIN

A> SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
 id | owner | balance 
----+-------+---------
  1 | alice |     100 
(1 row)

Reading the locked row costs B nothing — MVCC readers don't take row locks.

B> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     100 
(1 row)

Writing to it is another story: B's UPDATE must wait for A.

B> UPDATE accounts SET balance = balance - 10 WHERE id = 1;
⏳ B is waiting for a lock…

A> UPDATE accounts SET balance = 150 WHERE id = 1;
UPDATE 1

A> COMMIT; -- releases the row lock
COMMIT

⏵ B resumes:
UPDATE 1

B> SELECT balance FROM accounts WHERE id = 1; -- B's -10 applied on top of A's committed 150
 balance 
---------
     140 
(1 row)

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

The four modes

Row locks come in four strengths (the manual's Table 13.3 is the same conflict matrix):

you hold ↓ / they want →	FOR KEY SHARE	FOR SHARE	FOR NO KEY UPDATE	FOR UPDATE
FOR KEY SHARE	✅	✅	✅	⛔
FOR SHARE	✅	✅	⛔	⛔
FOR NO KEY UPDATE	✅	⛔	⛔	⛔
FOR UPDATE	⛔	⛔	⛔	⛔

You rarely type the middle two by hand, yet PostgreSQL reaches for them constantly (Row-Level Locks). An UPDATE that doesn't touch key columns takes FOR NO KEY UPDATE — the manual: "This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock." An UPDATE that changes a key, and every DELETE, takes the full FOR UPDATE. A foreign-key check takes the weakest mode, FOR KEY SHARE, on the referenced row; the manual never quite says so, but the scenario below proves it — the conflict signature you'll see, a non-key UPDATE passing while a DELETE blocks, matches FOR KEY SHARE and nothing else in the matrix.

A

B

SELECT … FOR SHARE (A)

SELECT … FOR SHARE (B)→ coexists

UPDATE→ FOR NO KEY UPDATE → ⏳ waits

COMMIT (releases A's FOR SHARE)

⏵ UPDATE→ FOR NO KEY UPDATE → completes

UPDATE→ FOR NO KEY UPDATE (A)

SELECT … FOR KEY SHARE→ coexists with the UPDATE

SELECT … FOR UPDATE→ ⏳ waits

COMMIT (releases A)

⏵ SELECT … FOR UPDATE→ completes

Two FOR SHARE locks on the same row coexist happily…

A> BEGIN;
BEGIN

A> SELECT id FROM accounts WHERE id = 1 FOR SHARE;
 id 
----
  1 
(1 row)

B> BEGIN;
BEGIN

B> SELECT id FROM accounts WHERE id = 1 FOR SHARE;
 id 
----
  1 
(1 row)

B> COMMIT;
COMMIT

…but FOR SHARE still blocks a plain UPDATE (which takes FOR NO KEY UPDATE).

B> UPDATE accounts SET balance = 200 WHERE id = 1;
⏳ B is waiting for a lock…

A> COMMIT;
COMMIT

⏵ B resumes:
UPDATE 1

The weakest lock, FOR KEY SHARE, even coexists with a running UPDATE…

A> BEGIN;
BEGIN

A> UPDATE accounts SET balance = 300 WHERE id = 1;
UPDATE 1

B> SELECT id FROM accounts WHERE id = 1 FOR KEY SHARE;
 id 
----
  1 
(1 row)

…while the strongest, FOR UPDATE, has to wait for it.

B> SELECT id FROM accounts WHERE id = 1 FOR UPDATE;
⏳ B is waiting for a lock…

A> COMMIT;
COMMIT

⏵ B resumes:
 id 
----
  1 
(1 row)

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

The row locks you didn't know you were taking

Insert a child row, and PostgreSQL locks the parent row for you — with FOR KEY SHARE, so the parent can still be updated, but not deleted or re-keyed. That's not a courtesy: without the lock, the parent could vanish between the FK check and the commit.

A

B

INSERT child→ FK locks parent FOR KEY SHARE

UPDATE parent balance→ allowed (non-key)

DELETE parent→ needs FOR UPDATE → ⏳ waits

COMMIT

⏵ DELETE parent→ needs FOR UPDATE ← 23503 update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"

A> BEGIN;
BEGIN

A> INSERT INTO orders VALUES (1, 1); -- FK check locks customers row 1 FOR KEY SHARE
INSERT 0 1

FOR KEY SHARE doesn't mind non-key updates — B can change the balance.

B> UPDATE customers SET balance = 50 WHERE id = 1;
UPDATE 1

But DELETE needs the strongest row lock (FOR UPDATE) — B has to wait.

B> DELETE FROM customers WHERE id = 1;
⏳ B is waiting for a lock…

A> COMMIT;
COMMIT

A's order is now committed, so B's DELETE resumes — straight into the FK.

⏵ B's blocked statement fails:
ERROR:  23503: update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"

B> SELECT balance FROM customers WHERE id = 1; -- the parent survived, with B's earlier non-key update intact
 balance 
---------
      50 
(1 row)

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

Once you know the implicit locks, most "why is this blocked?" mysteries explain themselves. A plain SELECT takes no row locks, ever — row locks are a writers-only affair. UPDATE takes FOR NO KEY UPDATE, DELETE (or a key-changing UPDATE) takes FOR UPDATE, and an FK insert takes FOR KEY SHARE on the parent. Two share-mode locks coexist, which is what keeps hot parent rows survivable under foreign-key churn.

Two properties of row locks carry over into the next chapters. They live until the transaction ends — there's no unlock statement, so a long transaction means long-held locks. And they're written into the row on disk: SELECT FOR UPDATE causes writes, which is why there's no limit on how many rows you can lock and why they're invisible in pg_locks. The monitoring lesson shows what you see instead, and the MVCC chapter shows the xmax stamp that does the work.

Further reading

• PostgreSQL docs: Row-Level Locks
• PostgreSQL docs: SELECT — The Locking Clause
• The same lesson on MySQL