Lesson 19 · Transactions & concurrency

Locks & deadlocks

MVCC means reads don't lock — but writes do. What can go wrong, and why.

Your win: explain what locks writes take, what SELECT … FOR UPDATE is for, and how a deadlock forms and gets resolved.

Reads don't lock; writes do

The MVCC payoff (Lesson 6): a plain SELECT takes no row locks — it reads a snapshot. But writes lock: an UPDATE/DELETE takes a row-level lock, so two writers to the same row serialize (the second waits for the first to commit).1

SELECT … FOR UPDATE Sometimes you read a row intending to update it, and must stop anyone else changing it in between. SELECT … FOR UPDATE locks the matched rows now, so a concurrent writer blocks until you commit. FOR SHARE is the weaker "don't let it change, but others may also share-read" variant. Table-level locks (from DDL / LOCK TABLE) exist but are rarer.

Deadlocks: a cycle of waits

Txn A: locks row 1 … now wants row 2 ─┐ │ each waits on the other → cycle Txn B: locks row 2 … now wants row 1 ─┘

Neither can proceed. Postgres's deadlock detector spots the cycle, picks a victim, and aborts it with a deadlock error; the other transaction continues. Your app retries the victim.2

The fix is ordering Deadlocks almost always come from acquiring locks in different orders. If every code path locks rows/tables in the same consistent order, the cycle can't form. That's the interview answer: "prevent by consistent lock ordering; handle the rest by retrying the aborted transaction."
Anchor Our layer exposes WithUpdateLock() / WithShareLock() that append FOR UPDATE / FOR SHARE to a query (internal/golibs/database/db.go:136). For coordination outside row locking, there are advisory locks — pg_try_advisory_lock(hashtext($1)) (internal/golibs/database/pg_advisory_lock.go) — a named, app-defined lock (e.g. "only one worker runs this job at a time").
Read this next

PostgreSQL — Explicit Locking

Row- and table-level lock modes, FOR UPDATE/FOR SHARE, advisory locks, and how the deadlock detector works.

→ postgresql.org/docs — Explicit Locking

Check yourself (from memory)

Q1. Under MVCC, a plain SELECT…

Reads use a snapshot — no locks. Writes take row locks; same-row writers serialize.

Q2. SELECT … FOR UPDATE is used to…

It locks the matched rows so no one else changes them before you commit — the guard against lost updates (Lesson 20).

Q3. When Postgres detects a deadlock it…

The detector picks a victim and rolls it back with a deadlock error; retry it. Prevent by consistent lock ordering.
How do locks work under MVCC, and how does Postgres handle a deadlock?
recall, then click to reveal
READS take no locks (snapshot). WRITES do: an UPDATE/DELETE takes a row lock, so two writers to the same row serialize. SELECT … FOR UPDATE explicitly locks rows you're about to change (FOR SHARE is weaker); table-level locks are rarer. A DEADLOCK is a cycle — A holds lock 1 wanting 2, B holds 2 wanting 1. Postgres's deadlock detector aborts one (the victim) with a deadlock error so the other proceeds; the app retries. Prevent by acquiring locks in a consistent order. Our repo has WithUpdateLock()/WithShareLock() and advisory locks (pg_try_advisory_lock).
Want the difference between advisory locks and row locks, or NOWAIT / SKIP LOCKED for queue-style workers? Ask me.

1. PostgreSQL — Explicit Locking.

2. PostgreSQL — Deadlocks.