SQL locking cheat sheet

Start here: most of the time you need none of this

A single SQL statement is already atomic. This needs no lock clause from you, because Postgres takes a row lock internally for the duration of the statement and the guard either matches or it does not:

UPDATE accounts SET balance = balance - 20
 WHERE id = :a AND balance >= 20

Rows affected = 0 is the rejection. That is the whole mechanism behind the fused guard in devex-payouts and the window fence in like-counter, neither of which writes FOR UPDATE anywhere.

You reach for explicit row locking when you must hold a row across more than one statement in a transaction: read it, decide something, then write based on what you read. That read-to-write gap is the thing the lock protects, and it is the only reason these clauses exist.

The four row lock strengths

All four are written SELECT ... FROM t WHERE ... FOR <mode> and all four last until the transaction ends. Weakest to strongest:

The two you will actually type are FOR SHARE and FOR UPDATE. The other two mostly explain why an UPDATE sometimes blocks on a foreign key you had forgotten about.

The conflict matrix

Read it as: someone already holds the row lock in the left column, you now ask for the one across the top.

FOR SHARE as a fence

The interesting use of FOR SHARE is not reading, it is asserting that something stays true. In the game-matching claim transaction:

BEGIN;
SELECT 1 FROM leases WHERE partition = :p AND epoch = :my_epoch FOR SHARE;
-- zero rows means we are a zombie: ROLLBACK
...the claim writes...
COMMIT;

A takeover bumps that epoch with an UPDATE, which conflicts with this FOR SHARE, so the stale owner and the new owner cannot both commit. A plain SELECT there would read a snapshot and prove nothing.

The two modifiers

These attach to FOR UPDATE or FOR SHARE and change what happens when the row you want is already locked. The default is to wait indefinitely.

SKIP LOCKED is what turns a table into a work queue, which is the delayed-payments poller exactly. The cost is the part to say out loud before an interviewer says it: skipping locked rows quietly abandons strict ordering. Nobody cares in a payments queue. It is fatal in a matchmaking queue whose product promise is join order, which is why game-matching names it and declines it.

Deadlocks, and the one rule that prevents them

Two transactions each holding a row the other wants deadlock, and Postgres kills one with error 40P01. Prevention is boring and absolute: acquire locks in a consistent global order everywhere in the codebase. Wallet does this by locking min(A,B) then max(A,B) so a transfer in either direction takes the same path.

Advisory locks

Locks on a number you invent, attached to no row. Useful when the thing to serialise is not a row: "only one instance of this job at a time", or "serialise everything touching user 12345" without holding real rows.

Distributed locks, and why a CAS usually beats one

For a single row in a single database you do not need a distributed lock. The CAS is strictly better, and reaching for Redis there is cargo cult. Four situations are where a lock genuinely earns its place.

1. The resource cannot compare anything

A CAS needs a WHERE clause to live in. An S3 object, a filesystem, a printer, a third-party payment gateway: there is no state to hang a condition on. That is the case fencing tokens were invented for, and they only work if the resource compares the token, which is resource-side participation all over again. If it genuinely cannot compare anything, no lock saves you and the honest move is idempotency at the far side. That is exactly why delayed-payments keys the gateway call by paymentId instead of trying to fence it.

2. The critical section spans more than one row

A CAS guards one row. If the unit of work is "read 10,000 rows, compute a plan, write to three tables and publish to Kafka", per-row CAS gives you no mutual exclusion over the job. Every individual write can pass its own guard while two workers still duplicate the entire computation and interleave their outputs. A lock is a claim about the whole critical section, which no single-row guard can make.

3. Efficiency, which is a different goal from correctness

This is the distinction most people skip. Locks get used for two unrelated reasons:

With 50 workers and an expensive job, a lock stops 49 of them burning CPU on work that would lose the CAS anyway. That is a throughput argument, not a safety one. CAS is optimistic (everyone works, losers retry and discard) and a lock is pessimistic (one works). When the work before the write is expensive and contention is high, optimism gets expensive.

4. There is no distinguishable "before" value to CAS on

CAS-on-state works when a write is a one-shot transition out of a known state: WHERE status = 'WAITING' works precisely because WAITING happens once and is consumed. When the same owner legitimately writes the same row over and over, there is no stale prior value left to test against, and a zombie's write looks structurally identical to a legitimate one. That is when you add a monotonic column and compare against it instead of against the data.

They are the same machinery pointed at different columns. The like-counter fence is exactly this: you cannot CAS on the count, because the count is supposed to keep changing, so it fences on a monotonic window id instead.

And the punchline for these designs: if you already have the database, you do not need Redis at all. A lease row with an epoch column gives you mutual exclusion, a monotonic token, and, uniquely, validation in the same transaction as the write. That is what game-matching does, and it is strictly stronger than an external lock plus fencing, because with an external lock the check and the write can never be atomic.

Table-level locks, and why your migration caused an outage

You rarely write LOCK TABLE yourself. These matter because DDL takes them.

The failure mode to be able to describe: an ACCESS EXCLUSIVE request queues behind whatever long transaction is already running, and every new query then queues behind it. That is how a migration that "takes 2ms" produces a five minute outage. Defences: CREATE INDEX CONCURRENTLY, and set a short lock_timeout before DDL so the migration fails fast instead of forming a queue.

The other axis: isolation levels

Locking is one way to prevent anomalies. Isolation level is the other, and they trade off against each other.

SERIALIZABLE can replace most explicit locking, but it does not delete the work, it moves it into a retry loop you have to actually write. Worth naming as the alternative you considered and declined.

If the interview is MySQL

• FOR UPDATE and FOR SHARE both exist; older MySQL spelled the latter LOCK IN SHARE MODE. SKIP LOCKED and NOWAIT arrived in 8.0.
• InnoDB defaults to REPEATABLE READ, not READ COMMITTED.
• InnoDB takes gap locks and next-key locks that lock the ranges between index entries, not only the rows you matched. A query that locks "no rows" can still block an insert into that range, so deadlock patterns appear that Postgres would never produce.

Where each of these already lives in your own designs

Ninety second recall

• One statement with a WHERE guard needs no lock. Rows affected = 0 is the rejection.
• Row locks exist for the read-to-write gap inside one transaction, and last until commit.
• FOR SHARE = nobody may change this. FOR UPDATE = nobody may touch this. Share blocks update; update blocks everything.
• Plain SELECT is never blocked by any of them.
• SKIP LOCKED for queues, and say the ordering cost out loud. NOWAIT when hanging is worse than failing.
• Deadlock prevention is one consistent global lock order, and ORDER BY does not give you one.
• Advisory locks when the thing to serialise is not a row; prefer the _xact_ variant.
• ACCESS EXCLUSIVE from DDL blocks reads too, which is the migration outage.
• SERIALIZABLE replaces locking with a retry loop you must write.
• A distributed lock never makes a write correct, it only stops workers racing to lose the same CAS. Ask "can the resource reject a stale write itself?" first.
• CAS on state for one-shot transitions, a monotonic column for rows written repeatedly, idempotency when the resource cannot compare at all.