Locks & optimistic concurrency

The MVCC lesson ended on a promise and a caveat. The promise: readers never block writers and writers never block readers, because they work on different physical row versions. The caveat, stated once and then set aside: two writers to the same row do block each other. This lesson is about that caveat — the concurrency that isn't free — because it's where real application bugs live. Not slow bugs; wrong bugs. Money computed incorrectly, a debt paid twice, an update that silently vanishes.

The unifying question is simple: when two transactions want to change the same thing, who wins, who waits, and who finds out they lost?

The bug that motivates everything: the lost update

Start with a race you have almost certainly written. Two requests both settle part of the same debt at the same time. Each does the obvious thing:

-- Both transactions run this, interleaved:
SELECT outstanding FROM debts WHERE id = 'd1';   -- both read 1000
-- ...app computes new value in application code...
UPDATE debts SET outstanding = 800 WHERE id = 'd1';  -- T1 writes 800
UPDATE debts SET outstanding = 700 WHERE id = 'd1';  -- T2 writes 700

Both read 1000. T1 subtracts 200 and writes 800. T2 — which also read 1000, before T1's write — subtracts 300 and writes 700. The final value is 700, but the correct answer after two payments totalling 500 is 500. T1's payment was silently erased. This is a lost update, and notice MVCC did not save you: each statement was individually fine, the reads didn't block the writes, and no error was raised. The gap is the window between the read and the write, where each transaction acted on a value that another transaction was busy invalidating. Every concurrency-control mechanism below exists to close that window.

Step the two transactions under each strategy. With No control you watch the −200 vanish; Pessimistic makes T2 wait for the lock and read the fresh value; Optimistic lets T2's compare-and-swap fail, then retry. All three end at ₹400 except the first.

What a lock physically is

The blunt fix is a lock: a transaction claims the right to a row, and anyone else who wants that right waits until the first transaction ends (commit or rollback). Postgres has several granularities, but the one that matters here is the row-level lock.

A crucial implementation detail: Postgres does not keep row locks in a big in-memory lock table (that would need memory proportional to the number of locked rows, and a busy transaction can lock millions). Instead a row's "I am locked for writing" state is recorded largely in the tuple itself — the xmax field and infomask bits from the pages lesson double as the lock holder. That's why Postgres can lock an unlimited number of rows cheaply: the bookkeeping rides along in data it was already writing. (Table-level locks and a handful of others do live in a shared lock table, which is why those are a finite resource — max_locks_per_transaction.)

Pessimistic concurrency: lock first, then work

The first philosophy is pessimistic: assume a conflict will happen, so take the lock before you touch the value. You do this with SELECT ... FOR UPDATE:

BEGIN;
SELECT outstanding FROM debts WHERE id = 'd1' FOR UPDATE;  -- locks the row
-- any other FOR UPDATE on d1 now WAITS here
UPDATE debts SET outstanding = outstanding - 200 WHERE id = 'd1';
COMMIT;  -- lock released, the waiter now proceeds and reads 800, not 1000

FOR UPDATE takes an exclusive row lock at read time. The second transaction's SELECT ... FOR UPDATE blocks until the first commits, and only then reads — so it sees 800 and correctly computes 600. The lost update is gone because the read-modify-write is now serialized on that row. Postgres offers a ladder of lock strengths for finer control — FOR UPDATE (I'll modify or delete this), FOR NO KEY UPDATE (I'll modify a non-key column — weaker, conflicts less), FOR SHARE (others may read-lock too but nobody may write), FOR KEY SHARE (just pin the key, e.g. so a foreign-key parent isn't deleted) — but the mental model is binary: a lock you take because you intend to change the row, and everyone else with the same intent queues behind you.

Pessimistic locking is correct and simple. Its cost is exactly the queue: under heavy contention on a hot row, transactions pile up waiting, and your throughput on that row drops to "one transaction at a time." When conflicts are common, that's the price of correctness. When conflicts are rare, you've made everyone wait for a collision that almost never happens — which motivates the other philosophy.

Optimistic concurrency: don't lock, check at the end, retry

The second philosophy is optimistic: assume conflicts are rare, so don't lock. Read the value and a version marker; do your work unlocked; at write time, atomically check the version hasn't changed. If it has, someone beat you — abort and retry.

SELECT outstanding, version FROM debts WHERE id = 'd1';  -- outstanding=1000, version=7
-- ...work, no lock held...
UPDATE debts SET outstanding = 800, version = 8
  WHERE id = 'd1' AND version = 7;   -- compare-and-swap
-- if this UPDATE reports 0 rows changed, someone else already bumped version
-- past 7 → your read was stale → re-read and retry the whole thing.

The WHERE version = 7 is a compare-and-swap: the update only lands if the row is still on the version you read. The loser's UPDATE matches zero rows, the app sees "0 rows affected," and retries from a fresh read. No locks are ever held, so under low contention this is faster than pessimistic locking — nobody waits, everybody just occasionally retries. Under high contention it degrades badly (constant collisions mean constant retries — wasted work), which is the exact mirror image of pessimistic's trade-off.

This is not just an application pattern — it's what Serializable isolation does internally. As the isolation lesson covered, Postgres's Serializable (SSI) tracks the read/write dependencies between concurrent transactions and, if it detects a dangerous cycle that would violate serial ordering, aborts one transaction with a serialization error — expecting you to retry. Serializable is optimistic concurrency, generalized to whole transactions and enforced by the database instead of a hand-written version column. The corollary: any code using Serializable must be prepared to catch a serialization failure and retry the transaction, or the guarantee does you no good.

Advisory locks: locking things that aren't rows

Sometimes the thing you need to serialize isn't a single row — it's a logical operation spanning several rows, or something with no row at all ("only one worker may run this reconciliation at a time"). Postgres offers advisory locks: application-defined locks keyed by an arbitrary integer, that the database tracks but attaches no meaning to. You decide what the key means.

The most useful form is pg_advisory_xact_lock(key) — a transaction-scoped advisory lock that is automatically released when the transaction ends (so you can never leak it by forgetting to unlock). Two transactions that call it with the same key serialize: the second waits until the first's transaction finishes. It's a mutex whose name is a number you compute, and it's how you enforce "one at a time" over a concept the schema doesn't have a single row for.

The queue trick: FOR UPDATE SKIP LOCKED

One more pattern worth knowing because it's so useful and so non-obvious. Suppose you want to use a table as a job queue: many workers, each should grab a job nobody else is working on. Plain SELECT ... FOR UPDATE LIMIT 1 fails — every worker locks the same top row and they serialize into a single-file line. The fix is one clause:

SELECT * FROM jobs WHERE status = 'pending'
  ORDER BY created_at LIMIT 1
  FOR UPDATE SKIP LOCKED;   -- skip rows other workers already locked

SKIP LOCKED tells Postgres: instead of waiting for a locked row, ignore it and move to the next unlocked one. Now ten workers each grab ten different jobs with no coordination and no waiting — a correct concurrent work queue in a single statement, no separate broker required. (There's also NOWAIT, which errors instead of skipping — useful when "someone else has it" should be a failure, not a skip.)