Row locks

Ordinary MVCC SELECTs do not conflict with row locks. Locking SELECTs, UPDATEs and DELETEs can conflict on the same row. Table locks, functions called by a query, and other resources can still cause a read to wait. This chapter uses PostgreSQL 18; the linked transcripts state the executed patch version.

An ordinary SELECT succeeds while UPDATE waits

Here B's ordinary SELECT returns 100 while A holds FOR UPDATE; B's UPDATE waits, then uses A's committed 150 and leaves 140. FOR UPDATE also conflicts with other sessions' locking reads.

A

B

SELECT … FOR UPDATE (locks the row)

ordinary SELECT→ 100

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)

B's ordinary SELECT reads the row without a conflicting row-lock request; cost is not measured.

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.6 · Run it yourself · Scenario source

The four modes

This complete compatibility table is a Documented contract, Table 13.3, not sixteen executed combinations. ✅ means compatible between different transactions; ⛔ means conflicting requests on the same row.

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	⛔	⛔	⛔	⛔

The row-lock contract specifies FOR UPDATE for DELETE and UPDATEs of certain key columns: currently columns with a unique index usable for a foreign key, excluding partial and expression indexes. Other UPDATEs take FOR NO KEY UPDATE. Do not treat every indexed-column update as key-changing for this rule.

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.6 · Run it yourself · Scenario source

This schedule executes SHARE/SHARE coexistence, SHARE blocking a non-key UPDATE, KEY SHARE coexisting with that UPDATE, and FOR UPDATE waiting for it. It does not execute the entire table.

The row locks you didn't know you were taking

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.6 · Run it yourself · Scenario source

The immediate foreign-key insert in this setup allows B's non-key update while its parent DELETE waits and later fails with 23503. That conflict signature is consistent with KEY SHARE; the scenario does not directly decode the lock mode. PostgreSQL 18's foreign-key implementation uses a FOR KEY SHARE lookup for this check. Deferred checks and other actions are not executed here.

Row locks normally remain until transaction end, but rollback to a savepoint releases locks acquired since it. Row locking can write tuple headers to disk. Ordinary row locks normally do not appear in pg_locks; the monitoring lesson shows the wait representation. SELECT without a locking clause does not by itself acquire these row-lock modes, but called functions can take locks.

Further reading

• PostgreSQL 18: Row-Level Locks
• PostgreSQL 18: SELECT locking clause
• The same lesson on MySQL