TransactionsGENERALDATABASE-SPECIFICSIMPLIFIED

The Dual Write Problem

The commit succeeds and the publish fails, or the publish succeeds and the commit rolls back. Two systems, no shared transaction, and no ordering that fixes it.

What actually happens How to build it

The requirement, the obvious build, and why it breaks

Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.

The question

How do I make a database write and a message publish either both happen or neither?

The requirement

When an order is placed, the order row must be written and an order.placed event must reach the message broker so fulfilment, analytics and the search index all learn about it.

The obvious build

Commit the transaction, then publish the event. If the publish throws, log it and alert — it will be rare, and the order is at least saved.

Why it breaks

Commit succeeds, publish fails. The order exists and nothing downstream knows. Fulfilment never ships it. There is no error visible to the customer, and the inconsistency is permanent.

How it breaks in production

Commit succeeds, publish fails. The order exists and nothing downstream knows. Fulfilment never ships it. There is no error visible to the customer, and the inconsistency is permanent.
Publish first instead? Then a publish succeeds and the transaction rolls back: downstream systems process an order that does not exist, and the event cannot be recalled.
Publish inside the transaction? The publish is a network call to another system, so it is not covered by the rollback — and it is now also holding locks and a pooled connection (External Calls Inside a Transaction).
The process can die between the two operations. No catch block runs, because there is no process left to run it.
"Log it and alert" means a human is the consistency mechanism, at whatever rate the failure occurs multiplied by your traffic.

Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale

What is actually happening

Two independent systems, each with its own durability, and no transaction spanning both. Whatever order you choose, there is a moment between the two writes where a crash leaves them disagreeing.
The four outcomes are: both succeed (fine), both fail (fine), first succeeds and second fails, second succeeds and first is rolled back. Only the last two are problems, and reordering the operations just swaps which one you get.
Retrying the second operation narrows the window and does not close it, because the retry loop itself lives in a process that can be killed.
Distributed transactions (two-phase commit, XA) genuinely solve this and are rarely available: most message brokers do not participate, the coordinator becomes a availability bottleneck, and locks are held across the prepare phase (Distributed Transactions).
The resolution is to stop having two writes. Make the event a row in the same database, written in the same transaction, and let a separate process turn rows into messages (The Transactional Outbox).
That converts an atomicity problem across two systems into a delivery problem within one — and delivery problems have known answers: retry, at-least-once, idempotent consumers.

Four outcomes, and reordering does not help

The whole problem is contained in one table. Two operations, each of which can succeed or fail independently, with no mechanism binding them. Whichever you do first, one of the mixed outcomes is possible — and that outcome is silent.

Notice that the failure has no error surface. The request returns 200 to the customer. The database is consistent. The broker is healthy. The only evidence is that a downstream system never heard about something that definitely happened.

Order chosen	Failure between them	Resulting state	Detected by
Commit, then publish	Publish fails or process dies	Order exists, nobody downstream knows	A customer asking where their order is
Publish, then commit	Transaction rolls back	Downstream acts on an order that does not exist	A downstream error, hours later
Publish inside the transaction	Transaction rolls back after publish	Same as above, plus locks held during a network call	Both problems at once
Retry the publish in a loop	Process killed mid-loop	Same as row one	Nothing — the loop died with the process
Outbox row inside the transaction	Reader fails or lags	Event delayed, never lost	Unpublished row count and age (The Transactional Outbox)

Why the obvious mitigations do not close it

Every team meets this problem and reaches for the same three fixes in the same order. Each is reasonable and each leaves the window open, for a reason worth understanding rather than memorising.

Try/catch around the publish — handles a broker error and not a process death. The window is smaller; it is still there, and a deploy will land in it.
Retry with backoff inside the request — same limitation, plus it now holds the request open during a broker outage and consumes capacity (Backoff and Jitter).
Compensating delete of the order — an undo that is itself a write that can fail, and one that destroys real customer data to resolve an internal messaging problem.
Two-phase commit — genuinely atomic and genuinely unavailable: brokers rarely participate, and the coordinator turns two independent failures into one shared one (Distributed Transactions).
Idempotent consumers alone — necessary, and insufficient. Idempotency makes duplicates safe; it does nothing for a message that was never sent (Job Idempotency).
Reconciliation job — genuinely useful as a backstop, and it detects divergence rather than preventing it. The window is the job interval.

The window nothing covers

UserLLMAgentToolDataDecisionHumanGuardrail

Stop having two writes

The resolution is not a better ordering or a better retry. It is to make both writes go to the same system, so one commit decides both. The event becomes a row in an outbox table, written inside the same transaction as the order.

After the commit, the fact that the event must be published is durable — it is in the database, exactly as durably as the order itself. A separate reader turns those rows into messages, and if it crashes, restarts, or runs twice, the row is still there. What you have bought is that no event can ever be lost; what you have accepted is that some events will be delivered more than once (The Transactional Outbox).

One commit, two facts

1-- Both statements are in the same transaction. Either both are
2-- durable or neither is. There is no window between them.
3BEGIN;
4 
5INSERT INTO orders (id, tenant_id, total, status)
6VALUES ($1, $2, $3, 'placed');
7 
8INSERT INTO outbox (id, topic, payload, created_at)
9VALUES ($4, 'order.placed', $5, now());
10 
11COMMIT;
12 
13-- A separate process publishes rows from `outbox` and marks them
14-- published. It may publish the same row twice after a crash;
15-- it can never publish a row for an order that was rolled back,
16-- and it can never miss one for an order that was committed.

The property being bought is precise: publication is now at-least-once instead of at-most-once. That is a strictly better failure mode, because a duplicate can be absorbed by an idempotent consumer and a missing event cannot be absorbed by anything.

How to build it

Most important first.

Write the event to an outbox table inside the same transaction as the state change. One commit, one atomic outcome (The Transactional Outbox).
Publish from a separate reader — a poller or a change-data-capture stream — and mark rows published after the broker acknowledges.
Accept at-least-once publication and require consumers to be idempotent. This is not a compromise, it is the actual semantics (At-Least-Once Delivery).
Where an outbox is too much machinery, make the downstream state derivable: a reconciliation job that finds orders with no corresponding downstream record and republishes.
Never make the event the source of truth for something the database already knows. If the event is lost, it should be reconstructable from the row.
If you genuinely need synchronous cross-system atomicity, reconsider the boundary: two things that must be atomic usually want to be in one database (The Modular Monolith).

What can go wrong

Failure modes

Silent divergence: the database and the downstream system disagree, and nothing detects it because each is internally consistent.
Alert fatigue from "publish failed" logs that nobody can act on individually.
A compensating delete that tries to undo the committed row after a failed publish, which is itself a write that can fail — and which loses the customer's order to fix an internal problem.
Retry-until-success inside the request, which turns a broker outage into request timeouts and a saturated pool.
Discovering the divergence months later, when the two systems are reconciled for the first time and the numbers do not match.
The outbox itself failing: the reader stops and nobody notices, so events pile up and downstream falls arbitrarily far behind.

What can race

The crash window between commit and publish is a race with the process lifecycle itself — a deploy, an OOM kill or a scale-in lands there eventually (Graceful Shutdown).
Publish-then-commit produces a race with the consumer: the consumer reads back the entity before the transaction commits and finds nothing, so the event looks spurious (Eventual Consistency in Practice).
Two outbox readers publishing the same row concurrently, which is why the claim must be a locking read rather than a plain select (The Transactional Outbox).

Security

Events frequently carry more data than the consumer needs. Publish identifiers and let consumers fetch what they are authorized to see, rather than embedding personal data in a broadcast (Naming Events).
A replayed or duplicated event must not be able to re-authorize anything. Consumers that make security decisions from events need the same idempotency guarantees as any other consumer (Webhook Idempotency).
Divergence between a database and a downstream authorization cache is a security failure, not just a consistency one: a revoked permission that never propagated is still in force (Cache Invalidation).

Misreads

"Publish inside the transaction and it will roll back with it." A network call is not part of the database transaction. Nothing rolls it back.
"Just retry the publish." The retry helps with transient broker errors and does nothing about a process that dies between the commit and the retry.
"Use exactly-once delivery." Brokers that advertise it mean something specific and narrow, usually within their own boundaries. Your business effect still needs to be idempotent — delivery semantics and processing semantics are different properties (Queue Semantics).
"Publish first, then commit — the event is more important." Now you emit events for things that never happened, which is worse: consumers act on them and cannot un-act.
"This is only a microservices problem." Any second system counts: a search index, a cache, an analytics pipeline, a webhook, a third-party CRM (Keeping a Search Index in Sync).

Operating it

How you see it in production

A reconciliation count: rows in the database with no corresponding downstream record, measured continuously rather than during incidents.
Publish failure rate, and separately, publish attempts that were never made because the process died. The second is invisible without an outbox.
End-to-end lag from commit timestamp to downstream processing timestamp (Depth Is Not an Emergency; Age Is).
For outbox implementations: unpublished row count and the age of the oldest unpublished row. Those two numbers are the health of the whole mechanism.

What changes at 10x and 100x

At low volume the failure is rare enough to look like it does not exist, which is exactly why it ships. At 10x traffic it happens ten times as often and starts being noticed as "data problems".
At higher scale the number of downstream consumers grows, so a lost event diverges several systems at once and the reconciliation cost multiplies.
Change-data-capture becomes attractive at scale because it removes the polling load and reads the write-ahead log the database already produces (Write-Ahead Logging).

What this costs

The outbox gives atomicity and costs you a table, a background process, publication latency, and at-least-once semantics that every consumer must handle.
Reconciliation instead of an outbox is cheaper to build and leaves a window of divergence whose length is the reconciliation interval.
Two-phase commit gives true atomicity and costs availability: if the coordinator or any participant is down, nobody commits (CAP and Distributed Systems).

Where this applies

Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.

GENERALApplies to any pair of systems without a shared transaction — database and broker, database and search index, database and third-party API.
DATABASE-SPECIFICPostgres and MySQL both support XA two-phase commit, but the common brokers (Kafka, SQS, RabbitMQ in typical configurations) do not participate as XA resources, so the theoretical solution is usually unavailable in practice. Where a broker and a database are the same product — a queue table in the database — the problem disappears entirely, which is a legitimate design choice at moderate scale.
SIMPLIFIEDDescribed with one database and one broker. Real systems often have several consumers with different delivery guarantees, and partial divergence — two of four systems updated — is the normal shape of the incident.

Where the depth lives

This domain teaches the application-side mechanism and hands the rest off.

API Designidempotency webhook-delivery

Databasetransactions-and-acid write-ahead-logging distributed-consistency

Architecturedistributed-transactions event-driven-architecture message-queues saga-pattern cap-and-partitions

Performancequeue-age

Domains that do not exist yet

— Distributed Systems — why atomic commitment across independent failure domains costs availability, and what the impossibility results actually say.