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.11 · 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.11 · 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 to 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