Idempotency, Retries, and Safe State Transitions

Learning outcomes

Reliable systems retry operations. Networks time out, clients reconnect, jobs restart, and users double-click. If the same logical command is processed twice, the database must not accidentally create duplicate orders, double-charge, consume inventory twice, or repeat state transitions.

What idempotency means

An operation is idempotent when applying the same logical request multiple times has the same business effect as applying it once.

Not the same as “same SQL can run twice”

This is not idempotent:

INSERT INTO part_usage(...)VALUES (...);

because each retry creates another usage row.

Idempotency key

Clients can send a unique request key:

request_id = '8c31...'

The database stores it with a unique constraint:

UNIQUE (request_id)

Command deduplication

CommandReceipt(  request_id PRIMARY KEY,  command_type,  resource_id,  result_code,  created_at)

On retry, the system can return the previously recorded result.

Idempotency must be transactional

Do not insert the business row and idempotency receipt in separate transactions. They must commit together or failure can create ambiguous retry behavior.

Commit uncertainty

A client sends a command, the server commits, but the response is lost. The client retries because it cannot know the outcome. Idempotency makes that retry safe.

Natural idempotency

Some operations are naturally idempotent:

SET archived = true

Running it twice leaves the same state.

Non-idempotent operations

These need more care:

balance = balance - 100quantity = quantity + 1INSERT paymentINSERT usage event

State transitions

Represent allowed transitions explicitly:

Do not allow arbitrary assignment of status values merely because they are in the valid domain.

Compare-and-set transition

UPDATE work_orderSET status_code = 'closed'WHERE work_order_id = ?  AND status_code = 'in_progress';

If zero rows update, the transition is no longer valid or another transaction changed the state.

Transition history

WorkOrderStatusHistory(  status_history_id,  work_order_id,  from_status,  to_status,  changed_at,  changed_by,  request_id)

Including request_id makes duplicate transitions easier to detect and audit.

Retryable versus non-retryable failures

Exponential backoff and jitter

When many clients retry immediately, they can create another contention spike. Backoff and random jitter reduce synchronized retry storms.

Exactly-once is usually an illusion across systems

Distributed messaging commonly provides at-least-once delivery or duplicate possibilities. Design consumers to be idempotent rather than assuming messages will appear exactly once.

Outbox + idempotent consumer

A robust workflow:

1. commit business state and outbox message together;
2. publish message, possibly more than once;
3. consumer stores processed message ID uniquely;
4. duplicate deliveries become harmless.

WorkshopHub example: RecordPartUsage

Client sends:

request_idwork_order_idpart_idquantitycharged_unit_price

Within one transaction:

1. insert command receipt or reserve request_id;
2. check WorkOrder state;
3. decrement inventory atomically;
4. insert PartUsage;
5. store response metadata;
6. commit.

Duplicate request outcome

If the same request_id arrives again, return the previously recorded result instead of consuming stock again.

Safe retry checklist

• Can the operation be identified uniquely?
• Are repeated effects prevented by a unique key or compare-and-set?
• Are state transitions conditional on expected current state?
• Are transaction failures classified as retryable or permanent?
• Are external events delivered through a retry-safe mechanism?

Practice: payment command

Summary and next chapter

Chapter 10 connected schema design to operational consistency. You can now model transaction boundaries, interpret ACID, protect invariants under concurrency, choose optimistic or pessimistic control, and design retry-safe idempotent state transitions. Chapter 11 moves into index-aware design: how indexes reshape physical schemas, selectivity, composite indexes, covering indexes, and the write/storage costs of indexing.

References

• Martin Kleppmann, Designing Data-Intensive Applications.
• Jim Gray and Andreas Reuter, Transaction Processing: Concepts and Techniques.
• PostgreSQL documentation on transactions, locking, and serialization failures.
• Pat Helland, writings on idempotency and distributed transactions.