FOR UPDATE SKIP LOCKED Was Not Enough: A Stale Failure Write Race in an Inbox Processor

Ezra Wu — Tue, 11 Aug 2026 04:06:35 +0000

Why a post-rollback failure transition needs its own serialization boundary.

I found this while auditing a reliability claim in a small inbox processor.

The processor used PostgreSQL, multiple worker instances, and FOR UPDATE SKIP LOCKED. On its normal path, one worker claimed a row, applied a local database effect, set processed_on_utc, and committed everything in a single transaction.

That path was correct.

The problem appeared after a failed attempt.

Worker A could fail and roll back, releasing its row lock. Worker B could then claim the same row, apply the effect, mark it processed, and commit successfully. After that, A could record its earlier failure in a separate transaction and overwrite B's visible status with retrying or dead_letter.

The local database effect was still applied once. The corruption was in retry state, dead-letter state, and the metrics operators would use during an incident.

In this article, “exactly once” refers only to the local database effect committed in the same transaction as processed_on_utc. It does not cover external effects such as HTTP calls, email, payments, or webhooks.

The path we had reviewed

The inbox table represented messages that had been received but not yet fully applied. A worker selected one eligible row with a query conceptually like this:

SELECT ...
WHERE id = ?
  AND processed_on_utc IS NULL
FOR UPDATE SKIP LOCKED

Once a worker held the row lock, it ran the dispatcher and committed the local database effect together with:

processed_on_utc = now()
status = 'processed'

The expected behavior was:

another worker could not claim the same row while the first worker held the lock;
duplicate delivery still produced one inbox row;
the local database effect and processed marker either committed together or both rolled back.

The existing tests covered those cases. A second worker skipped a row already claimed by the first worker. Duplicate delivery produced one inbox record and one local effect.

At that point, the processor was described as applying its local effect exactly once and avoiding spurious failure state.

The local-effect part was supported by the implementation. The spurious-failure part was not.

The failure path used a different transaction

When dispatch succeeded, the worker completed the row in the transaction that held the SKIP LOCKED claim:

claim row
  -> dispatch
  -> write local database effect
  -> set processed_on_utc and status = 'processed'
  -> commit

When dispatch threw, that transaction rolled back and released the row lock.

Afterward, the processor called RecordFailureAsync in a separate transaction. Before the fix, it effectively did this:

read row by id
  -> increment retry_count
  -> set status = 'retrying' or 'dead_letter'
  -> commit

That second transaction re-read the row, but it did not re-lock it and did not validate whether a failure transition was still valid for the row's current state.

In particular, it did not check whether another worker had already set processed_on_utc.

The stale part was not necessarily the database read.

The stale part was the decision carried forward from the earlier failed attempt:

This attempt failed, so the row should move to retrying or dead-letter.

That decision could become invalid before the failure recorder wrote anything.

The interleaving

The race required two legitimate worker instances, A and B.

A claims the inbox row
A dispatch throws
A rolls back

At this point, A no longer held the lock. The row still had processed_on_utc IS NULL, so another worker could claim it.

B claims the same row
B dispatches successfully
B writes the local database effect
B marks the row processed
B commits

The row was now complete.

Then A resumed its failure bookkeeping:

A opens RecordFailureAsync
A reads the row by id
A does not lock the row or check processed_on_utc
A writes retrying or dead_letter
A increments retry_count
A commits

In the deterministic reproduction, B had already committed processed before A entered RecordFailureAsync.

A did not need an outdated snapshot to cause the bug. Even after another transaction had completed the row, the failure recorder still applied a retry or dead-letter transition without checking whether that transition remained valid.

The final row could have:

processed_on_utc IS NOT NULL
status = 'retrying'

or:

processed_on_utc IS NOT NULL
status = 'dead_letter'

What this did and did not break

The local database effect was not applied more than once.

The claim query required processed_on_utc IS NULL. Once B had committed the processed marker, future apply attempts could no longer claim the row. A's failure recorder changed retry state, but it did not clear processed_on_utc.

So this was not a duplicate-execution bug.

The corruption was in the state used to operate and observe the system:

A successfully completed message could appear to be retrying.
The processed counter could be incremented by B while the retry or dead-letter counter was incremented by A for the same message.
A message that had completed successfully could receive a dead-letter record if A had already exhausted its retry budget.
A due-message scan could keep selecting a row whose visible status was retrying, while the actual claim query rejected it because it had already been processed.
An operator could investigate or reprocess work that was never actually unresolved.

Retry state, dead-letter records, and metrics are operational inputs. Once they can no longer distinguish unfinished work from completed work, they become misleading during an incident.

Why the original concurrency tests did not catch it

The existing concurrency test verified that worker B could not claim the row while worker A still held the lock.

That is useful, but it covers only the apply transaction.

The relevant interval was later:

A rollback
  -> lock released
  -> B claims and completes the row
  -> A records failure in a new transaction

A test that only proves SKIP LOCKED prevents simultaneous claims does not exercise this gap.

The processor had two concurrency-sensitive state transitions:

apply the message effect and mark the row processed;
record a failed attempt and move the row toward retry or dead-letter.

Only the first transition had been treated as a state transition that needed serialization.

Making the race deterministic

I did not want a test that depended on scheduler timing.

In the normal implementation, rollback and failure recording happened back-to-back. There was no convenient await between them that would reliably let another worker run.

I added a small internal test seam named:

AfterRollbackBeforeRecordFailureForTests

It is a no-op in production. The test uses it after A's transaction has rolled back but before A enters RecordFailureAsync.

The test sequence became deterministic:

1. A claims the row and dispatch fails.
2. A rolls back, releasing the lock.
3. The test seam lets B claim the row.
4. B applies the local database effect and commits processed state.
5. A resumes and records its failure.

The regression test verifies that after the full sequence:

the local database effect exists once;
the inbox row remains processed;
processed_on_utc remains set;
retry_count remains 0;
no dead-letter row exists.

On the pre-fix implementation, the final status was retrying rather than processed.

The failing version was committed intentionally as 6bd3018, so the test can still be checked out and reproduced. The fix followed in 7d37540.

The fix

RecordFailureAsync needed to treat its write as a new state transition, not as an unconditional continuation of the earlier failed attempt.

The fixed failure path opens its own transaction and re-claims the row with a blocking FOR UPDATE.

Conceptually:

begin transaction

select row for update

if processed_on_utc is not null:
    commit no-op
    return

increment retry_count

if retry limit reached:
    move to dead-letter
else:
    schedule retry

commit

The choice of a blocking FOR UPDATE is deliberate.

If another worker is still mid-apply, the failure recorder should wait for that worker to commit or roll back, then observe the row's final state. Using SKIP LOCKED again would leave the failure recorder unable to determine whether the row was still unresolved or merely being processed elsewhere.

If another worker has already marked the row processed, failure recording becomes a no-op.

This re-claim is intentionally short-lived in this reference. It protects only the row-local failure transition after dispatch has returned. The shipped dispatcher models a same-database local effect; this is not a recommendation to keep database transactions open across network I/O.

A production adaptation that performs long-running external work would need separate bounds for worker concurrency, lock-wait time, failure recovery, and connection-pool capacity. Adding a lock timeout may be appropriate in such a system, but it does not remove the need to define what retries a failed failure-recording transition.

A conditional update is another valid implementation

The explicit re-claim is not the only way to encode this invariant in PostgreSQL.

A conditional update can also guard the transition:

UPDATE inbox_messages
SET
    status = 'retrying',
    retry_count = retry_count + 1
WHERE id = $1
  AND processed_on_utc IS NULL;

Under PostgreSQL's default READ COMMITTED isolation level, if this update conflicts with a concurrent worker's row update, it waits for that worker to commit or roll back, then evaluates its condition against the current committed row version.

If worker B has already set processed_on_utc, the update affects zero rows.

That is a valid alternative for this race.

This reference keeps the explicit re-claim because failure recording does more than set one column. It evaluates retry policy from the current retry count, chooses between retry and dead-letter, writes retry metadata, may insert a dead-letter record, and emits the associated operational signals in the same transaction.

An UPDATE ... RETURNING or CTE-based implementation could reduce a database round trip while preserving the same invariant. That would be an implementation refinement, not a change to the concurrency model.

What I changed in the reliability claim

FOR UPDATE SKIP LOCKED did what it was supposed to do: it serialized row claims for the apply path.

The mistake was assuming that this guarantee extended into a later transaction.

A lock protects the critical section that actually holds the lock. It does not protect a decision made earlier and written later after the lock has been released.

For retry handling, compensation, dead-lettering, or other bookkeeping that runs in a separate transaction, the later operation needs to derive its transition from current state:

re-lock
-> read current state
-> decide from that state
-> write only when the transition is still valid

The full case study includes the deterministic regression test, the intentionally red commit, the final fix, and the explicitly bounded guarantee:

Read the Inbox Stale-Failure Write Race case study

The complete reference implementation is available in modulith-reliability-kit.

DEV Community: Ezra Wu