The dual-write problem & the transactional outbox
This chapter separates database-local changes from a publish to another system. The scenarios run on MySQL 8.4 InnoDB. They demonstrate database states and rollback boundaries, not real broker delivery or process crashes.
The dual-write problem
The broker table below is a database-local stand-in, written separately from the order. In the first schedule, the order commits and the publish is omitted. In the second, the stand-in event is inserted in autocommit, then the order fails its CHECK constraint with 3819. The scenario asserts the mismatched counts:
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 OKThe stand-in publish is omitted after commit. This models a failure window without killing a process.
App> SELECT (SELECT count(*) FROM orders) AS orders,
(SELECT count(*) FROM broker) AS events;
orders | events
--------+--------
1 | 0
(1 row)Attempt 2 writes a stand-in event in autocommit before attempting the order.
App> INSERT INTO broker VALUES ('order_placed: order 2');
Query OK, 1 row affectedThe order INSERT fails its CHECK constraint with 3819. No timeout or crash is injected.
App> INSERT INTO orders VALUES (2, 'mallory', -5); -- ER_CHECK_CONSTRAINT_VIOLATED
ERROR 3819 (HY000): Check constraint 'orders_chk_1' is violated.The stand-in event exists without order 2. No downstream processing is observed.
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
These are two modeled failure windows, not proof of permanent disagreement or of every failure ordering. No broker or downstream consumer is observed. See the conceptual dual-write discussion for the boundary being modeled.
The fix: only ever write to one system
The application inserts the order and outbox row in the same transaction. The scenario asserts both committed records and the absence of both records from the rolled-back attempt:
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
App> SELECT id FROM orders ORDER BY id;
id
----
1
(1 row)The second attempt explicitly rolls back. The next SELECTs assert that only order 1 and its outbox row remain.
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 FROM orders ORDER BY id;
id
----
1
(1 row)
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)The relay deletes the row and explicitly rolls back. Publication is omitted; no HTTP call or crash is executed.
Relay> DELETE FROM outbox WHERE id = 1;
Query OK, 1 row affected
Relay> ROLLBACK;
Query OKThe rolled-back DELETE leaves the event available for reselection. A later relay transaction selects and deletes it again. This establishes database reselection, not redelivery to a recipient.
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
Entailed guarantee†: these InnoDB order and outbox changes share a commit boundary, provided they remain in the same transaction and no implicit commit separates them. † This follows from transaction atomicity, not from testing every failure. It does not include an external publish.
What the relay rollback proves
The relay claims event 1 with FOR UPDATE SKIP LOCKED, deletes it, and rolls back. A later transaction selects that event again, deletes it, and commits. The asserted final pending count is 0. Publication is intentionally omitted: the transcript proves reselection after rollback, not two deliveries.
Entailed delivery risk†: if a relay publishes externally before committing its deletion, a failure after successful publication can leave the outbox row available for another attempt. † This follows from the external effect being outside the database rollback boundary. No receiver effect is recorded here. At-least-once delivery additionally requires continued successful relay attempts and a delivery service; neither is established by this SQL schedule. Consumers need a separate deduplication protocol for repeated effects. The idempotency lesson protects database-local work under a retained key, not arbitrary remote effects.
Relay scheduling is outside this scenario
This SQL-only recipe selects pending rows; it does not implement a scheduler, wake-up channel, or binlog consumer. PostgreSQL's LISTEN/NOTIFY lesson demonstrates a separate wake-up mechanism. For a polling relay, select an interval from measured workload and latency requirements. The interval alone is not an upper bound on delivery time: locks, backlog, transaction duration, retries, and the recipient also matter. Query cost and delivery latency are not measured here.