Dual writes & the transactional outbox

Inside one database, BEGIN … COMMIT can always save you. This page is about the moment that stops being true: your transaction needs to reach a second system — a message broker, a search index, another service's API. There is no BEGIN that spans your database and Kafka.

The dual-write problem

Write to the database and publish to the broker — two writes, two systems, and a process that can die between them. It doesn't matter which write goes first; each order picks which lie you end up with:

App

Broker

INSERT order, COMMIT← the order exists

publish "order placed" — process dies← the event is lost

downstream never learns about the order

Write-first loses events (downstream never learns about the order); publish-first invents them (downstream processes an order that was never placed). Retries don't fix this — they only change the odds. The two systems need to agree, and nothing makes them.

The fix: only ever write to one system

The outbox pattern's insight is that the application never talks to the broker at all. The event is written to the same database, in the same transaction as the order — and atomicity, which the database has guaranteed all along, does the rest:

App

Relay

BEGIN

INSERT INTO orders …

INSERT INTO outbox …← same transaction

COMMIT← order and event exist together, or not at all

SELECT … FROM outbox FOR UPDATE SKIP LOCKED

publish to broker, DELETE FROM outbox, COMMIT

A separate relay process moves events from the outbox table to the broker — typically a SKIP LOCKED job-queue worker pointed at the outbox: crash-safe, parallelizable, five lines of SQL.

At-least-once, by construction

The relay itself still performs two writes to two systems: "publish to the broker" and "delete from the outbox". If it dies between them, the event is delivered twice. That is not a bug to fix but the deal you signed: the dual-write problem never disappears; the outbox shrinks it from "events can be lost or invented" down to "events can repeat" — and repeats are handled with idempotent consumers. Exactly-once is not on the menu; idempotent at-least-once is how grown-ups spell it.

See it happen

Both tracks prove the failure and the fix with a real crashed relay:

• PostgreSQL: dual writes & the outbox — plus LISTEN/NOTIFY, the transactional wake-up call that removes the relay's polling latency
• MySQL: the outbox pattern — no LISTEN/NOTIFY there: the relay polls, or graduates to binlog-based CDC

Further reading

• microservices.io: Transactional outbox
• microservices.io: Polling publisher — the relay variant both tracks demonstrate