Two SKIP LOCKED implementations compared — outbox relay vs notification drain

You shipped the pattern twice with different claim semantics. The comparison is a design conversation in miniature:

Why the dialects differ — the external-I/O rule: the relay's work (publish to RabbitMQ) is fast (socket write) → it can afford to hold row locks through the operation, giving exactly-once claiming. The drain's work (APNs push, seconds) must never hold row locks — locks held across external I/O serialize all drains and leak connections — so it claims with time instead, accepting bounded double-sends. That one rule — locks end where network calls begin — is the portable lesson, and you own two exhibits.

The drain's claim, annotated (Day 52's SQL): note it does the lease-grant in the UPDATE (attempts+1, next_attempt_at+120 s) — atomic increment-and-claim; the subselect with SKIP LOCKED just selects eligible rows without blocking competing drains. Backoff and claim share one statement: retry state is write-once per attempt.

The relay's claim, annotated: SELECT ... FOR UPDATE SKIP LOCKED (locks held) → publish each → UPDATE published_at=NOW() WHERE id=ANY(claimed) → commit. Failure mid-batch: transaction aborts, nothing marked published, next tick retries the whole batch — at-least-once to the broker, deduped downstream (Day 48's chain). Note the design asymmetry with the drain: the relay can retry batches atomically because its operation is idempotent-cheap; the drain retries rows individually because sends have per-row terminal outcomes.

The metrics each exposes (queue lag, Day 61): relay → outbox depth/age query (partial index makes it free); drain → getJobCounts() + delivery rows by status — the SQL visibility of both is a shared design win.

The generalization (the exam answer)

"There are two safe ways to claim work in Postgres: transaction-scoped SKIP LOCKED for fast/idempotent operations (lock = claim; crash = rollback; exactly-once claiming) and forward-leased claims for slow/external operations (time = claim; crash = lease expiry; at-least-once execution, idempotency required). I've production- shipped both dialects — outbox relay and notification drain — and chose per the locks-never-cross-I/O rule."

Trace the two failures, step by step

The table is the summary; these are the traces an interviewer actually wants.

Relay crash, mid-batch. Tick fires at T. The relay opens a transaction, SELECT ... FOR UPDATE SKIP LOCKED returns 100 unpublished rows, and it publishes rows 1–40 to RabbitMQ before the pod is killed at T+300 ms. What happens:

• The transaction never commits, so published_at is never set for any of the 100 rows. Postgres rolls back on connection death and the row locks evaporate.
• The 40 already-published messages are in RabbitMQ — publishing is not transactional with the database, and it cannot be without a distributed transaction.
• At T+5 s the next tick (or the restarted pod) claims the same 100 rows and republishes all of them, including the 40 duplicates.
• The result is at-least-once delivery to the broker, which is correct by design: consumers dedup on event_id (Day 48's chain). The outbox does not promise exactly-once publishing; it promises no lost events, and it delivers that because a message cannot exist without the row, and the row cannot be marked published without a commit.

The one thing that breaks this is publishing outside the transaction scope — commit first, publish after. Then a crash between commit and publish loses the event permanently. That is the classic dual-write bug, and it is why the relay's FOR UPDATE scope is deliberately wide.

Drain crash, mid-send. A drain claims 50 rows, granting each a 120-second lease (attempts = attempts + 1, next_attempt_at = NOW() + 120s) in the same statement that selects them. It sends 12 pushes, then dies at T+4 s.

• The 12 sends happened; the ledger rows still read "claimed" with next_attempt_at in the future. They were never marked sent, because the process that would have marked them is gone.
• Nothing is stuck: the lease is a timestamp, not a lock, so no connection is held and no competing drain is blocked. At T+120 s those rows become eligible and are re-claimed.
• The 12 users may receive a duplicate push. Bounded duplicate spam is the accepted price of never holding a database lock across an APNs call — and apns-collapse-id plus the row-level coalesceKey (Day 74) often make the duplicate invisible on the device anyway.
• Terminal outcomes are preserved: rows already marked failed terminally or sent before the crash are not re-claimed, because the eligibility predicate excludes terminal statuses.

The asymmetry in one sentence: the relay retries batches atomically because publishing is fast and idempotent-cheap; the drain retries rows individually because sends have per-row terminal outcomes and take seconds. Same underlying rule, two dialects — locks end where network calls begin.

The third dialect you have not built: leases need fences

There is a subtle hole in any lease-based claim, and naming it unprompted is a strong signal. A worker whose lease has expired may still be alive and mid-operation — a zombie. It finishes, writes its result, and that write can clobber the state produced by the worker that legitimately re-claimed the row after the lease expired.

The standard fix is a fencing token: a monotonically increasing number issued with each claim (a per-row claim_version, or a global sequence). Every write the worker performs carries the token it was issued and is conditional — UPDATE ... WHERE id = ? AND claim_version = ?. The zombie's write matches zero rows and is discarded. This is the same mechanism as the CAS conditional transition in Zoth's state machine (Day 32): the version column is what makes a time-based claim safe, because time alone cannot tell you whether the previous holder is actually dead.

Traverse's drain has the lease but not the fence, so a slow worker that outlives its 120-second lease can double-write a delivery status. At current volume the practical impact is a duplicate push or a corrected status. The honest statement: "the lease bounds the damage, and a claim-version column is the fix I would add before raising drain concurrency." Knowing the gap and its fix is worth more than a design that looks airtight and is not.