SELECT FOR UPDATE and the job queue

The lost-update race, and the pessimistic fix

Before you reach for FOR UPDATE, ask yourself: am I reading a value and then writing a decision based on it? If yes, and if another session could do the same thing concurrently, you have a lost-update window right now.

The default READ COMMITTED level does not serialize a read-then-write done in two statements. Two sessions can both read a balance, both compute a new value off the stale read, and both write — the second silently overwrites the first. That is the lost update.

-- Session 1 and Session 2 both run this, interleaved:
SELECT balance FROM accounts WHERE id = 1;   -- both read 500
-- app computes 500 - 100 = 400
UPDATE accounts SET balance = 400 WHERE id = 1;  -- both write 400; one debit vanishes

SELECT ... FOR UPDATE fixes it pessimistically: it takes a row-level write lock on every row it returns. A second transaction that tries to lock the same row blocks until the first commits, then proceeds against the now-current data.

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- locks the row
-- only this transaction holds it; others wait here
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- lock releases; the next waiter re-reads the fresh balance

The lock lives for the rest of the transaction and releases on COMMIT or ROLLBACK. The cost is serialization: contended rows process one transaction at a time. That’s the point — correctness over throughput on the hot row.

The four lock strengths

Postgres has four row-lock modes, from strongest to weakest. Picking the weakest one that’s correct lets more concurrency through.

• FOR UPDATE — full write lock. Blocks any other FOR UPDATE/FOR SHARE and blocks UPDATE/DELETE of the row. Use when you will modify the row.
• FOR NO KEY UPDATE — slightly weaker; taken automatically by a plain UPDATE that doesn’t touch a key column. It does not block FOR KEY SHARE, which is what foreign-key checks take — so it reduces FK-related contention.
• FOR SHARE — shared read lock. Multiple transactions can hold it; it blocks writers. Use to pin a row you’re reading but not changing.
• FOR KEY SHARE — weakest; what a foreign-key existence check acquires on the parent row. It only blocks changes to the row’s key.

The practical lesson: a child-table INSERT takes FOR KEY SHARE on the parent, and a parent UPDATE of a non-key column takes FOR NO KEY UPDATE — these two were made compatible so ordinary FK traffic doesn’t serialize. Reach for plain FOR UPDATE only when you truly intend to update the locked row.

SKIP LOCKED: the canonical concurrent queue

SKIP LOCKED changes the wait behaviour: instead of blocking on an already-locked row, the query skips it and returns the next available one. That is exactly what a job queue needs — each worker grabs a different unlocked job.

-- One worker's atomic dequeue: claim the oldest free job, skipping any a peer holds.
BEGIN;
SELECT id FROM jobs
WHERE status = 'queued'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;
-- mark the claimed row so it won't be re-selected after commit:
UPDATE jobs SET status = 'running', locked_at = now() WHERE id = :claimed_id;
COMMIT;

Ten workers running this in parallel each lock and claim a distinct row; no duplicates, no blocking, near-linear scaling. This is how Sidekiq-Pro, pg-boss, Que, and most modern Postgres queues work — no separate broker, just FOR UPDATE SKIP LOCKED.