The dual-write problem & the transactional outbox

Every problem so far lived inside one database, where BEGIN … COMMIT could always save you. This chapter is about the moment that stops being true: there is no BEGIN that spans MySQL and Kafka. The theory — why two writes to two systems can't be made atomic, and how an outbox shrinks the damage — is Concepts: dual writes & the outbox; this page proves it on MySQL, crashes included.

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:

Attempt 1 — write, then publish. The order commits…

App> BEGIN;
Query OK

App> INSERT INTO orders VALUES (1, 'alice', 90);
Query OK, 1 row affected

App> COMMIT;
Query OK

…and the process crashes before the publish step ever runs. The broker never hears about order 1.

App> SELECT (SELECT count(*) FROM orders) AS orders,
            (SELECT count(*) FROM broker) AS events;
 orders | events 
--------+--------
      1 |      0 
(1 row)

Attempt 2 — publish first, then write. The event goes out…

App> INSERT INTO broker VALUES ('order_placed: order 2');
Query OK, 1 row affected

…and then the order INSERT fails — a constraint, a crash, a timeout, anything.

App> INSERT INTO orders VALUES (2, 'mallory', -5); -- ER_CHECK_CONSTRAINT_VIOLATED
ERROR 3819 (HY000): Check constraint 'orders_chk_1' is violated.

Downstream services now process an order that never existed.

App> SELECT (SELECT count(*) FROM orders WHERE id = 2) AS orders,
            (SELECT count(*) FROM broker WHERE event LIKE '%order 2%') AS events;
 orders | events 
--------+--------
      0 |      1 
(1 row)

Verified against MySQL 8.4.10 · Run it yourself · Scenario source

Write-first loses events; publish-first invents them. Retries only change the odds.

The fix: only ever write to one system

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 InnoDB has guaranteed since chapter 1, does the rest:

The order and its event are written in ONE transaction — both land in the same database.

App> BEGIN;
Query OK

App> INSERT INTO orders VALUES (1, 'alice', 90);
Query OK, 1 row affected

App> INSERT INTO outbox (event) VALUES ('order_placed: order 1');
Query OK, 1 row affected

App> COMMIT;
Query OK

Atomicity covers the failure path too: no committed order, no event.

App> BEGIN;
Query OK

App> INSERT INTO orders VALUES (2, 'bob', 75);
Query OK, 1 row affected

App> INSERT INTO outbox (event) VALUES ('order_placed: order 2');
Query OK, 1 row affected

App> ROLLBACK;
Query OK

App> SELECT id, event FROM outbox ORDER BY id;
 id |         event         
----+-----------------------
  1 | order_placed: order 1 
(1 row)

A relay claims the event exactly like a chapter-5 job-queue worker…

Relay> BEGIN;
Query OK

Relay> SELECT id, event FROM outbox ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
 id |         event         
----+-----------------------
  1 | order_placed: order 1 
(1 row)

…publishes it to the broker (an HTTP call — outside any transaction), deletes it, and crashes before COMMIT.

Relay> DELETE FROM outbox WHERE id = 1;
Query OK, 1 row affected

Relay> ROLLBACK;
Query OK

The delete evaporated with the crash — the event is still in the outbox. The restarted relay publishes it AGAIN: at-least-once delivery, so consumers must be idempotent.

Relay> BEGIN;
Query OK

Relay> SELECT id, event FROM outbox ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
 id |         event         
----+-----------------------
  1 | order_placed: order 1 
(1 row)

Relay> DELETE FROM outbox WHERE id = 1;
Query OK, 1 row affected

Relay> COMMIT;
Query OK

Relay> SELECT count(*) AS pending FROM outbox;
 pending 
---------
       0 
(1 row)

Verified against MySQL 8.4.10 · Run it yourself · Scenario source

A separate relay process moves events from the outbox to the broker. It is exactly the SKIP LOCKED job-queue worker from chapter 5, pointed at the outbox table.

At-least-once, by construction

Look closely at what the crash proved. The relay published the event, then died before committing the DELETE — so the event is delivered twice. That is the deal you signed: at-least-once delivery — and repeats are exactly what chapter 5's idempotency keys already handle on the consumer side.

No LISTEN/NOTIFY: the relay polls

PostgreSQL pairs its outbox with LISTEN/NOTIFY, a transactional wake-up call that removes the polling latency. MySQL has no equivalent: there's no server-push channel a commit can signal, so the MySQL relay polls. And that's fine. A SELECT … FOR UPDATE SKIP LOCKED against an indexed, near-empty outbox every 100–500 ms is cheap, and the polling interval is your worst-case delivery latency.

If that latency matters, the usual escalation is reading the binlog (Debezium-style change data capture), which turns the database's own replication stream into the wake-up call — same outbox table, no polling, considerably more moving parts.

The order and its event commit or vanish together, with no window where one exists without the other. The relay is a SKIP LOCKED worker — crash-safe, parallelizable, five lines of SQL — so its consumers have to be idempotent, because delivery is at-least-once by construction. And with no LISTEN/NOTIFY on MySQL, you either poll for bounded latency or tail the binlog with CDC for speed at the cost of more moving parts.

The anomaly catalog

The dual-write problem & the transactional outbox