Part 3 — Changing data › Chapter 24

Concurrency patterns that actually hold

Optimistic vs pessimistic, SELECT … FOR UPDATE SKIP LOCKED, and a work queue in a table done properly.

No advert asked for this

The reference database is built on these. They are also the fastest way to sound senior in an interview.

Optimistic concurrency

🧒 Simple

Two ways to share a book. Pessimistic: take it off the shelf and hold it, so nobody else can even open it while you decide what to write. Optimistic: note which edition you read, go away and think, then come back and say “I want to change page 4 of edition 7” — and if it is now edition 8, you are told, and you look again.

🎓 Professional

A version column, incremented on every write, and every update carrying the version it read. The row count is the answer: one means you won, zero means somebody changed it since you read it, so you reload and either retry or tell the user. It costs one integer column and holds no lock across the user's thinking time — which is why it is the default choice for anything a human edits.

Optimistic concurrency: no lock, no waiting, and the row count is the answer.

-- The reference database's orders table carries a version column for exactly this.
UPDATE orders                                 -- one row, changed in place
SET    status  = 'confirmed',                 -- the change the user asked for…
       version = version + 1                  -- …and the stamp that says "I moved it"
WHERE  id = ? AND version = ?;                -- only if it is still the version we read

-- rows affected = 1  -> we won: committed.
-- rows affected = 0  -> somebody else got there first: 409 Conflict, reload, retry.

Why this beats a lock for anything a human edits

No lock is held across the user's thinking time. A pessimistic lock taken when the form opens is held while somebody goes to lunch.
The check and the write are one statement, so there is no window between them. That is what makes it safe without a lock — not the version column by itself.
The row count is the result. Zero is not an error and must not be treated as one: it means the data moved, and the caller has to re-read and decide, because the engine cannot know whether the two changes conflict in business terms.
version + 1 is computed by the engine from the current row, under its own short-lived lock. Reading the version into the application and writing back a number is the race this pattern exists to remove.

Engine	Idiomatic version column
Any	An `INTEGER` you increment yourself. Portable, obvious, testable
SQL Server	`rowversion` / `timestamp` — maintained automatically, database-unique
PostgreSQL	The system column `xmin` works, and is what Hibernate uses; an explicit column is clearer
Oracle	`ORA_ROWSCN`, with `ROWDEPENDENCIES` enabled on the table

⚠ Trap — a timestamp is not a version

WHERE updated_at = ? looks equivalent and is not: two updates inside the same clock tick produce the same value, and clock resolution is coarser than you think (SQL Server's DATETIME is accurate to about 3 ms). Under load that is a lost update with no error. Use a counter or a real rowversion.

Pessimistic locking, and SKIP LOCKED

Pessimistic locking, and the two modifiers that make it usable in production.

-- Take the lock while READING, and hold it to the end of the transaction.
BEGIN;                                               -- the lock lives as long as this
  SELECT on_hand FROM stock WHERE product_id = ?     -- read the row…
  FOR UPDATE;                                        -- …exclusively: others wait here
  UPDATE stock SET on_hand = on_hand - ?             -- now nobody can have changed it
  WHERE product_id = ?;                              -- the same row, by key
COMMIT;                                              -- the lock is released here, not before

-- The two modifiers that make it usable:
SELECT ... FOR UPDATE NOWAIT;       -- fail immediately rather than queue behind someone
SELECT ... FOR UPDATE SKIP LOCKED;  -- ignore locked rows and take the next free one

What the lock does and does not cover

FOR UPDATE locks the rows the SELECT returned, until COMMIT or ROLLBACK. It does not stop new rows appearing that would have matched — that is a phantom, and it needs SERIALIZABLE (chapter 22).
Without NOWAIT or a lock timeout, a blocked session waits indefinitely by default in some engines. Under load that turns into a queue of connections and looks like the database being down.
SKIP LOCKED changes the meaning of the query: it returns a different set of rows depending on what else is running. That is wrong for a report and exactly right for a work queue.
SQL Server spells these as hints: WITH (UPDLOCK), WITH (UPDLOCK, READPAST).

✔ SKIP LOCKED is the whole reason a queue in a table works

Without it, ten workers polling the same job table all block on the first unclaimed row and you have a one-worker system with nine spectators. With it, each worker takes a different row on the first attempt. PostgreSQL 9.5+, MySQL 8+, Oracle. SQL Server spells it WITH (UPDLOCK, READPAST). It converts “a queue in a database is a bad idea” into “a queue in a database is fine up to a few thousand jobs a second”.

A work queue in a table, done properly

A work queue in a table: claiming exactly one job, atomically, with no race and no double processing.

-- PostgreSQL: claim ONE job atomically.
UPDATE outbox                                 -- claim by WRITING, not by reading
SET    claimed_at = now(),                    -- stamp who took it and when…
       claimed_by = $1,                       -- $1 is a bound parameter: the worker id
       attempts   = attempts + 1              -- …and count the attempt, for poison-message limits
WHERE  id = (                                 -- the subquery picks exactly one id
  SELECT id FROM outbox                     -- …by looking at the queue itself…
  WHERE  published_at IS NULL                 -- not done yet…
    AND (claimed_at IS NULL                   -- …and either never claimed…
      OR claimed_at < now() - INTERVAL '5 minutes')  -- …or claimed by a worker that died
  ORDER  BY created_at                        -- oldest first: fair, and keeps latency bounded
  FOR UPDATE SKIP LOCKED                      -- step over rows other workers hold
  LIMIT  1                                    -- exactly one job
)                                             -- end of the id subquery
RETURNING id, topic, payload;                 -- hand the job back in the same round trip

-- SQL Server: the same idea, with hints instead of SKIP LOCKED.
UPDATE TOP (1) o                              -- one row…
SET    claimed_at = SYSUTCDATETIME(),         -- UTC, at full precision
       claimed_by = @worker,                  -- a parameter again
       attempts   = attempts + 1             -- the same attempt counter
OUTPUT inserted.id, inserted.topic, inserted.payload  -- T-SQL's RETURNING
FROM   outbox o WITH (UPDLOCK, READPAST)      -- lock what we read; skip what others hold
WHERE  o.published_at IS NULL;                -- unpublished jobs only

Every clause here is load-bearing

SKIP LOCKED / READPAST is what makes the queue scale: without it, ten workers all try to claim the same oldest row and nine of them wait. With it, each takes the next free one and throughput rises with worker count.
The claim is a single UPDATE … RETURNING. Select-then-update would leave a window in which two workers claim the same job.
The claimed_at < now() - INTERVAL '5 minutes' branch reclaims jobs from workers that died mid-flight. Without it, one crash loses a message permanently.
attempts is what lets you stop retrying a poison message forever. Add AND attempts < 5 and a dead-letter path.
A table-as-queue is fine into the low thousands of jobs a second. Beyond that the answer is a real broker, and saying so is the right answer in an interview.

Three details that make it production-grade

A claim timeout. A worker that dies holding a claim must not hold the job forever. Reclaim anything claimed more than n minutes ago.
An attempts counter and a dead-letter rule. A message that fails permanently will otherwise be retried until the end of time.
A partial index on exactly the worker's predicate. WHERE published_at IS NULL — the index stays tiny however large the table grows, because published rows leave it entirely.

The transactional outbox

Both writes now succeed together or fail together, because they are the same transaction. Delivery is moved to a worker that can safely retry.

🧒 Simple

You need to save the order and send the confirmation email. Send first and the save might fail — you promised something that did not happen. Save first and the send might fail — it happened and nobody was told. So instead you write the email into a letters to post box in the same notebook, in the same stroke of the pen, and a postman empties the box later.

🎓 Professional

There is no transaction spanning your database and a message broker, so “save and publish” is a dual-write problem with no atomic solution. The outbox converts it into a single local transaction: the row and the message are written together, and a separate worker reads the outbox and publishes. Delivery becomes at-least-once, so consumers must be idempotent (chapter 20) — and that is the honest trade, not a flaw to hide.

The transactional outbox: state change, audit trail and the message to publish, in one transaction.

BEGIN;                                         -- three writes, one unit
  UPDATE orders SET status = 'shipped',        -- the state change…
                    shipped_at = now(),        -- …with its timestamp…
                    version = version + 1      -- …and the optimistic-lock bump
  WHERE id = $1 AND version = $2;              -- only if nobody moved it first
  -- 0 rows -> ROLLBACK and return 409. Check it before continuing.

  INSERT INTO order_events (order_id, from_status, to_status, actor)  -- the audit row…
  VALUES ($1, 'confirmed', 'shipped', $3);                            -- …written here, not later

  INSERT INTO outbox (topic, payload)                                 -- the message to publish…
  VALUES ('order.shipped',                                            -- …its topic…
          json_build_object('orderId', $1)::text);                    -- …and its body, as text
COMMIT;                                        -- all three, or none of them
-- A separate worker publishes from the outbox afterwards, and may publish twice.

The problem this solves, and the honest cost

Publishing to a message broker inside the transaction is impossible to make atomic: the commit can succeed and the publish fail, or the reverse. Writing the message to a table makes it part of the same transaction, and a worker publishes it after.
The cost is at-least-once delivery: the worker can publish and then crash before marking the row published, so the message goes twice. That is not a flaw to hide — it is why consumers must be idempotent (chapter 20).
json_build_object(...)::text — build the JSON in the engine rather than concatenating strings, then cast to text because the column is text. Hand-built JSON breaks the first time an order id needs escaping.
Keep the transaction short. It now contains three writes; adding an HTTP call to it would hold locks for the duration of somebody else's outage.

The last-unit problem

Two customers order the last item at the same moment. Three defences, and the strongest one is not the code:

Three defences against overselling the last item — and the strongest one is not the code.

-- 1. THE CONSTRAINT. Even if every other defence fails, the database refuses.
CONSTRAINT ck_stock_nonneg CHECK (on_hand >= 0)   -- declared once, enforced always

-- 2. An atomic, conditional UPDATE. No read-modify-write, so there is no window.
UPDATE stock                                      -- no SELECT anywhere in this pattern
SET    on_hand = on_hand - :qty                   -- computed by the engine, from the current row
WHERE  product_id = :id                           -- the row…
  AND  on_hand   >= :qty;                         -- …and the condition, in the SAME statement
-- rows affected = 0 -> not enough stock. No SELECT involved, so nothing to race.

-- 3. Only if you must serialise something more complex than one row:
SELECT on_hand FROM stock WHERE product_id = :id FOR UPDATE;   -- take the lock, then decide

Why the order matters

The constraint is the only defence that survives a second application, a manual fix in a SQL console, or a bug in the retry path. Everything above it is optimisation.
Defence 2 removes the race by never reading the value into the application at all: the comparison and the arithmetic happen inside one statement, on one row, under the engine's own lock.
Zero rows affected is the “out of stock” answer, and it arrives without an exception — so the caller can return a clean message instead of parsing an error string.
Defence 3 is a real lock and therefore a real queue. Reach for it when the decision spans several rows or tables, not for a single decrement.

✔ Say this in an interview

“I would make the decrement a single conditional update rather than a read followed by a write, so there is no window to race in, and I would still put a CHECK (on_hand >= 0) on the table — because the constraint is the only defence that holds when somebody writes a second application against the same database.”

The constraint refusing to be talked round, and the conditional update that simply declines.

-- The blunt version: the database refuses outright.
UPDATE stock SET on_hand = on_hand - 1000 WHERE product_id = 1;  -- more than we have
-- CHECK constraint failed: ck_stock_nonneg      ← an error, and the row is unchanged

-- The conditional form, which declines politely instead:
UPDATE stock SET on_hand = on_hand - 1000                        -- the same arithmetic…
WHERE product_id = 1 AND on_hand >= 1000;                        -- …guarded by the condition
SELECT changes();   -- 0. No error, no oversell, and the caller can say why.

Two correct outcomes, one of which is nicer to the user

Both prevent the oversell. The difference is whether the application handles an exception or reads a row count — and row counts are far easier to turn into a good error message.
Keep the constraint anyway. The conditional update is the polite path; the constraint is the one that still works when somebody writes a second application against the same database.
changes() is SQLite; T-SQL is @@ROWCOUNT, PostgreSQL exposes it through the driver as the command tag.

Optimistic concurrency: a version column and WHERE version = ?. Zero rows affected is the conflict signal.
A timestamp is not a version. Two updates in one clock tick share a value.
Pessimistic locking is correct and costs concurrency. Never hold a lock across a user's thinking time.
SKIP LOCKED is what makes a queue-in-a-table work with more than one worker.
A production queue needs a claim timeout, an attempts counter and a partial index on the worker's predicate.
There is no transaction across a database and a broker. The outbox turns the dual write into one local transaction.
Outbox delivery is at-least-once, so consumers must be idempotent. Say so rather than implying exactly-once.
Prefer a single conditional update to read-modify-write: there is no window to race in.
Back every concurrency pattern with a constraint. It is the only defence that survives a second application.

Interview questions this chapter answers

Optimistic or pessimistic locking?

Optimistic where conflicts are rare and the edit involves a human — a version column costs nothing and holds no lock across thinking time. Pessimistic where a conflict is likely and retrying is expensive, or where I must serialise a multi-step operation. For a simple decrement I would use neither: a single conditional update has no window at all.

How do you stop two customers buying the last item?

Make the decrement one atomic conditional statement — UPDATE … SET on_hand = on_hand - :q WHERE id = :id AND on_hand >= :q — and treat zero rows affected as "out of stock". Then add CHECK (on_hand >= 0) so the invariant holds even if somebody later writes different code against the same table.

How do you publish an event reliably when you save a record?

A transactional outbox: write the domain change and the message into the same database transaction, and have a worker read the outbox and publish. It removes the dual-write problem at the cost of at-least-once delivery, so consumers must be idempotent — usually a unique constraint on the message id.