Transactions across services: 2PC, sagas, outbox, last-seat booking

Reserve the last seat in the Inventory DB and charge $200 in the Payment DB; both or neither. A coordinator (transaction manager) runs it.

1. Prepare: the coordinator asks both to prepare.
2. Inventory locks seat 14A, writes "will reserve" to its durable log, votes YES.
3. Payment checks the card, holds $200, logs it, votes YES.
4. Commit: if ALL voted YES the coordinator logs "commit" and tells both to commit; locks are released.
5. If ANY voted NO (card declined), everyone aborts: the seat is freed, nothing charged.

The database provides the primitive (Postgres PREPARE TRANSACTION, XA in MySQL/Oracle); the coordinator is separate (a transaction manager, or built into distributed DBs like Spanner). The "reserved" state is a lock plus a write-ahead-log record held by the DB engine, with no TTL.

• Locks are held across both phases, so other users wait.
• Coordinator crashes after the YES votes: participants are stuck in limbo holding locks (the blocking problem).
• Slow: several round trips and disk writes.
• All participants must be up, so availability drops (CP behavior).

A saga splits one business transaction into a sequence of local transactions, one per service, each committing on its own. If a later step fails, the saga runs compensating transactions for the steps that already committed, in reverse order. There is no global lock and no coordinator holding locks, so it keeps working when a service is slow. The price: the system is only eventually consistent, and in the middle other users can see half-done state.

• A committed local transaction cannot be rolled back, so you issue a new opposite action: release the seat, refund the payment, cancel the shipment.
• Some steps cannot be undone (an email already sent). Put those after the pivot, the point of no return (e.g. after the payment is captured), and put the steps most likely to fail first.
• Steps and compensations must be idempotent and retried until they succeed, because messages are delivered at least once (see Message queues).
• A compensation can fail too: retry with backoff, then send it to a dead-letter queue and alert a human.

Rule of thumb: choreography for two or three simple steps; orchestration once there are more steps, branches or timeouts.

• No isolation. Sagas give up the I in ACID: another user can see the seat HELD or a charge that will later be refunded. Make the in-between state explicit with a semantic lock (a HELD / PENDING status the application understands, as in the seat hold below).
• The saga state must be durable. Persist which step it reached so a crash resumes instead of forgetting. "Update the state and send the next command" must be atomic: that is the outbox pattern, at the end of this section.
• Timeouts. If a step never answers, treat it as failed and compensate. A late reply can still arrive, which is another reason steps must be idempotent.

Booking is CP: never sell the same seat twice; better to say "try again". Create the hold atomically so the database serializes the two clicks:

UPDATE seats
SET status='HELD', held_by='user_X', expires_at=now()+interval '10 minutes'
WHERE seat_id='14A' AND status='AVAILABLE';
-- first click changes 1 row; second finds status != AVAILABLE, changes 0 rows -> "seat taken"

• Other ways: optimistic locking with a version column, a unique constraint on (flight, seat), or Redis SET NX with a TTL as a fast lock (the DB is still the final authority).
• The application-level hold (HELD + expires_at) is a soft lock you build, better than holding real DB locks for minutes while a user pays. It is what sagas use.

1. Lazy expiry: treat an expired hold as available inside the booking query: WHERE seat_id=? AND (status='AVAILABLE' OR (status='HELD' AND expires_at < now())). No background job needed; gives correctness.
2. Active expiry: a sweeper job every minute, a TTL feature (Redis key TTL, DynamoDB TTL), or a delayed message (SQS delay, delayed queue) at hold time. Gives tidiness and accurate displayed availability.

Use both. And when the payment confirmation finally arrives, verify the hold is still yours and unexpired before confirming; if it was lost (paid at minute 11, someone else took it), refund.

A service often has to update its database and publish an event (booking confirmed, so tell email and loyalty). Those are two systems with no shared transaction. Commit then crash before publishing: the event is lost for good. Publish first then the commit fails: an event announces something that never happened. Using 2PC between a database and a broker is not realistic.

Outbox pattern: write the event into an outbox table in the same local transaction as the business change. A separate relay then publishes the outbox rows to the broker.

BEGIN;
UPDATE seats SET status='BOOKED' WHERE seat_id='14A' AND held_by='user_X';
INSERT INTO outbox (event_id, topic, payload)
  VALUES (gen_random_uuid(), 'booking.confirmed', '{"seat":"14A","user":"user_X"}');
COMMIT;
-- relay (separate process): read unsent outbox rows, publish to Kafka, mark them sent

• The relay can crash after publishing but before marking the row sent, so delivery is at least once. Consumers must be idempotent: dedupe on event_id (an "inbox" table of processed IDs).
• The same trick makes sagas safe: each step commits its state change and its next command message in one transaction.