The dual-write problem and the outbox pattern

Key points

• Writing to the database and then publishing to a broker is a dual write. There is no atomicity across two systems, so a crash between them leaves them permanently disagreeing.
• Both orderings fail. Publish first and the event may describe work that then rolls back. Save first and a crash loses the event, so downstream never learns the order exists.
• Retrying does not fix it: the first write already happened, so a retry either duplicates it or you need the very transaction you do not have.
• The transactional outbox solves it by removing the second system from the transaction. The event is written to an outbox table in the same local transaction as the business row, so both commit or neither does.
• A separate relay then reads the outbox and publishes. It can crash after publishing but before marking the row sent, so delivery is at-least-once and consumers must be idempotent.
• Change data capture is the low-overhead relay: tools like Debezium tail the database transaction log rather than polling, so they add almost no load and cannot miss a row.

Example

// WRONG - the classic dual write. Every ordering has a losing case.
@Transactional
public void placeOrder(Order order) {
    orders.save(order);                       // committed at method exit
    kafka.send("orders", order.id(), event);  // crash here -> event never sent,
}                                             //   and the transaction still commits

// RIGHT - one local transaction covers both rows.
@Transactional
public void placeOrder(Order order) {
    orders.save(order);
    outbox.save(new OutboxEvent(
            UUID.randomUUID(),
            "Order", order.id().toString(),
            "OrderPlaced",
            json.write(event)));
    // Both rows commit together, or neither does. No broker involved yet.
}

// The relay. Separate, restartable, and safe to run more than once.
@Scheduled(fixedDelay = 500)
@Transactional
public void publishPending() {
    // SKIP LOCKED lets several relay instances run without fighting.
    List<OutboxEvent> batch = outbox.findUnpublishedForUpdateSkipLocked(100);
    for (OutboxEvent e : batch) {
        kafka.send("orders", e.aggregateId(), e.payload());
        e.markPublished();     // crash before this -> republished, hence at-least-once
    }
}

/*
 * CREATE TABLE outbox_event (
 *   id             UUID PRIMARY KEY,
 *   aggregate_type VARCHAR(64)  NOT NULL,
 *   aggregate_id   VARCHAR(64)  NOT NULL,   -- becomes the Kafka key,
 *   event_type     VARCHAR(64)  NOT NULL,   --   so per-aggregate order holds
 *   payload        JSON         NOT NULL,
 *   created_at     TIMESTAMP    NOT NULL,
 *   published_at   TIMESTAMP    NULL,
 *   INDEX idx_unpublished (published_at, created_at)
 * );
 *
 * What it guarantees: every committed change produces its event, eventually.
 * What it does NOT: exactly-once delivery, or ordering across aggregates.
 */

Never write to two systems in one method - put the event in the same transaction as the data and let a relay publish it.