Transactional outbox and the dual-write problem: an interactive simulator

A service that saves to its database and publishes an event to a message broker is doing two writes to two systems, and no transaction covers both. Whichever it does first, a crash or an outage between them leaves the two disagreeing. The transactional outbox fixes this by writing the event into the database, in the same transaction as the data, and publishing it from there afterwards.

The playground runs a minute of orders through four approaches. Take the broker down for ten seconds, make requests crash between their two steps, and compare what each one gets wrong.

The dual-write problem

Saving an order and then publishing OrderPlaced looks safe until the process dies between the two, or the broker is unreachable. The order is committed and the event is gone: the warehouse never ships, the email is never sent. With 5% of requests crashing between steps, 48 of 1,200 orders go silent; with the broker down for ten seconds, all 200 orders in that window do.

Publishing first just moves the problem. Now a crash after the publish leaves an event for an order that was never saved, 48 of them in the same preset, and while the broker is down the service cannot take orders at all: 200 are refused. Retrying the publish in memory narrows the window but cannot close it, because the retries die with the process.

How the transactional outbox works

1. Write both rows in one transaction: the order, and an outbox row holding the event. They commit together or not at all.
2. Relay: a separate process reads unsent outbox rows in order and publishes them to the broker.
3. Mark sent: after the broker acknowledges, it marks the rows sent, or deletes them.
4. Retry until it works: if the broker is down, rows simply wait. In the playground’s outage the events pile up for ten seconds and all go out the moment it returns.

The relay can crash after publishing a batch and before marking it sent, and then publishes it again: 20 duplicates in the playground. So the outbox gives at-least-once delivery, and consumers must be idempotent, for example by recording each event id they have processed. Events also arrive a little late, half a second on average with a one-second poll.

Log tailing instead of polling

Change data capture reads committed changes straight from the database’s replication log, such as the PostgreSQL write-ahead log or the MySQL binlog, and publishes them. Debezium is the common tool, often reading the outbox table so that published events have a deliberate shape rather than mirroring table rows. It adds no polling queries and cuts the delay, 150 ms instead of 500 in the playground, but needs a connector to run and monitor, and it also redelivers after a restart, from its last saved log position.

The outbox in SQL

-- in the request: the order and its event commit together, or neither does
BEGIN;
INSERT INTO orders (id, customer_id, total) VALUES (:id, :customer, :total);
INSERT INTO outbox (id, topic, key, payload)
VALUES (gen_random_uuid(), 'orders', :id, :event_json);
COMMIT;

-- in the relay, every second: publish unsent rows in order, then mark them sent
SELECT id, topic, key, payload FROM outbox
WHERE sent_at IS NULL ORDER BY created_at LIMIT 500
FOR UPDATE SKIP LOCKED;                 -- several relays can share the work
-- ... publish each to the broker and wait for acks ...
UPDATE outbox SET sent_at = now() WHERE id = ANY(:published_ids);
-- a crash between the publish and this update sends the batch again: at least once

In a system design interview

Whenever a service writes data and emits an event, say how they stay in step: “The order service writes the order and an OrderPlaced row to an outbox table in one transaction. Debezium tails the outbox and publishes to Kafka, keyed by order id so events for an order stay in order. Delivery is at least once, so the inventory and email consumers deduplicate on event id.” Expect the follow-ups: ordering, how big the outbox grows during a broker outage, and cleaning up sent rows.

Read the guide to change data capture or delivery semantics, see idempotency keys for handling the duplicates, or watch a strong candidate design a payment system.

Questions people ask