Careeroza

Transactional Outbox

The Dual-Write Problem

order-service must do two things: save the order in MySQL and publish OrderPlaced to Kafka. These are two different systems, so there is no transaction around both:

@Transactional
public Order placeOrder(PlaceOrderRequest req) {
    Order order = orders.save(Order.from(req));
    kafka.send("order-events", new OrderPlaced(order.getId(), order.getTotal()));  // (1) sent...
    return order;
}   // (2) ...but the commit happens HERE and can still fail -> event for an order that does not exist

// Swap the order (commit first, then send) and a crash between the two loses the event:
// the order exists, but inventory and payment never hear about it.

Failure	Result
Kafka send succeeds, DB commit fails	Ghost event: payment charges for an order that was never saved
DB commit succeeds, app crashes before send	Lost event: order stuck in PENDING forever
Kafka is down for 2 minutes	Every order in that window fails or loses its event

The Outbox Solution

Write the event into an outbox table in the same database, in the same local transaction as the order. A separate relay reads unsent rows and publishes them to Kafka, then marks them sent.

order-service
 +------------------- one local transaction -------------------+
 |  INSERT INTO orders (...)                                    |
 |  INSERT INTO outbox (aggregate_id, type, payload)            |
 +--------------------------------------------------------------+
                 |
        Outbox relay (poller every 500 ms, or Debezium CDC reading the binlog)
                 |  SELECT ... WHERE sent_at IS NULL ORDER BY id
                 v
            Kafka topic "order-events"  -->  inventory, payment, notification

CREATE TABLE outbox (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    aggregate_id  VARCHAR(64)  NOT NULL,      -- order id, used as the Kafka key
    event_type    VARCHAR(100) NOT NULL,      -- OrderPlaced
    payload       JSON         NOT NULL,
    created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at       TIMESTAMP    NULL
);
CREATE INDEX idx_outbox_unsent ON outbox (sent_at, id);

Example: Outbox and Relay

Example

The "database" commits the order and the outbox row together, or neither (order 1003 is rolled back). The relay then publishes unsent rows in order. The broker is down on the second publish call, so event 2 stays in the outbox and is sent on the next poll: nothing is lost.

import java.util.*;

public class Main {
    public static void main(String[] args) {
        Database db = new Database();
        OrderService orders = new OrderService(db);
        orders.placeOrder(1001, 2998);
        orders.placeOrder(1002, 1499);
        try { orders.placeOrder(1003, -5); }
        catch (IllegalArgumentException e) { System.out.println("rolled back: " + e.getMessage()); }
        System.out.println("orders=" + db.orders.keySet() + ", outbox rows=" + db.outbox.size());

        Broker broker = new Broker(2);                 // the 2nd publish call fails
        OutboxRelay relay = new OutboxRelay(db, broker);
        for (int poll = 1; poll <= 3; poll++) { System.out.println("poll " + poll + ":"); relay.poll(); }
        System.out.println("topic order-events = " + broker.topic);
    }
}

record OutboxRow(long id, String type, String payload) {}

class Database {
    final Map<Long, Integer> orders = new LinkedHashMap<>();
    final List<OutboxRow> outbox = new ArrayList<>();
    final Set<Long> sent = new TreeSet<>();
    long nextId = 1;

    void inTransaction(Runnable work) {            // all-or-nothing, like @Transactional
        Map<Long, Integer> o = new LinkedHashMap<>(orders);
        List<OutboxRow> b = new ArrayList<>(outbox);
        long id = nextId;
        try { work.run(); }
        catch (RuntimeException e) { orders.clear(); orders.putAll(o); outbox.clear(); outbox.addAll(b); nextId = id; throw e; }
    }
}

class OrderService {
    private final Database db;
    OrderService(Database db) { this.db = db; }
    void placeOrder(long orderId, int amount) {
        db.inTransaction(() -> {
            db.orders.put(orderId, amount);
            db.outbox.add(new OutboxRow(db.nextId++, "OrderPlaced", "OrderPlaced{orderId=" + orderId + "}"));
            if (amount <= 0) throw new IllegalArgumentException("invalid amount for order " + orderId);
            System.out.println("committed order " + orderId + " + outbox row");
        });
    }
}

class Broker {
    final List<String> topic = new ArrayList<>();
    private final int failOnCall;
    private int calls = 0;
    Broker(int failOnCall) { this.failOnCall = failOnCall; }
    void publish(OutboxRow row) {
        if (++calls == failOnCall) throw new IllegalStateException("broker unavailable");
        topic.add(row.payload());
    }
}

class OutboxRelay {
    private final Database db;
    private final Broker broker;
    OutboxRelay(Database db, Broker broker) { this.db = db; this.broker = broker; }
    void poll() {
        List<OutboxRow> unsent = db.outbox.stream().filter(r -> !db.sent.contains(r.id())).toList();
        if (unsent.isEmpty()) { System.out.println("  nothing to send"); return; }
        for (OutboxRow row : unsent) {
            try {
                broker.publish(row);
                db.sent.add(row.id());
                System.out.println("  sent event " + row.id() + " " + row.payload());
            } catch (IllegalStateException e) {
                System.out.println("  " + e.getMessage() + ", event " + row.id() + " stays in outbox");
                return;                                 // keep order: retry from here next poll
            }
        }
    }
}

Output

committed order 1001 + outbox row
committed order 1002 + outbox row
rolled back: invalid amount for order 1003
orders=[1001, 1002], outbox rows=2
poll 1:
  sent event 1 OrderPlaced{orderId=1001}
  broker unavailable, event 2 stays in outbox
poll 2:
  sent event 2 OrderPlaced{orderId=1002}
poll 3:
  nothing to send
topic order-events = [OrderPlaced{orderId=1001}, OrderPlaced{orderId=1002}]

Outbox in Spring Boot

@Service
@RequiredArgsConstructor
public class OrderService {
    private final OrderRepository orders;
    private final OutboxRepository outbox;
    private final ObjectMapper json;

    @Transactional                                   // ONE local transaction for both inserts
    public Order placeOrder(PlaceOrderRequest req) throws JsonProcessingException {
        Order order = orders.save(Order.from(req));
        outbox.save(new OutboxEvent(order.getId().toString(), "OrderPlaced",
                json.writeValueAsString(new OrderPlaced(order.getId(), order.getUserId(), order.getTotal()))));
        return order;
    }
}

@Component
@RequiredArgsConstructor
public class OutboxRelay {
    private final OutboxRepository outbox;
    private final KafkaTemplate<String, String> kafka;

    @Scheduled(fixedDelay = 500)
    @Transactional
    public void publishPending() {
        // SELECT ... WHERE sent_at IS NULL ORDER BY id LIMIT 100 FOR UPDATE SKIP LOCKED
        for (OutboxEvent e : outbox.lockNextBatch(100)) {
            kafka.send("order-events", e.getAggregateId(), e.getPayload()).join();  // wait for the broker ack
            e.markSent(Instant.now());
        }
    }
}

public interface OutboxRepository extends JpaRepository<OutboxEvent, Long> {
    @Query(value = """
        SELECT * FROM outbox WHERE sent_at IS NULL
        ORDER BY id LIMIT :limit FOR UPDATE SKIP LOCKED""", nativeQuery = true)
    List<OutboxEvent> lockNextBatch(@Param("limit") int limit);
}

FOR UPDATE SKIP LOCKED (MySQL 8 / PostgreSQL) lets several order-service instances run the relay without sending the same row twice at the same moment.
If the app crashes after send but before the commit, the row is sent again on restart. The outbox gives at-least-once delivery, so consumers must be idempotent (next topic).
Delete or archive sent rows with a nightly job so the table stays small.

Tip: Instead of polling, Debezium (Change Data Capture) can read the MySQL binlog / PostgreSQL WAL and stream new outbox rows to Kafka with very low latency. Its "outbox event router" is built for exactly this table shape.