Dual writes & the transactional outbox

A local transaction can group transactional database writes. An independently committed external effect is outside that rollback boundary. This page describes the boundary modeled by the PostgreSQL and MySQL Scenarios; neither runs Kafka, HTTP, or a delivery service. Coordinated distributed protocols have different prerequisites, covered in two-phase commit and XA.

The dual-write problem

The two schedules deliberately commit an order without its event, or commit a database-local broker stand-in before the order fails. Both assert the resulting mismatched records. The separately committed stand-in models a recipient outside the application transaction even though both tables are in one database. No process is killed or downstream consumer observed. These are failure windows, not proof that every write ordering permanently loses or invents an event. Putting both local writes in one transaction would remove that modeled split.

The existing illustrative timeline describes an omitted external publication, not an executed crash:

App

Receiver

INSERT order, COMMIT← database commit

publication omitted← modeled failure window

no publication recorded by this model

Write order and intent together

Insert the order and outbox row in one database transaction. Both engine Scenarios assert the committed pair and absence of the rolled-back pair. A relay then locks and deletes an event, explicitly rolls back, selects it again, and commits its deletion. The final pending count is zero.

† The database-local pair shares one commit boundary, provided both writes are transactional and no implicit commit separates them. This follows from atomicity, not from testing every failure. It covers no external publication. The order/outbox assertions demonstrate the stated commit and rollback schedules.

The existing illustrative recipe separates the two boundaries:

App

Relay

BEGIN

INSERT INTO orders

INSERT INTO outbox← same transaction

COMMIT← database-local pair

SELECT FROM outbox FOR UPDATE SKIP LOCKED

publish externally← narration, not observed here

DELETE FROM outbox, COMMIT← separate database completion

Delivery remains conditional

† If an external publication succeeds before deletion commits, a failure between those operations can leave the row available for another publication. This duplicate-window inference follows from separate effect and database boundaries. No Transcript here records a repeated receiver effect, and explicit rollback is not a real crashed relay.

At-least-once delivery requires durable retention, continued retries, and an available receiver. The SQL does not establish those conditions. Consumers need their own retained identity and deduplication protocol for their effect boundary. Database-local idempotency is not a general exactly-once external-delivery guarantee. The source outbox design describes the pattern, not additional execution by this project.

See the modeled boundary

• PostgreSQL outbox: database state assertions and explicit relay rollback. LISTEN/NOTIFY observes notifications for a connected listener; it provides no durable replay or measured relay-latency bound.
• MySQL outbox: the same local boundary under InnoDB's transactional conditions. Polling and binlog-based relay scheduling are design options, not implemented delivery tests here.

Further reading

• Transactional outbox
• Polling publisher