Distributed Transactions and Saga Pattern

Advanced System Design & Software Architecture · Lesson 32 · High-Level Design and Distributed Systems

Lesson Overview

A learner clicks Pay. The platform must reserve a seat, capture money, and record an order. Those three facts do not share a PostgreSQL primary. Seats live with the course inventory. Money is confirmed by a payment provider that is not an XA resource. The order row lives in the tenant shard from Database Sharding and Partitioning. A local @Transactional boundary, the kind lesson 18 and lesson 28 used, commits one database. It cannot roll back a card capture that already succeeded, and it cannot lock a seat row while an HTTP call to the provider waits.

Two-phase commit is the textbook way to commit several resource managers together. PostgreSQL can participate, with PREPARE TRANSACTION and COMMIT PREPARED. The cost is lock time, a coordinator that must recover, and participants that sit still until the decision arrives. That cost is unacceptable across a payment provider and a learner-facing request. The saga pattern, from Garcia-Molina and Salem, replaces one global commit with a sequence of local transactions and compensating transactions. Intermediate states are visible. That visibility is the consistency choice CAP Theorem and Consistency Models told you to name.

This lesson designs an orchestrated order saga for TechLearningPro, shows when choreography is the better shape, and writes the transactional outbox that lets a local commit and a later Kafka publish share a fate. The relay, the broker, and delivery semantics are Message Queues and Event Streaming. Idempotent consumers are Idempotency and Exactly-Once Processing. Kafka's own guarantees are Delivery Guarantees and Idempotent Producers. This lesson stops at the database row that makes the publish possible, and at the compensations that make the saga honest.

Learning Objectives

By the end of this lesson you will be able to:

1. Describe two-phase commit, including prepare, the durable decision, and why a crashed coordinator blocks participants.
2. Use PostgreSQL's real prepared-transaction commands and name the operational view pg_prepared_xacts.
3. Explain why a learner checkout that calls a payment provider should not hold XA locks across that call.
4. Define a saga as local transactions plus compensations, and state which ACID property it does not provide.
5. Choose orchestration or choreography from the need for timeouts, branches, and a readable process state.
6. Design compensations that are new transactions, idempotent, and safe to retry, including a pivot step after payment capture.
7. Model a semantic lock with a reservation row and an expiry, instead of a database lock held across services.
8. Insert an outbox row in the same local transaction as the business update.
9. Advance saga state with a conditional update so a duplicate message does not capture twice.
10. Leave the process in compensation_failed for a human when an automatic compensation cannot finish.

Prerequisites

• A local transaction on one PostgreSQL database: Transactions and COMMIT.
• Lesson 30's rule that checkout for one tenant is one shard, and that inventory may still be another service's database.
• Lesson 31's willingness to say that an intermediate state is visible.
• Spring JdbcClient and @Transactional on one DataSource. DataSourceTransactionManager and Spring's JDBC support commit one resource. They do not become a distributed transaction manager because a method calls two repositories.
• Lesson 9, Event-Driven Architecture Fundamentals, introduced process versus choreography at a smaller scale. This lesson is the failure and compensation design.

Detailed Core Concepts

What a distributed transaction is asking for

A distributed transaction wants several durable systems to commit or abort as one decision. Either the seat, the charge, and the order all become visible, or none do. Coordinators exist because those systems do not share a WAL. The classical protocol is two-phase commit (2PC).

Phase 1, prepare. The coordinator asks each participant to prepare. The participant does the work, writes a durable record that it can commit or abort, and then holds the locks the work required. In PostgreSQL that record is a prepared transaction. The participant votes yes or no. A no, or a timeout the coordinator treats as no, aborts the decision.

Phase 2, commit or abort. The coordinator writes the decision to its own durable log first. Then it tells every participant to commit or roll back. Participants acknowledge. If a participant is down at that moment, the coordinator retries until the participant applies the decision it already promised to accept.

The protocol can leave the system blocked. If the coordinator crashes after participants have prepared and before they learn the decision, each participant holds locks and waits. It cannot commit, because the decision might be abort. It cannot abort, because the decision might be commit and another participant might already have committed. Availability of those rows waits on coordinator recovery. That is the cost CAP would have predicted: a partition or a crash between coordinator and participant, and you refuse to guess.

PostgreSQL implements the participant side:

sqlCopy
BEGIN;
UPDATE inventory.course_seat
   SET seats_left = seats_left - 1
 WHERE course_id = :courseId
   AND seats_left >= 1;
PREPARE TRANSACTION 'saga-order-7f3a';
-- later, on the same database, after the coordinator decides:
COMMIT PREPARED 'saga-order-7f3a';
-- or
ROLLBACK PREPARED 'saga-order-7f3a';

PREPARE TRANSACTION is rejected unless max_prepared_transactions is greater than zero. The default is zero, which is a production-shaped default: prepared transactions hold locks, survive restart, and show up in pg_prepared_xacts until someone commits, rolls back, or an operator intervenes. An abandoned row in that view is an incident, not a metric to graph and ignore. Each allowed prepared transaction also reserves shared memory. Turn the setting on only where a real coordinator is going to resolve every id.

The Java side of 2PC is JTA with XA resources. The PostgreSQL JDBC driver provides org.postgresql.xa.PGXADataSource. Spring provides JtaTransactionManager, which enlists XA resources. Something else must be the transaction manager: Narayana and Atomikos are real implementations people run. DataSourceTransactionManager is not that manager. A method with two non-XA JdbcTemplate calls against two URLs is two local transactions in sequence. If the second fails, the first stays committed. Calling the method @Transactional does not change that unless both resources are enlisted in one JTA transaction.

2PC fits a short transaction between databases you operate, with an operator who can read pg_prepared_xacts, and with no human or HTTP call inside the prepare window. It does not fit "reserve the seat, call the card network, then commit." The network is not a participant that can prepare and hold. Even if a provider offered a long authorization, holding PostgreSQL row locks until the HTTP client returned would pin inventory behind a slow dependency. Checkout needs a different contract.

Sagas

A saga (Garcia-Molina and Salem, 1987) is a sequence of local transactions T1 … Tn. Each Ti commits on its own database. For each Ti except the last, the designer supplies a compensating transaction Ci that semantically undoes Ti if a later step fails. The saga's guarantee is semantic atomicity: either every step completes, or the compensations of the completed steps run. It is not a global snapshot. Other clients see reserved seats and pending orders.

Compensations are forward transactions. A captured charge is undone by a refund transaction, not by ROLLBACK of the capture. An email is undone by a correction email, or not at all. If you cannot name Ci, you cannot put Ti before a step that might fail. Reorder the saga so the step you cannot undo is last, or change the product so the step is reversible.

A pivot is the step after which the forward path is the one you finish or explicitly compensate, often the payment capture. Before the pivot, compensation is "release the seat" and "mark the order cancelled," and no money has moved. After the pivot, compensation includes a refund. The state machine should make that obvious.

Orchestration and choreography

Choreography. Each service subscribes to facts and emits the next fact. Inventory hears OrderPlaced, reserves, and emits SeatsReserved or SeatsRejected. Payment hears SeatsReserved and emits PaymentCaptured or PaymentFailed. Inventory hears PaymentFailed and releases. Nobody stores the whole process. That is a virtue when the reactions are independent: send a receipt email, update a search projection, increment a low-stakes counter. Those listeners do not need a shared timeout. The failure mode is a missing listener. The process looks finished because no row says otherwise.

Orchestration. A process manager stores the saga state and sends commands: ReserveSeats, then CapturePayment, then ConfirmOrder. It records replies. Timeouts live next to the state. The failure mode is a process manager that starts owning other services' tables and rules. The orchestrator stores order_id, state, reservation_id, and payment_id. Inventory still decides whether a seat exists. Payment still talks to the provider. The orchestrator decides what to ask next.

TechLearningPro checkout is orchestrated. There is a branch (no seats, card declined, provider timeout), a pivot (capture), and compensations that must run in a known order. Receipt email and search indexing can stay choreographed on OrderCompleted. They must not be how the seat is released. Event-Driven Architecture Fundamentals drew this line once. This lesson puts a table behind it.

Semantic locks

A database row lock held across Reserve and the payment HTTP call couples inventory throughput to provider latency and fails when the caller's transaction ends. 2PC would hold that lock even longer. A saga uses a reservation row instead:

sqlCopy
INSERT INTO inventory.reservation (
    id, course_id, order_id, seats, state, expires_at)
VALUES (
    :id, :courseId, :orderId, 1, 'reserved', now() + interval '15 minutes');

The seat count decrements in the same local transaction as the insert. Other checkouts see seats_left already reduced. The reservation expires. A sweeper releases expired reservations with the same compensation as a payment failure, so a crashed orchestrator does not pin seats until a human notices. Expiry is the isolation strategy: a semantic lock with a lease, visible to the business, instead of an XA lock invisible to the product.

The outbox

The orchestrator and each participant must not commit their database and then "also" publish to Kafka. The process can die in the gap. The transactional outbox puts the message in the same commit as the state change:

sqlCopy
CREATE TABLE commerce.outbox (
    id             uuid PRIMARY KEY,
    aggregate_type text NOT NULL,
    aggregate_id   uuid NOT NULL,
    event_type     text NOT NULL,
    payload        jsonb NOT NULL,
    created_at     timestamptz NOT NULL DEFAULT now(),
    published_at   timestamptz
);

CREATE INDEX outbox_unpublished
    ON commerce.outbox (created_at)
    WHERE published_at IS NULL;

A relay process, built in lesson 33, reads unpublished rows, publishes them, and sets published_at. Publishing can succeed and the published_at update can fail. The relay will publish again. Consumers must tolerate duplicates. That is at-least-once delivery. It is not exactly-once, and this lesson will not call it that. Kafka idempotent producers and consumer deduplication are the broker-side tools in Idempotent Producers and Idempotency and Exactly-Once Processing. The outbox is the bridge: the local database is the source of truth for "this step happened," and the topic is a copy.

Payloads carry saga_id, order_id, and the idempotency key. They do not carry card numbers or raw provider secrets. The payment service stores the provider reference and emits payment_id.

State moves forward on a predicate

Duplicate messages are normal. The state transition is the deduplication:

sqlCopy
UPDATE commerce.order_saga
   SET state = 'payment_captured',
       payment_id = :paymentId,
       updated_at = now()
 WHERE order_id = :orderId
   AND state = 'seats_reserved';

If the update count is zero, the saga was already past this step or was never in seats_reserved. The handler acknowledges the message and does not capture again. The payment service uses the same idea: a unique idempotency_key on payments, inserted in the same transaction as the outbox row. A retry hits the unique constraint and returns the original payment_id.

Architecture and Design Diagrams

mermaidCopy
flowchart TD
    api[Checkout API]
    orch[Order saga rows]
    outbox[commerce.outbox]
    inv[Inventory DB]
    pay[Payment DB]
    provider[Card provider]
    relay[Outbox relay]
    topic[Kafka topic]
    api --> orch
    orch --> outbox
    relay --> outbox
    relay --> topic
    topic --> inv
    topic --> pay
    pay --> provider
    inv -->|SeatsReserved or Rejected| topic
    pay -->|Captured or Failed| topic
    topic --> orch

The orchestrator does not open a connection to inventory's tables. It publishes a command by writing an outbox row. Inventory performs one local transaction. Payment performs one local transaction and one provider call inside its own service, after its reservation of the idempotency key, with the provider call outside any database lock that inventory cares about.

mermaidCopy
sequenceDiagram
    participant Orch as Order saga
    participant Inv as Inventory
    participant Pay as Payment
    Orch->>Inv: ReserveSeats
    Inv-->>Orch: SeatsReserved
    Orch->>Pay: CapturePayment
    alt capture succeeds
        Pay-->>Orch: PaymentCaptured
        Orch->>Orch: state completed
    else capture fails
        Pay-->>Orch: PaymentFailed
        Orch->>Inv: ReleaseSeats
        Inv-->>Orch: SeatsReleased
        Orch->>Orch: state compensated
    end

A crash at any arrow is recovered by reading order_saga.state and the unpublished outbox, not by a global rollback. If PaymentCaptured was sent and the orchestrator dies before completed, recovery sends nothing to the provider again. It continues forward. If ReleaseSeats was applied and the reply is lost, the retry sees the reservation already released and replies success.

Technical Explanation

Why the prepare window is the problem

Trace a 2PC checkout that you are refusing to ship. Inventory prepares the seat decrement and holds the row lock. Payment prepares a local ledger row. The coordinator calls the card network. The network takes eight seconds. Every other learner who wants that seat waits on the lock, or the prepare times out and you abort a charge you might have submitted. If the coordinator process dies after both prepares and after the network captured the card, inventory stays locked until pg_prepared_xacts is resolved by hand, and the card is already captured. You have the blocking problem and an external side effect the abort cannot undo. The saga exists because the side effect was never going to be a ROLLBACK.

Use 2PC where both participants are databases you operate, the work is milliseconds, and you have an on-call path for pg_prepared_xacts. A nightly transfer between two internal ledgers can qualify. Learner checkout does not.

Saga states for the order

completed is after the pivot. A later refund is a new saga, with its own idempotency key, because the learner asked for it. Do not reuse the checkout saga's compensation as the customer-service refund unless the steps are actually the same code path and you meant that.

Timeouts: if started has no reservation reply before the reservation would have expired anyway, move to compensation or to compensated if inventory reports no reservation. If seats_reserved has no payment reply, query payment by idempotency key before you release. The provider may have captured. Releasing the seat and then discovering a capture is how you sell the same seat twice and also owe a refund. The query-before-compensate step is the crash window. Write it down.

Idempotent release

sqlCopy
UPDATE inventory.reservation
   SET state = 'released', released_at = now()
 WHERE id = :id
   AND state = 'reserved';

Increment seats_left only when that update changed a row. A second ReleaseSeats updates zero rows and must not add a seat. The expiry sweeper uses the same statement. Orchestrator compensation and the sweeper race, and the predicate keeps the seat count honest.

The reserve path is a conditional decrement:

sqlCopy
UPDATE inventory.course_seat
   SET seats_left = seats_left - 1
 WHERE course_id = :courseId
   AND seats_left >= 1;

Zero rows means sold out. The saga hears SeatsRejected and ends compensated without calling payment. Two orchestrators cannot both pass this update for the last seat. That is the local atomicity you kept. You do not need a distributed lock. Distributed Locking and Coordination is for a different problem. Holding one across the card network would recreate the prepare window.

Outbox write beside the state change

javaCopy
@Transactional
public void onSeatsReserved(UUID orderId, UUID reservationId, UUID eventId, String payload) {
    int moved = jdbc.sql("""
            UPDATE commerce.order_saga
               SET state = 'seats_reserved',
                   reservation_id = :reservationId,
                   updated_at = now()
             WHERE order_id = :orderId
               AND state = 'started'
            """)
        .param("orderId", orderId)
        .param("reservationId", reservationId)
        .update();
    if (moved == 0) {
        return;
    }
    jdbc.sql("""
            INSERT INTO commerce.outbox
                (id, aggregate_type, aggregate_id, event_type, payload)
            VALUES
                (:id, 'order', :orderId, 'seats_reserved', CAST(:payload AS jsonb))
            """)
        .param("id", eventId)
        .param("orderId", orderId)
        .param("payload", payload)
        .update();
}

JdbcClient.update() returns the row count. The insert runs only when the transition happens, and it runs in the same Spring transaction as the update, on one DataSource. A crash leaves either both rows or neither. The relay in lesson 33 is the only publisher. The service does not inject a KafkaTemplate into this method. Doing both is the dual write the outbox replaces. When lesson 33 adds the relay, it will read published_at IS NULL in order, publish, and then mark the row. Consumers deduplicate on eventId.

Recovery scan

A scheduled sweep in the orchestrator selects sagas stuck in seats_reserved or payment_captured longer than their budget. For each, it loads the state and sends the next command or the compensation, using the same idempotent handlers. Recovery is normal execution of the state machine, not a special admin script that updates tables by hand. Hand updates skip the outbox and lose the event.

compensation_failed is excluded from automatic spin. Alert on the count. An operator retries after the payment provider is healthy, or refunds out of band and records a terminal note. Silent infinite retry hides provider outages inside a thread pool.

What other learners observe

Between reserve and capture, seats_left is lower and the order is not completed. The product can show "payment processing" on the buyer's page, via read-your-writes on the saga row. Another learner simply sees fewer seats. That is a dirty read of a business reservation, and it is accepted. Hiding the reservation would allow oversell. Lesson 31 called this an intermediate state. The saga is where it became concrete.

A buyer who refreshes and sees "processing" and then "paid" had monotonic reads of the saga if you pin that read to the primary. Do not serve saga state from a lagged replica during the minute you are deciding whether to refund.

Practical Implementation or Design Walkthrough

Problem

Implement order, seat, and payment for one course purchase. One seat remains. Two learners click Pay. The card provider can decline, time out, or capture and lose the HTTP response.

Requirements

• At most one learner gets the last seat.
• A declined card releases the seat.
• A captured card is never "forgotten" because the orchestrator restarted.
• A duplicate HTTP request from the buyer's browser does not capture twice.
• No PREPARE TRANSACTION on the request path.
• No card data in the outbox.

Local tables

sqlCopy
CREATE TABLE commerce.order_saga (
    order_id        uuid PRIMARY KEY,
    tenant_id       uuid NOT NULL,
    learner_id      uuid NOT NULL,
    course_id       uuid NOT NULL,
    state           text NOT NULL,
    reservation_id  uuid,
    payment_id      text,
    idempotency_key text NOT NULL UNIQUE,
    updated_at      timestamptz NOT NULL DEFAULT now()
);

The unique idempotency_key is the buyer's retry token. The second POST returns the existing saga instead of inserting another.

Inventory and payment keep their own tables in their own databases. The orchestrator stores their ids and does not join to them.

Happy path

1. Insert saga started and an outbox command ReserveSeats in one transaction. Commit.
2. Relay publishes. Inventory decrements seats if seats_left >= 1, inserts the reservation, writes its outbox, commits.
3. Orchestrator applies SeatsReserved with the conditional update above, writes command CapturePayment.
4. Payment inserts a row keyed by idempotency key, calls the provider outside the database transaction that only recorded "capture in progress," then records captured or failed and writes the outbox. The provider call is not inside a transaction that holds the seat row. The payment service's own "in progress" row is what makes the retry safe.
5. Orchestrator sets completed and writes OrderCompleted for the choreographed email and search listeners.

Failure path

Provider declines. Payment emits PaymentFailed. Orchestrator sets a compensating state and writes ReleaseSeats. Inventory's predicate update releases once. Orchestrator sets compensated.

Provider timeout. Payment does not know the outcome. It queries the provider by the same idempotency key the provider documented, or it reconciles from the provider's report. It does not capture a second time. Until that query returns, the saga stays seats_reserved or a dedicated payment_uncertain state. It does not release the seat while the charge might have succeeded. Uncertainty is a state. Treating uncertainty as failure double-sells or double-charges.

Orchestrator dies after inventory committed and before the orchestrator stored seats_reserved. Inventory's reply is in the outbox or already on the topic. Redelivery runs the conditional update. If the message was lost and only the reservation exists, the expiry sweeper releases it, or the recovery scan sees a reservation id by querying inventory with the order id. Prefer the reply. The sweeper is the backstop, and its expiry must be longer than the payment attempt budget so it does not release a seat under an in-flight capture.

Validation

• Two concurrent checkouts, one seat: one saga completes, the other gets SeatsRejected, payment mock sees one capture attempt.
• Decline: seat count returns to the prior value, and a second release does not increment it again.
• Kill the orchestrator after the outbox insert and before relay: on restart the row is still unpublished and the relay sends it once the relay exists. Until lesson 33, a test double can play the relay.
• Replay PaymentCaptured twice: one state transition, one payment_id.
• Assert pg_prepared_xacts stays empty through the test suite so nobody "helps" by enabling 2PC on the request path.
• Assert outbox JSON has no card field.

Operational notes

Log saga_id and state on every transition. Metric: sagas in each state, age of the oldest non-terminal saga, compensation_failed count, outbox age of the oldest unpublished row. The unpublished age is the bridge to lesson 33. If it grows, the relay is down and the databases are still consistent. Learners are waiting. That is a queue incident, not a split commit.

Enterprise Real-World Scenarios

Scenario 1: Course purchase saga

Context. TechLearningPro checkout. Inventory is a service because several products decrement seats. Payments are a service because only that service holds the provider credential. Orders stay on the tenant shard.

Design. Orchestrated saga as above. Email and the search index subscribe to OrderCompleted and do not participate in compensation. Sync commit from lesson 29 applies to the payment service's local record of a capture and to the order saga row. It does not extend to a distributed prepare. The buyer's page reads saga state from the primary.

Incident this prevents. The previous design called the provider and then inserted the order. The insert failed on a constraint. The card was captured and no order existed. Compensation could not run because there was no saga row and no idempotency key, only a provider reference in an HTTP log. The new design writes the intent and the key before the call, and writes the capture result in the payment database before it claims success to the orchestrator.

Scenario 2: Internal ledger movement where 2PC is still on the table

Context. A staff-training finance job moves a day's instructor payout from a pending ledger database to a payable ledger database. Both are PostgreSQL. There is no card network. The job runs at night. Rows involved are a few thousand, and each transaction is a single batch id.

Design. A coordinator with a durable decision log prepares both databases, commits the decision, then COMMIT PREPARED on each. max_prepared_transactions is set on those two clusters only. An alert fires if pg_prepared_xacts is non-empty for more than a minute. On-call has a runbook that reads the coordinator log and finishes the matching COMMIT PREPARED or ROLLBACK PREPARED. The job does not call HTTP. The learner checkout saga is not "simplified" into this path because the night job worked.

If either database is one you cannot operate, or the batch grows until lock time hurts daytime traffic, the same movement becomes a saga with a compensating posting. 2PC is chosen for a short, owned, recoverable window. It is not the default for every multi-service story.

Best Practices

• Keep one local transaction per service per step. Name the database it commits.
• Do not hold row locks or prepared transactions across HTTP calls.
• Put the step you cannot compensate last, or do not automate the failure path past it.
• Make every compensation idempotent with a state predicate, and share that statement with expiry sweepers.
• Query an uncertain payment before you release a seat.
• Store saga state in the orchestrator and domain rules in the services.
• Write the outbox row in the same transaction as the state change. Publish elsewhere.
• Deduplicate with conditional transitions and unique idempotency keys.
• Bound retries. Park compensation_failed for a human.
• Keep secrets and card data out of payloads.
• Use 2PC only for short transactions among databases you operate, with pg_prepared_xacts on the alert list.
• Treat a redelivery as success if the state is already where the message would have moved it.
• Read in-flight saga state from the primary.

Common Mistakes and Anti-Patterns

@Transactional across two data sources. Spring commits the transaction manager you actually configured. Two plain JDBC pools mean two commits. The first stays durable when the second throws. Enlist XA on purpose or split the work into a saga on purpose. Do not leave it accidental.

Prepared transactions on the checkout path. Locks sit in pg_prepared_xacts while the provider is slow or the coordinator is dead. Seat inventory stalls. Leave max_prepared_transactions at zero on request-path databases.

Compensation that is not idempotent. "Add the seat back" on every ReleaseSeats message double-counts when the relay republishes. Gate the increment on a reservation transition from reserved to released.

Releasing the seat on payment timeout. Timeout means unknown, not declined. Unknown plus release plus a later discovery of capture sells the seat twice. Persist payment_uncertain and reconcile with the provider's idempotency key.

Choreography with no owner for the failure. Three services emit events and nobody notices that SeatsReserved never received PaymentCaptured. An orchestrator row in seats_reserved older than the budget is a page. A diagram of arrows is not a page.

Dual write to the database and Kafka. The commit succeeds, the process dies, the topic never hears it. The outbox row is the fix. A try/catch that logs "will publish later" is the same bug with a log line.

God orchestrator. The process manager updates inventory tables through a second data source "to go faster." It now has the distributed transaction again, without the protocol. Send a command. Let inventory commit.

Troubleshooting and Design Review

Design review: What commits in which database at each step? What is the compensation, and is it idempotent? Where is the pivot? What happens on provider timeout? Where is the outbox written? What state is visible to another learner? Who is paged when the saga is stuck, and where do they look?

Interview Questions and Answers

Q1. What problem does two-phase commit solve, and what does it cost?

It commits or aborts several resource managers on one decision. Phase 1 asks each participant to prepare a durable vote and hold locks. Phase 2 records the decision and tells them to commit or roll back. PostgreSQL's participant commands are PREPARE TRANSACTION, COMMIT PREPARED, and ROLLBACK PREPARED, visible in pg_prepared_xacts, and they require max_prepared_transactions above zero. The cost is lock time, extra round trips and WAL, and blocking if the coordinator dies after prepare. Participants cannot safely guess. That blocking is why a user-facing checkout does not wrap a card call in 2PC.

Q2. Why is a saga not a distributed transaction with better branding?

A saga commits each step in its own database immediately. Later failure runs compensating transactions, which are new facts: a refund, a seat release. Other transactions see the intermediate reservation. There is no global rollback and no snapshot isolation across services. Garcia-Molina and Salem called the guarantee semantic atomicity. If you need the seat decrement and the ledger insert to become visible at the same instant, a saga will not give you that. Short 2PC between databases you operate might. A card network still will not join that 2PC.

Q3. When do you orchestrate, and when do you choreograph?

Orchestrate when the business process has branches, a timeout, and compensations that must happen in an order you can draw. Checkout is that process: reserve, then capture, or release. A row in order_saga is how you notice a stuck purchase. Choreograph when listeners react independently and do not undo each other. Email and search on OrderCompleted qualify. Choreography's failure is a forgotten consumer. Orchestration's failure is a process manager that absorbs domain tables. Keep commands going out and domain rules inside inventory and payment.

Q4. How do you make a compensation safe under at-least-once delivery?

Make it a local transaction whose predicate is the current state. ReleaseSeats sets the reservation to released only from reserved, and returns seats to the pool only if that update changed a row. A redelivery updates nothing and adds nothing. The expiry sweeper calls the same statement. Refund uses the payment idempotency key so a second refund request returns the first refund. A compensation that "always adds one seat" is not safe under the outbox relay you are about to build.

Q5. What is the transactional outbox, and what does it not promise?

In the same database transaction as the saga update, you insert a row into commerce.outbox with the event id, type, and payload. A relay later publishes rows where published_at is null and then marks them. If the process dies after commit, the row is still there. If the relay publishes and crashes before the mark, it publishes again. The promise is that a committed step will be offered to the broker at least once. It is not an atomic commit with Kafka, and it is not exactly-once processing. Consumers deduplicate on the event id. Lesson 33 owns the relay. This lesson owns the insert.

Q6. The card call times out. Should the saga release the seat?

Not yet. Timeout is not a decline. Persist an uncertain payment state and reconcile with the provider using the same idempotency key you sent. If the provider captured, continue to payment_captured and complete the order. If the provider has no charge, then release the seat and compensate. Releasing immediately risks a captured charge and a seat you sold to someone else. The state machine needs payment_uncertain or an equivalent. A single failed bucket is too coarse.

Q7. Where should the idempotency key live?

On the saga row, unique, from the buyer's first POST, so a double submit returns the same order. On the payment row, unique, so a retried capture returns the same payment_id. On each outbox event id, so consumers can ignore duplicates. Three keys, three scopes. One key for the whole company is meaningless. The payment provider must see the payment key as well, or the reconcile step in a timeout has nothing to query.

Q8. What will you see in PostgreSQL if someone enables prepared transactions and the coordinator crashes?

Rows in pg_prepared_xacts for the ids that were prepared and not finished. Those transactions hold locks across restart. Ordinary sessions block on the locked seats or ledger rows. max_prepared_transactions had to be raised or the prepare would have failed at the start. Recovery is to read the coordinator's decision log and run COMMIT PREPARED or ROLLBACK PREPARED for that gid. Guessing is how the two databases diverge. This is acceptable only where that runbook is real. It is not acceptable as an accidental side effect of a library that "supports XA."

Q9. How do you stop the orchestrator becoming a second inventory service?

The orchestrator stores process state: ids, step, timestamps. It sends ReserveSeats and accepts SeatsReserved or SeatsRejected. The conditional UPDATE on seats_left runs in the inventory database, in inventory's transaction, next to the reservation insert. If the orchestrator opens inventory's DataSource to "just decrement," you have a distributed write with neither 2PC nor a compensation boundary. Code review rejects the extra data source on the saga component.

Q10. A colleague wants one global serializable transaction for checkout. What do you trade by refusing?

You give up a single snapshot where seat, charge, and order appear together, and you give up automatic rollback of the card provider. You keep local atomicity on each database, a reservation that other buyers can see, and compensations you can retry. You also keep checkout available when one participant is slow, because you are not holding its locks inside a prepare. The buyer sees "processing" and then "paid" or "card declined." Say that in the UI. The trade is documented intermediate states in exchange for a failure mode you can finish.

Q11. How does this lesson hand off to the messaging lesson?

The outbox row is committed here. Lesson 33's relay reads published_at IS NULL, publishes to Kafka, and marks the row, accepting duplicates. Broker acks, partitions, and consumer groups live there, along with Delivery Guarantees. Deduplicating consumers and the difference between at-least-once and exactly-once processing live in Idempotency and Exactly-Once Processing. A saga design that starts by picking a partition count has skipped the state machine. The state machine is the contract the topic must not invent.

Hands-On Exercises

Exercise 1: Order, seat, payment

Problem. Design the TechLearningPro purchase saga. One seat remains. The provider can decline or time out.

Tasks.

1. Draw the states, including an uncertain payment state, and name the database that commits each transition.
2. Write the reserve SQL and the release SQL so a double release cannot increase seats_left twice.
3. Show the outbox insert in the same transaction as the transition from started to seats_reserved.
4. Specify the timeout behavior that queries the provider before release.
5. Say what another learner observes while the saga is seats_reserved.

Deliverables. State table, SQL, the Spring transaction boundary in words or code, and a test list covering decline, timeout, and redelivery.

Exercise 2: Where 2PC would belong

Problem. A nightly job posts one batch between two PostgreSQL ledgers you operate. A teammate wants the same mechanism for checkout.

Tasks.

1. Describe prepare and commit for the nightly job, including pg_prepared_xacts and the recovery step.
2. Explain the lock and the external side effect that make the same protocol a poor checkout.
3. State the alert that tells you a prepared transaction is stuck.

Rubric.

Lesson Summary

Two-phase commit gives several databases one decision and can block participants while locks sit in pg_prepared_xacts. Use it for short work between systems you operate and can recover. Learner checkout includes a card provider and a slow call, so it is a saga: local transactions on the order shard, on inventory, and on payment, with compensations that are new transactions. Orchestrate checkout because it branches and times out. Choreograph mail and search from OrderCompleted. Reserve seats with a conditional update and a reservation lease, not with a lock held across HTTP. Write the outbox row beside the state change. Treat provider timeout as unknown until you reconcile. Deduplicate with state predicates and idempotency keys. Park failed compensations for a human.

The unpublished outbox row is the input to Message Queues and Event Streaming. The duplicate publish the relay will cause is the input to Idempotency and Exactly-Once Processing.

Further Reading

• Hector Garcia-Molina and Kenneth Salem, "Sagas," ACM SIGMOD, 1987.
• PostgreSQL documentation: PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED, and the view pg_prepared_xacts.
• PostgreSQL JDBC: org.postgresql.xa.PGXADataSource. Spring Framework: org.springframework.transaction.jta.JtaTransactionManager versus DataSourceTransactionManager.
• Chris Richardson, Microservices Patterns, saga and transactional outbox chapters, for the orchestration and isolation discussion used in practice.
• TechLearningPro: Transactions, Delivery Guarantees, Idempotent Producers.
• This course: Event-Driven Architecture Fundamentals, CAP Theorem and Consistency Models, Message Queues and Event Streaming, Idempotency and Exactly-Once Processing.