Transactions when the database stops being the whole system

A database transaction gives me an unusually reassuring boundary.

BEGIN
  │
  ├── create order
  ├── create order lines
  ├── reduce basket balance
  └── record order history
COMMIT

Either the transaction commits all of those writes or it commits none of them.

That is an atomicity guarantee. It is not the complete definition of correctness.

The database still needs to protect the intended invariant through suitable isolation, locks, conditional updates, uniqueness rules and other constraints. The application still needs to make a valid business decision.

That local model is so useful that I had begun applying it mentally to the complete application.

begin checkout

reserve stock

authorise payment

create order

arrange shipping

commit everything

But once those operations belong to different systems, there is no longer one database transaction wrapped around them.

ORDER DATABASE

INVENTORY DATABASE

PAYMENT PROVIDER

KAFKA

SHIPPING SERVICE

Each participant has its own state, availability and failure boundary.

Payment may succeed while order is unavailable.

Inventory may reserve stock while the response is lost.

Order may commit while the event broker is temporarily unreachable.

Shipping may reject an order that every earlier step accepted.

The application still needs consistency.

It no longer receives that consistency from one COMMIT.

When the database stops being the whole system, a transaction becomes one local guarantee inside a wider distributed process.

The larger process needs other mechanisms:

• local transactions;
• durable messages;
• idempotent operations;
• explicit workflow state;
• compensation;
• deadlines;
• reconciliation;
• operational recovery.

These mechanisms do not reproduce one enormous database transaction.

They create a different way to reach and verify a trustworthy outcome.

1. A transaction is a local guarantee

Suppose the order service owns:

• orders;
• order lines;
• order history;
• an order outbox.

If those records live in one transactional database, order can update them together.

BEGIN
  │
  ├── insert order-5001
  ├── insert order lines
  ├── insert history entry
  └── insert OrderCreated outbox event
COMMIT

The order service can promise:

If order-5001 exists, its lines, history and publication obligation were committed with it.

That is a strong guarantee because one transactional resource controls the commit.

ONE TRANSACTIONAL RESOURCE
           │
           ▼
ONE TRANSACTION MANAGER
           │
           ▼
ONE COMMIT DECISION

The service may still own other stores, such as an object store or search index, that do not participate in the same transaction. Ownership alone does not create atomicity. The boundary follows the transactional resource.

Moving to distributed services does not make local transactions obsolete.

It makes their scope more important.

A remote call is not part of the local transaction

It is tempting to write:

BEGIN ORDER TRANSACTION

call inventory

call payment

insert order

COMMIT

This can hold locks, connections and transaction-log resources while waiting on the network.

The remote operation may also succeed even if the local transaction later rolls back.

ORDER TRANSACTION

rolled back


PAYMENT PROVIDER

payment authorised

The database cannot roll back the provider because the call happened between BEGIN and COMMIT.

The code’s indentation does not create atomicity across systems.

One function does not imply one transaction.

A local transaction should normally contain local transactional work. Remote effects need their own reliability and recovery model.

Two local commits are not one distributed commit

Suppose inventory commits a reservation at 14:30:01 and order commits at 14:30:02.

For one second, only the reservation exists.

If order fails before its commit, inventory still contains committed state. If inventory fails after order commits, order may exist without stock.

Distributed consistency often means making those incomplete states explicit rather than pretending they cannot occur.

For example:

The workflow needs to know which states are valid, temporary, overdue or impossible.

2. Distributed work creates partial and uncertain states

A checkout is not one Boolean transaction that is either committed or rolled back.

It contains several owned state machines.

The complete business process emerges from relationships between these states.

For example:

Order confirmation requires trustworthy evidence that inventory is reserved and the payment decision is acceptable.

No single database necessarily owns every fact involved.

The workflow needs somewhere to record what it currently knows.

A timeout can produce an unknown outcome

Suppose order sends:

ReserveStock

reservation_id:
    reservation-71

The request reaches inventory, inventory commits, and the response arrives after order’s deadline.

ORDER                         INVENTORY

ReserveStock
    ─────────────────────────►

                              reserve stock
                              commit

deadline expires

DEADLINE_EXCEEDED

                              response arrives too late

Order knows:

No successful response arrived before the deadline.

It does not know:

Inventory performed no work.

The correct workflow state may be:

inventory_status:
    OUTCOME_UNKNOWN

Recovery can then:

• query the reservation by reservation_id;
• retry ReserveStock using the same identity;
• wait for a delayed StockReserved event;
• run reconciliation.

A distributed process cannot safely reduce every timeout to failure.

Sometimes uncertainty is the most accurate state available.

Idempotency makes the uncertain step repeatable

Retries should use the same operation identity.

attempt 1 ──┐
            ├──► reservation-71
attempt 2 ──┘

Inventory enforces one reservation for reservation-71.

The same principle applies to order creation, payment authorisation, capture, refunds, shipment creation and notifications.

Idempotency does not make the complete workflow atomic.

It makes one distributed step safe to attempt again while the workflow seeks a known result.

The complete workflow is not isolated

Each local transaction may have useful isolation inside its own database.

The saga as a whole does not gain one isolation boundary across every participant.

Concurrent workflows can still create:

• lost updates;
• conflicting reservations;
• dirty or premature reads of compensable state;
• one workflow acting on a state another workflow later compensates;
• decisions based on values that change before the pivot.

Possible protections include:

• semantic states such as PENDING_CHECKOUT;
• reservations and leases;
• entity versions;
• conditional transitions;
• rereading authoritative state before an irreversible step;
• commutative updates where possible;
• short-lived ownership tokens;
• local serialisation around one aggregate.

A saga coordinates progress.

It does not provide distributed isolation automatically.

3. Durable messages connect local decisions

A distributed workflow progresses through a sequence of locally committed decisions.

A possible checkout path is:

PlaceOrder
    │
    ▼
OrderCreated
    │
    ▼
ReserveStock
    │
    ▼
StockReserved
    │
    ▼
AuthorisePayment
    │
    ▼
PaymentAuthorised
    │
    ▼
ConfirmOrder
    │
    ▼
OrderConfirmed

Each state-changing step should have:

• a stable operation identity;
• one authoritative owner;
• one local transaction;
• a durable result;
• a durable route to the next piece of work.

The outbox joins local state to outgoing communication

Inventory can commit its reservation and a StockReserved publication obligation together.

BEGIN
  │
  ├── create reservation-71
  ├── update available stock
  └── insert StockReserved outbox event
COMMIT

The outbox guarantees:

If inventory committed the reservation, it also committed a durable obligation to publish StockReserved.

The event may still be delayed or published more than once.

The outbox does not turn checkout into one transaction. It prevents one service from committing a fact and silently forgetting to communicate it.

The inbox joins incoming messages to local effects

Suppose the order workflow consumes:

StockReserved

event_id:
    event-44

It needs to record inventory success, advance its workflow and issue the payment command.

BEGIN
  │
  ├── insert inbox record
  ├── update checkout workflow
  └── insert AuthorisePayment command in outbox
COMMIT

The inbox identity should be scoped to the consumer effect, not assumed to be globally unique for every use of the event.

For example:

consumer_name:
    bfstore-order-workflow

projection_generation:
    1

event_id:
    event-44

A suitable uniqueness rule is:

consumer_name
+
projection_generation
+
event_id

That allows another legitimate consumer or a new rebuild generation to process the same event independently.

OUTBOX

local state → outgoing message


INBOX

incoming message → local state

Both patterns use one local transaction to protect one side of a messaging boundary.

Neither creates a transaction spanning every service.

Kafka transactions protect another boundary

For Kafka-to-Kafka consume-transform-produce work, a Kafka transaction can atomically commit output records and consumed Kafka offsets.

That is useful inside Kafka’s transaction boundary.

It does not automatically include a relational database, search index, payment provider or email service.

Every transactional mechanism should be described by the boundary it actually protects.

4. A saga coordinates local transactions

A saga is not merely any asynchronous chain.

A saga is a coordinated distributed business transaction made from local transactions, with an explicit strategy for completing, compensating or otherwise resolving partial progress.

A simple shape is:

LOCAL TRANSACTION A
        │
        ▼
MESSAGE
        │
        ▼
LOCAL TRANSACTION B
        │
        ▼
MESSAGE
        │
        ▼
LOCAL TRANSACTION C

For checkout, that might mean:

1. create a pending order;
2. reserve stock;
3. authorise payment;
4. confirm the order;
5. start fulfilment.

If a later step fails, the workflow may execute compensating actions for earlier committed steps.

Compensable steps, pivot and retryable completion

A useful saga model separates three kinds of transaction:

COMPENSABLE STEPS
        │
        ▼
      PIVOT
        │
        ▼
RETRYABLE COMPLETION STEPS

• Compensable steps can be counteracted by later business operations.
• The pivot is the point of no return or the decision after which the workflow commits to forward completion.
• Retryable completion steps should eventually finish rather than attempting to restore the original state.

One possible checkout interpretation is:

The real pivot may instead be payment capture, dispatch or another legally meaningful action. The product and provider contracts decide.

This model encourages the workflow to validate critical prerequisites before the pivot and delay irreversible actions until it has enough evidence to commit to forward completion.

Orchestration gives the workflow one owner

An orchestrator can record:

CHECKOUT WORKFLOW

order:
    created

inventory:
    reserved

payment:
    pending

next action:
    authorise payment

It sends commands, observes results, applies deadlines and decides when to compensate or continue.

The orchestrator does not own inventory or payment state.

It owns the workflow’s durable knowledge of those outcomes.

Choreography distributes the process

In choreography, services react to events.

OrderCreated
      │
      ▼
inventory reserves stock
      │
      ▼
StockReserved
      │
      ▼
payment authorises

This can reduce direct workflow coupling.

It can also make these questions harder to answer:

• Which step is pending?
• Who notices that PaymentAuthorised never arrives?
• Who releases stock after a deadline?
• Who decides that checkout has failed?
• Who owns the complete customer outcome?

Choreography works well when reactions are genuinely independent or the process remains simple.

A critical multistep workflow usually benefits from explicit ownership somewhere, even when communication remains event-driven.

5. Compensation is another business process

Suppose inventory reserved two chairs and payment is later declined.

The workflow requests:

ReleaseStock

This resembles rollback.

It is not rollback.

The reservation existed, may have been observed and remains part of the history.

RESERVATION HISTORY

reservation created

reservation released

Rollback erases an uncommitted local change. Compensation responds to a committed distributed change with another committed change.

Compensation can fail

ReleaseStock is another distributed command.

It needs:

• an operation ID;
• idempotent handling;
• a deadline;
• durable retry state;
• monitoring;
• reconciliation;
• operator recovery when necessary.

Compensation is not a magical reverse arrow.

The workflow should remain visible until required compensations reach known outcomes.

Compensation order is business-defined

Compensating actions do not always run in the exact reverse order.

The correct order may depend on:

• business risk;
• dependencies between effects;
• provider requirements;
• whether repairs can run in parallel;
• whether a later action has made an earlier reversal unsafe.

A workflow may need to void payment before releasing a scarce reservation, or release several independent reservations concurrently.

The business contract decides.

Not every action can be undone

Some effects cannot be perfectly reversed:

• an email was read;
• a parcel was dispatched;
• a customer saw a price;
• an external report was published;
• a fraud review was opened.

A later correction, return, refund or superseding report does not erase the earlier history.

Before performing a step, the workflow should ask:

• Can this effect be compensated?
• Can it be delayed until after the pivot?
• Can it be made conditional?
• Can its consequences be corrected safely?
• Is the process ready to commit to forward completion?

Reordering steps chooses the tolerated partial state

Consider:

1. capture payment
2. reserve stock

The risk is money captured without stock.

Alternatively:

1. reserve stock
2. authorise payment

The risk is stock held without payment.

Neither order removes failure.

It chooses which temporary inconsistency is easier to manage.

Distributed transaction design is partly the art of choosing which partial state is tolerable and recoverable.

Reservations make temporary ownership explicit

A reservation has its own lifecycle:

AVAILABLE
    │
    ▼
RESERVED
    │
    ├──► COMMITTED_TO_ORDER
    └──► RELEASED

It can include:

• reservation ID;
• order ID;
• items;
• expiry time;
• status;
• version.

Expiry must be a guarded transition.

A delayed expiry worker should release only a reservation that is still reserved and still belongs to the expected generation. A late CommitReservation command should observe the authoritative terminal state rather than reviving an expired reservation accidentally.

Explicit intermediate states turn distributed uncertainty into managed resources.

6. Workflow state makes progress recoverable

The workflow state itself deserves a local transaction.

Suppose the orchestrator consumes StockReserved.

BEGIN
  │
  ├── insert scoped inbox record
  ├── update checkout to PAYMENT_PENDING
  └── insert AuthorisePayment in outbox
COMMIT

This guarantees:

If the workflow records stock as reserved, it also records the durable next command.

The wider process advances through a chain of locally protected transitions.

Guard every workflow transition

A transition such as:

PAYMENT_PENDING → CONFIRMED

should be conditional on the expected workflow generation and operation identities.

For example:

• expected current workflow version;
• matching payment operation ID;
• matching order and correlation identity;
• inventory reservation still valid;
• event not already consumed.

This prevents a delayed result from an earlier attempt confirming the wrong generation of the workflow.

Deadlines must survive restarts

The orchestrator can record:

inventory_status:
    PENDING

inventory_generation:
    3

inventory_deadline:
    14:31:00

When the deadline passes, a worker should update only the step and generation it inspected.

UPDATE checkout_workflows
SET inventory_status = 'OUTCOME_UNKNOWN'
WHERE checkout_id = ?
  AND inventory_status = 'PENDING'
  AND inventory_generation = 3;

This fences a stale timeout task from overwriting a workflow that has already progressed to generation 4.

A durable deadline is more than an in-memory timer.

It is part of the workflow’s state machine.

Retries keep the same operation identity

Suppose the workflow retries payment authorisation.

attempt 1:
    payment_attempt_id = payment-91

attempt 2:
    payment_attempt_id = payment-91

A genuinely new payment attempt, such as a replacement card after a decline, receives a new identity.

payment-91:
    original card


payment-92:
    replacement card

The correlation ID connects the whole checkout.

The operation ID protects one side effect.

A successful call is not workflow success

Inventory may succeed.

Payment may succeed.

Order confirmation may fail.

The workflow must decide whether to retry confirmation, compensate, or enter recovery.

The levels should remain visible:

Long-running work deserves an operation resource

Instead of holding one request open across the workflow, the API can return a durable operation resource.

A useful representation may include:

• operation ID;
• workflow status;
• current step;
• creation and update times;
• terminal result;
• recoverable error;
• required customer action.

For example:

operation_id:
    checkout-7f82

status:
    PROCESSING

current_step:
    PAYMENT

required_action:
    none

The resource should describe business progress, not merely whether one process is alive.

Terminal states must be visible

Possible terminal states include:

• CONFIRMED;
• CANCELLED;
• FAILED;
• RECOVERY_REQUIRED.

RECOVERY_REQUIRED acknowledges that automation cannot currently prove a safe terminal outcome.

The workflow should not remain silently pending forever.

Uncertainty becomes owned work rather than an unlabelled gap.

7. Reconciliation closes gaps in knowledge

Ordinary messaging and retries will not resolve every case.

Messages can be delayed.

Consumers can contain bugs.

External providers can accept requests while responses disappear.

Operators can replay records incorrectly.

Reconciliation compares what the workflow believes with what the authoritative owner reports.

ORDER WORKFLOW

payment-91 = UNKNOWN


PAYMENT PROVIDER

payment-91 = AUTHORISED

The reconciler can update local knowledge and continue the workflow.

Reconciliation must be concurrency-safe

The repair itself should be:

• conditional on the state originally inspected;
• version-aware;
• idempotent;
• audited;
• safe if delayed ordinary processing catches up concurrently.

A repair command should carry:

• a stable repair identity;
• the expected workflow version;
• the authoritative evidence used;
• the intended transition.

Otherwise a reconciler and a delayed consumer can both apply the same result or overwrite one another.

Periodic reconciliation is part of the system

A periodic job can search for suspicious states:

• checkout pending longer than ten minutes;
• payment outcome unknown longer than two minutes;
• reservation active after order cancellation;
• confirmed order without fulfilment plan;
• outbox record unpublished beyond its threshold.

It can then query authorities, retry an idempotent command, publish a repair command, create an operator task or move the workflow to manual review.

Reconciliation turns silent inconsistency into managed work.

Manual recovery is architecture too

Some cases cannot be resolved automatically.

An operator may need to inspect payment evidence, create or cancel an order deliberately, issue a refund, contact the customer and record the resolution.

The system should support this through:

• searchable operation IDs;
• audit history;
• safe repair commands;
• authorised administrative tooling;
• expected-version checks;
• clear ownership.

Manual recovery is not evidence that architecture failed.

Pretending no case will ever need it is.

Eventual consistency needs an authority map

A combined customer view may show:

That can be valid if order owns confirmation and shipping owns fulfilment state.

The system should know which owner answers each question.

Derived projections should remain clearly derived.

Without an authority map, eventual consistency becomes several databases disagreeing with no recognised source.

Two-phase commit is a different option

Two-phase commit can coordinate resources that support the protocol.

COORDINATOR
    │
    ├── prepare resource A
    ├── prepare resource B
    └── prepare message system
          │
          ▼
       commit all
       or abort all

The prepare phase records that each participant can commit. The transaction manager then issues the final commit or rollback decision.

This can provide atomic commitment among participating resources.

It does not make a non-participating payment provider, email service or public API transactional.

Prepared participants may retain locks and other resources while awaiting resolution, so coordinator recovery and the handling of in-doubt transactions matter.

The choice between atomic commitment and a saga depends on:

• participant support;
• latency and availability requirements;
• required isolation;
• duration of the operation;
• compensation feasibility;
• cost of temporary inconsistency.

Sagas are not automatically better.

Two-phase commit is not automatically inappropriate.

The guarantee and its operational cost should justify one another.

Avoid rebuilding a global transaction through synchronous calls

A microservice design can accidentally create this:

ORDER
  │
  ├── synchronous inventory call
  ├── synchronous payment call
  ├── synchronous shipping call
  ├── synchronous notification call
  └── wait for everything

Every participant must respond, but no shared rollback exists.

The call chain becomes an unreliable imitation of a transaction coordinator.

A better design separates what must complete before the user receives a result from what can continue asynchronously.

Keep local invariants local.

If order total, lines, discount allocation and status must commit together, splitting them across several services may create a distributed invariant without a compelling ownership reason.

Good service boundaries reduce the number of cross-service invariants.

They do not eliminate business processes that span services.

8. A bfstore checkout model

Suppose bfstore runs checkout as an orchestrated saga.

Storefront

The storefront creates:

checkout_id:
    checkout-7f82

Repeated submissions use the same identity.

Order service

Order commits:

BEGIN
  │
  ├── enforce one order per checkout_id
  ├── create order-5001 as PENDING
  ├── create workflow generation 1
  └── write OrderCreated and ReserveStock to outbox
COMMIT

Inventory service

Inventory handles:

ReserveStock

reservation_id:
    reservation-71

and commits:

BEGIN
  │
  ├── enforce unique reservation_id
  ├── verify available stock
  ├── create reservation
  ├── reduce available quantity
  └── write StockReserved to outbox
COMMIT

The local inventory update uses locking, a version check or a conditional statement so two concurrent checkouts cannot both reserve the last unit.

Order workflow

The workflow consumes StockReserved and commits:

BEGIN
  │
  ├── record scoped inbox identity
  ├── mark inventory = RESERVED
  ├── mark payment = PENDING
  └── write AuthorisePayment to outbox
COMMIT

Payment service

Payment uses:

payment_attempt_id:
    payment-91

with the provider.

It records the authoritative result locally and publishes PaymentAuthorised or PaymentDeclined.

Successful path

Order consumes PaymentAuthorised only when the expected workflow generation and payment attempt match.

It commits:

BEGIN
  │
  ├── record payment result
  ├── set order = CONFIRMED
  └── write OrderConfirmed to outbox
COMMIT

Confirmation is the pivot in this example.

Notification and fulfilment become retryable completion work after it.

Payment-declined path

Order commits:

BEGIN
  │
  ├── mark payment = DECLINED
  ├── mark order = CANCELLING
  └── write ReleaseStock to outbox
COMMIT

After inventory reports StockReleased, order becomes CANCELLED.

If release repeatedly fails, the workflow remains visible as RECOVERY_REQUIRED.

Each transition is local and atomic.

The complete process is distributed, observable and recoverable.

Distributed transaction checklist

A diagram containing several databases is not yet a transaction design.

The design begins when each failure between those databases has an owned state and a recovery path.

The mental model I am keeping

My original model was:

BEGIN

do everything

COMMIT

That model remains excellent inside one transactional boundary.

The wider model is:

                     DISTRIBUTED BUSINESS OPERATION
                                  │
                                  ▼
                         DURABLE WORKFLOW STATE
                                  │
                 ┌────────────────┼────────────────┐
                 │                │                │
                 ▼                ▼                ▼
          LOCAL TRANSACTION  LOCAL TRANSACTION  LOCAL TRANSACTION
              ORDER             INVENTORY           PAYMENT
                 │                │                │
                 ▼                ▼                ▼
              OUTBOX           OUTBOX            OUTBOX
                 │                │                │
                 └────────────────┼────────────────┘
                                  ▼
                          COMMANDS AND EVENTS
                                  │
                                  ▼
                       NEXT WORKFLOW DECISION

Local transactions answer:

Which changes did this transactional resource commit together?

Isolation and constraints answer:

Which concurrent changes and invalid states can the owner prevent?

Idempotency answers:

How can the same distributed step be attempted again without multiplying its effect?

The outbox answers:

How can committed local state carry a durable publication obligation?

The inbox answers:

How can repeated delivery create one local consumer effect?

Workflow state answers:

Which distributed outcomes are known, pending, failed or uncertain?

The saga answers:

How will partial progress complete, compensate or otherwise resolve?

Compensation answers:

Which new action should respond to an earlier committed action?

Reconciliation answers:

What actually happened when ordinary messaging did not leave trustworthy knowledge?

No single mechanism replaces the database transaction.

They extend trustworthy processing beyond the database’s border.

So when the database stops being the whole system, transactions do not disappear.

They become local islands of certainty.

The architecture’s job is to connect those islands using durable messages, stable identities and explicit recovery, without pretending the sea between them is part of one giant COMMIT.

References and further reading

Martin Kleppmann: Designing Data-Intensive Applications
Explores transactions, isolation, distributed coordination, logs, stream processing and the limits of atomic commitment across independently failing systems.

Chris Richardson: Microservices Patterns
Covers sagas, transactional outbox, polling publishers, transaction-log tailing and idempotent consumers.

Amazon Builders’ Library: Making retries safe with idempotent APIs
Explains caller-provided operation identity and mutating APIs that can safely receive repeated requests.

Microsoft Azure Architecture Center: Saga distributed transactions pattern
Describes sagas, compensable transactions, pivot transactions, retryable transactions and distributed isolation concerns.

Microsoft Azure Architecture Center: Compensating Transaction pattern
Explains domain-specific compensation, resumable undo work, ordering and points of no return.

gRPC: Deadlines
Explains deadlines, cancellation and why callers must not infer that a timed-out server performed no work.

MySQL: XA Transactions
Documents prepare and commit phases for transactions coordinated across XA-capable resources.

Apache Kafka: Design
Documents Kafka transactions and exactly-once processing boundaries for Kafka records and consumed offsets.