The dual-write problem & the transactional outbox

A local PostgreSQL transaction covers its database changes. A separately committed publication is outside that boundary. These examples use database tables and explicit statement ordering, not Kafka, HTTP, or a live broker. The shared explanation describes the design; the observations and limits of the PostgreSQL examples are given here.

The dual-write problem

The broker table is a Receiver model: its records commit separately from the order operation under study. Both tables are in one PostgreSQL database. The first schedule deliberately omits publication after committing order 1. The second commits a broker record, then the order insert fails its CHECK.

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

App> BEGIN;
BEGIN

App> INSERT INTO orders VALUES (1, 'alice', 90);
INSERT 0 1

App> COMMIT;
COMMIT

Publication is deliberately omitted. No process crashes; the broker table has no order-1 event.

App> SELECT (SELECT count(*)::int FROM orders) AS orders,
            (SELECT count(*)::int 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');
INSERT 0 1

The order INSERT fails its CHECK constraint after the separately committed broker record.

App> INSERT INTO orders VALUES (2, 'mallory', -5); -- check_violation
ERROR:  23514: new row for relation "orders" violates check constraint "orders_amount_check"

The receiver table has an event without an order. No downstream service is executed.

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

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

The assertions show one order without an event and one event without its order. No process is killed and no downstream consumer runs. These are modeled boundary observations, not proof of permanent disagreement in every system. If both writes used the same database transaction, this split would disappear.

The fix: write order and event intent together

Write the order and the outbox row in the same transaction:

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

App> BEGIN;
BEGIN

App> INSERT INTO orders VALUES (1, 'alice', 90);
INSERT 0 1

App> INSERT INTO outbox (event) VALUES ('order_placed: order 1');
INSERT 0 1

App> COMMIT;
COMMIT

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

App> BEGIN;
BEGIN

App> INSERT INTO orders VALUES (2, 'bob', 75);
INSERT 0 1

App> INSERT INTO outbox (event) VALUES ('order_placed: order 2');
INSERT 0 1

App> ROLLBACK;
ROLLBACK

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

App> SELECT id, customer, amount FROM orders ORDER BY id;
 id | customer | amount 
----+----------+--------
  1 | alice    |     90 
(1 row)

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

Relay> BEGIN;
BEGIN

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

A real relay would publish outside this transaction. Here publication is narration only; the DELETE is executed and explicitly rolled back.

Relay> DELETE FROM outbox WHERE id = 1;
DELETE 1

Relay> ROLLBACK;
ROLLBACK

ROLLBACK restored the deleted row. The relay selects it again and commits its deletion. No receiver effect or delivery count is recorded here.

Relay> BEGIN;
BEGIN

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;
DELETE 1

Relay> COMMIT;
COMMIT

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

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

The assertions check committed order 1 and its event, and the absence of rolled-back order 2 and its event. The relay locks the available row, deletes it, and rolls back explicitly. The row is selectable again; the next delete commits and the pending count becomes zero. This is Demonstrated behavior of outbox state.

Delivery requires a separate protocol

The relay's publication step is explanatory narration only: this scenario records no receiver effect. It proves neither delivery nor a repeated effect. Its SKIP LOCKED selection protects database row selection under the stated worker protocol, not external execution.

† If a relay publishes successfully before committing removal of the outbox row, a failure in between leaves the row eligible for another publication. That duplicate window follows from the two separate commit boundaries. This is an Entailed guarantee, not an observed receiver count here. An at-least-once delivery design also requires durable event retention, continued retries, and an available receiver; this SQL alone cannot ensure them. Consumers must handle repeats under their own effect boundary.

LISTEN/NOTIFY can signal committed work to a connected listener. Retain polling or another recovery path for missed notifications; no relay latency is measured here.

The anomaly catalog

The dual-write problem & the transactional outbox