Sagas: transactions that can't ROLLBACK

This lesson models a workflow whose steps commit separately. If a later step cannot complete, a new local transaction compensates for an earlier committed step. It does not demonstrate a multi-service saga coordinator.

Watch a saga fail forward

The scenario uses two InnoDB tables in one database as stand-ins for flight and hotel services. It commits a flight reservation, cannot reserve a hotel room, then restores the flight seat in a new transaction:

Step 1 — book the flight. A local transaction, committed immediately.

Saga> BEGIN;
Query OK

Saga> UPDATE flights SET seats = seats - 1 WHERE id = 1;
Query OK, 1 row affected

Saga> SELECT seats FROM flights WHERE id = 1;
 seats 
-------
     4 
(1 row)

Saga> COMMIT;
Query OK

This autocommit Reader sees the committed flight reservation between steps. Local transaction isolation still applies.

Reader> SELECT seats FROM flights WHERE id = 1;
 seats 
-------
     4 
(1 row)

Step 2 — book the hotel. No rooms left: the step fails as a business outcome, not an error.

Saga> BEGIN;
Query OK

Saga> UPDATE hotels SET rooms = rooms - 1 WHERE id = 1 AND rooms > 0; -- 0 rows — nothing to book
Query OK, 0 rows affected

Saga> ROLLBACK;
Query OK

Step 1 already committed — there is nothing a ROLLBACK could undo. The saga runs a COMPENSATING transaction: a new forward transaction that reverses the booking.

Saga> BEGIN;
Query OK

Saga> UPDATE flights SET seats = seats + 1 WHERE id = 1;
Query OK, 1 row affected

Saga> SELECT seats FROM flights WHERE id = 1;
 seats 
-------
     5 
(1 row)

Saga> COMMIT;
Query OK

Reader> SELECT seats FROM flights WHERE id = 1;
 seats 
-------
     5 
(1 row)

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

The autocommit Reader sees 4 seats after the first commit and before compensation. The hotel's guarded UPDATE affects 0 rows, a business outcome rather than a database exception. Its local ROLLBACK does not undo the committed flight reservation. The compensation increments seats and commits; the final reader observes 5 seats.

Local transactions still have their own isolation. This workflow has no single transaction hiding all intermediate commits, but that does not mean every reader immediately sees every step: visibility depends on each reader's isolation and read view. The transcript demonstrates this Reader's observation, not global visibility across services or isolation from other workflows.

Entailed boundary†: a later ROLLBACK cannot undo a previously committed step. Returning a reserved seat therefore requires a new business operation, not rollback of the earlier transaction. † This follows from the local commit boundary. Compensation is not general reversal of history; its own concurrency, failures, and repeat attempts need an application protocol. Only a successful compensation is executed here.

For a real workflow, define recoverable intermediate states, progress recording, and repeat-safe compensation. Recording a step's progress with its database-local effect uses the same transaction boundary as an outbox, but this scenario contains no progress table or crash-recovery coordinator and proves neither.

Further reading

• The same lesson on PostgreSQL