The work and its checkpoint can never be atomic

Description

When one service does the work and another records it, some work runs twice. Idempotency is a correctness requirement.

This post covers two writes that look like one step: doing some work, and recording that it was done.

In this design the work runs in one service. Another service writes the record. No transaction spans both. So some work will run twice. The shape of the design guarantees it, and high load is not required. That makes idempotency a correctness requirement. If a stage cannot be made idempotent, the architecture has to change. Tighter timeouts will not fix it.

The architecture

The platform is an enterprise quoting system. It runs as Spring Boot services on EKS, and around 30 of them share one self-managed Oracle 12c instance.

Each in-flight workflow has one row in a PENDING_WORK table. The row is keyed by GROUP_ID and QUOTE_ID and carries a STAGE and a STAGE_STATE. A scheduler runs on every pod, and ShedLock makes it a singleton. It orchestrates and holds no business logic. Each stage is an HTTP endpoint on a core service. The orchestrator calls the endpoint, waits for success, then writes the next stage. Both services can update the row, but only the core service writes the business tables. Retry is bounded, after which the row is marked non-resumable. One user submission fans out into roughly 120 external API calls and database writes.

Two commits, one gap

The core service commits the work. Later, the orchestrator commits the checkpoint. These are two commits in two processes, and every ordering leaves a gap. Work then record risks duplication. Record then work risks silent loss, because the row says done and nothing will retry it. There is no third ordering. This design does the work first, which is the better failure.

Here is the window:

1. The orchestrator reads a row at PRICING / PENDING.
2. It sends POST /stages/pricing to the core service.
3. The core service does the work and commits.
4. The core service returns 200 OK.
5. The orchestrator pod dies before it updates the row. A node drain or an OOM kill is enough.
6. The ShedLock lease expires. A scheduler on another pod acquires it.
7. That scheduler reads the row. It still says PRICING / PENDING.
8. It sends the same request. The work runs twice.

Step 5 can also be a read timeout after step 3. The orchestrator sees a failure and retries. The team built that retry as the recovery path. It is also what produces the duplicate. The team also spent real effort sizing executors and queues against the connection pool. That work controls load. It has no effect on whether a stage runs twice. Shorter timeouts shrink the window. Nothing closes it.

Why not two-phase commit

XA works. Oracle supports it, and Spring can drive it through a JTA transaction manager such as Narayana. The price is extra round trips and forced log writes on every transaction, plus a coordinator that is a failure domain of its own. If the coordinator dies after prepare, participants hold in-doubt locks until someone resolves them. On a database shared by 30 services, those locks block other teams. Most teams decline XA for these reasons, and they are right to.

This system has a more basic problem. A stage is an HTTP call, and HTTP has no prepare phase. The core service cannot join a transaction owned by the orchestrator. The third-party APIs behind it cannot either.

Exactly-once is not on offer

At-most-once means possible loss. At-least-once means possible duplication. Exactly-once across a network boundary does not exist, because a sender cannot tell a lost request from a lost reply. Kafka's exactly-once guarantee holds inside Kafka and ends at the first external side effect. What a system can build is effectively-once: at-least-once delivery plus a receiver that makes repeats harmless.

Semantics	Guarantee	Cost	Choose it when
At-most-once	No duplicates. Loss is possible.	Almost none. No retry, no durable state.	Occasional loss is acceptable, as with metrics.
At-least-once	No loss. Duplicates are possible.	Retries and a durable record of pending work.	The receiver is already idempotent.
Effectively-once	Each effect lands once.	At-least-once plus a dedup store or idempotent writes, with retention and cleanup.	A duplicate costs money or breaks correctness.

The workflow above is at-least-once by construction. Every stage has to be effectively-once at the receiver.

Making a stage idempotent

The cheapest option is a natural key already in the data, and it is the one the team uses. Every stage endpoint is idempotent on GROUP_ID and QUOTE_ID, the identifiers that key the work row. That is the only reason the trace above ends safely.

With a unique constraint, a second insert fails with ORA-00001, which Spring raises as DuplicateKeyException. The endpoint has to catch it and return the original result as a success. An error sends the orchestrator into retries until the row goes non-resumable.

Endpoint idempotency covers the database writes keyed on those identifiers. External calls need more. If a stage composes several calls with CompletableFuture and one fails, a retry repeats the calls that succeeded, unless the stage checks for a stored result before each one.

Without a natural key, the caller can send an explicit idempotency key. It must be stable across retries, so it is derived from the row identifiers and the stage. A fresh UUID per attempt would defeat it. The receiver stores each key with its response, which costs an extra write per call, an index and a cleanup job.

Some operations are idempotent by nature. A MERGE to a known value is safe to repeat, and so is SET STATUS = 'PRICED'. An increment such as SET TOTAL = TOTAL + ? is not, and neither is an unkeyed insert into a history table. Rewriting increments as absolute sets often fixes a stage with no new infrastructure.

A dedup table generalises the key store, and its hard question is retention. Keys must outlive every path by which a request can be replayed, including a manual resume of a non-resumable row weeks later. Keys kept for 24 hours protect nothing against that.

The hard case is a third-party API that is not idempotent and takes no key. The caller writes an intent row before the call and an outcome after it. An intent with no outcome is unknown, and a blind retry may repeat the effect. Unknowns go to reconciliation through a status lookup or the provider's reports. A confirmed duplicate needs a compensating action, such as a cancel. At an illustrative rate of 1 ambiguous call in 10,000, 120 calls per submission leaves about 1.2% of submissions with at least one unknown.

The outbox, when both sides are owned

The outbox pattern closes the gap when the team owns both sides. The service writes its business change and an event row in one local transaction. A publisher sends unpublished rows afterwards.

CREATE TABLE OUTBOX_EVENT (
  ID            NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  AGGREGATE_KEY VARCHAR2(100) NOT NULL,
  EVENT_TYPE    VARCHAR2(50)  NOT NULL,
  PAYLOAD       CLOB          NOT NULL,
  CREATED_AT    TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
  PUBLISHED_AT  TIMESTAMP
);
CREATE INDEX OUTBOX_PENDING_IX ON OUTBOX_EVENT (PUBLISHED_AT, ID);

The publisher still sends first and marks second. That is the same dual-write, moved to a place where a retry is safe.

@Scheduled(fixedDelay = 1000)
@SchedulerLock(name = "outboxPublisher", lockAtMostFor = "PT30S")
public void publishPending() {
    List<OutboxEvent> batch = jdbc.query(
        "SELECT * FROM OUTBOX_EVENT WHERE PUBLISHED_AT IS NULL " +
        "ORDER BY ID FETCH FIRST 100 ROWS ONLY", outboxMapper);
    for (OutboxEvent e : batch) {
        sender.send(e.aggregateKey(), e.id(), e.payload()); // a crash here causes a resend
        jdbc.update("UPDATE OUTBOX_EVENT SET PUBLISHED_AT = SYSTIMESTAMP WHERE ID = ?",
            e.id());
    }
}

The outbox buys atomicity between a state change and its message. It costs a table, a publisher and constant polling against a database that 30 services already share. Delivery is still at-least-once, so consumers dedupe on ID.

A narrower version fits the database-only stages. The core service already writes PENDING_WORK. If it advanced STAGE in the same transaction as its business writes, those stages would have no gap. That needs one datasource inside one @Transactional method. Two datasources are two transactions, even on one Oracle instance. The cost is that stage transitions would live in two services, and the orchestrator would have to read an already-advanced row as success. External API stages gain nothing.

Fencing for concurrent work

Idempotency handles repeated work. Fencing handles concurrent work, and this system needs both. ShedLock grants a lease with an expiry. If the holder stalls in a long GC pause, or a run outlives lockAtMostFor, another pod takes the lock while the first is still running. The row also has two writers by design, the orchestrator and the core service.

The team already has part of a fence. An ATTEMPT_COUNT column is updated on every execution of a stage. The orchestrator stops triggering a stage once the count reaches a configurable threshold. That bounds how often a stage runs. It does not stop two workers running it at once. If the count is read, checked and then incremented, two workers that both read 2 can both start attempt 3.

The same column becomes a fencing token once every write is conditional on it.

// Claim the attempt. Only one worker can move the count from n to n + 1.
int claimed = jdbc.update(
    "UPDATE PENDING_WORK SET ATTEMPT_COUNT = ATTEMPT_COUNT + 1 " +
    "WHERE GROUP_ID = ? AND QUOTE_ID = ? AND STAGE = ? AND ATTEMPT_COUNT = ?",
    groupId, quoteId, stage, readCount);
if (claimed == 0) return; // another worker owns this attempt

// ...call the stage endpoint...

int rows = jdbc.update(
    "UPDATE PENDING_WORK SET STAGE = ?, STAGE_STATE = ? " +
    "WHERE GROUP_ID = ? AND QUOTE_ID = ? AND STAGE = ? AND ATTEMPT_COUNT = ?",
    nextStage, nextState, groupId, quoteId, stage, readCount + 1);
if (rows == 0) throw new StaleCheckpointException(groupId, quoteId, stage);

A stale worker's checkpoint now matches zero rows. Without the condition, it could move a row back to a stage already passed. The stale call to the endpoint still happens, and idempotency makes it harmless. The conditional claim also makes the threshold exact. The core service's writes to the row need the same condition.

A decision guide

For each stage, the questions go in this order.

1. Does running it twice leave the same state? Call it twice with the same input and compare the rows. If it does, the stage is already idempotent.
2. Is there a natural key, or can the orchestrator send a stable key that the receiver stores? Then idempotency is cheap. The cost is storage and a retention rule that covers manual resumes.
3. Is the effect owned by a third party that accepts no key? Then it needs an intent record, reconciliation and compensation. It also belongs in its own stage, so retries of other work do not repeat it.
4. If none of these hold and a duplicate is unacceptable, the architecture has to change. Timeouts and retry counts are the wrong tools.

Isn't this what Temporal and Step Functions are for?

Yes. Both solve durable execution properly. Either one would replace the work table, the ShedLock scheduler and the hand-written retry logic.

Neither removes the need for idempotency. A Temporal activity or a Step Functions task is still a call that can succeed and lose its reply. Temporal's own documentation recommends idempotent activities. The dual-write moves to the activity boundary and stays there.

The counterweights are specific. Step Functions Standard workflows bill per state transition, and a 120-way fan-out multiplies the transitions in every submission. Workflow state lives in the engine's store, outside Oracle, so it is not transactional with business data. That is two systems without a shared transaction, again. A new platform also brings new on-call knowledge to a team that already runs its own database. With either tool in place, the team would still be writing idempotent stages.