Dual Writes and the Divergence They Inevitably Produce

During a migration, the legacy store and the new store both need the current data. The obvious way to keep them current is to write both from the application: after a successful update to the legacy table, issue the corresponding update to the new one. This is the dual write, and it is a common source of corruption in incremental migrations.

The reason is structural rather than a matter of careful coding. Two writes to two independent systems are two separate transactions, and no amount of error handling makes them one. Between them there is a window in which one side has committed and the other has not, and the process can stop inside that window. Even when both succeed, two concurrent requests can interleave so that the two stores end up with different final values.

The interleaving that loses an update

Take two requests, each incrementing a counter from 10 to 11 and 12 respectively, and two stores that apply writes in the order they arrive.

Request A writes the legacy store (10 → 11). Request B writes the legacy store (11 → 12). Request B then writes the new store (10 → 11). Request A then writes the new store (11 → 11). The legacy store holds 12, the new store holds 11, and both operations reported success. Nothing failed; the order of arrival at each store differed because the two writes are independent network calls with independent scheduling.

The general statement: without a shared ordering authority, the two stores are two replicas with no replication protocol, and their states diverge under concurrency. Last-writer-wins at each store is not a conflict resolution strategy, because each store sees a different “last”.

The window can also be crossed by a crash. A process that commits to the legacy store and dies before writing to the new one leaves a permanent gap. Applications typically wrap the second write in a retry, which converts the problem into duplication rather than loss: the retry may reapply an update that partially succeeded, or may execute after a later update and resurrect a stale value.

Why compensation does not close the window

The standard engineering response is to write the legacy store, then write the new store, and on failure of the second, undo the first. Compensation narrows the window without closing it, for two reasons.

First, the compensating write can fail for the same reason the original did, and then the system is in the state compensation was meant to prevent, with no further recourse. Second, a compensation that reverses a value rather than a delta is wrong under concurrency: if another request modified the row between the forward write and the compensation, restoring the original value discards that modification.

Two-phase commit exists precisely to avoid this. In XA-style systems the coordinator asks both participants to prepare, then commits them both, and the participants hold locks in between. It works, at the cost of a synchronous coordinator on the write path, locks held across a network round trip (or several), and a recovery log that must be correct for the coordinator to resolve in-doubt transactions after a crash. The operational cost is high enough that the pattern is rare in new systems, and legacy systems that already use it usually keep it as an isolated island.

Moving the second write inside one transaction

The alternative that removes the window is the transactional outbox. Instead of writing to the second store directly, the application writes a record of the intended change into a table in the same database as the primary write, in the same transaction. Either both the business row and the outbox row commit, or neither does. A separate relay process reads committed outbox rows in order and applies them to the second store.

BEGIN;

WITH debited AS (
    UPDATE account
       SET balance_minor = balance_minor - 2500
     WHERE id = 8814
       AND balance_minor >= 2500
    RETURNING id, balance_minor
)
INSERT INTO outbox (aggregate_id, event_type, payload, occurred_at)
SELECT id, 'account.debited',
       jsonb_build_object('account_id', id,
                          'amount_minor', 2500,
                          'balance_minor', balance_minor),
       now()
  FROM debited;

COMMIT;

The update and the outbox insert share a transaction identifier, so they become visible atomically. The RETURNING clause is load-bearing: when the balance guard matches no row, no outbox record is written either, so a rejected debit produces neither a ledger change nor an event. The relay delivers at least once: it reads a batch, publishes, then marks the rows as dispatched. A crash between publishing and marking causes redelivery, not loss, which shifts the requirement onto the consumer: every applied event must be idempotent, keyed by the outbox row’s identifier. That identifier is also the ordering key. If the relay publishes rows in commit order for a given aggregate, the second store applies them in the same order the primary store did.

Ordering across aggregates is a different problem and usually does not need solving. Ordering within an aggregate is what preserves invariants, and it is achieved by publishing per-aggregate in commit order or by having the consumer discard events older than the version it has already applied. Version numbers stored on the aggregate make the second option cheap.

Log tailing instead of an outbox

When the legacy application cannot be modified to write an outbox, the change stream can be taken from the database’s own write-ahead log. Logical decoding in PostgreSQL, the binary log in MySQL, and equivalent mechanisms in other engines expose committed row changes in commit order. A capture process reads that stream and applies it to the destination.

This requires no application change, which is its main advantage, and it inherits the database’s ordering guarantees, which is its second. It also exposes changes made by processes the application does not own: batch jobs, ad-hoc fixes, migrations run by an administrator. Those are precisely the writes a dual-write implementation misses.

The costs are different. The capture process must be operated and monitored, because replication slots that are not consumed retain write-ahead log segments and can fill the disk, a failure mode that turns a lagging consumer into a primary database outage. Schema changes need handling, since the capture output must be interpreted against a possibly newer table definition. And row changes carry no business meaning: a capture stream reports that a column changed, not why, so the destination must reconstruct intent from the before-and-after images, which is fragile for updates that touch several columns at once.

Where divergence still appears

Even with a single ordered stream, the two stores can disagree, and the disagreements are worth classifying because they are detected by different means.

Lost deletes. A hard delete in the source produces a delete event; a destination that models deletion as a status flag sees an update it does not understand. Soft-delete conventions must be translated explicitly, in the anti-corruption layer, not inferred.

Type and precision drift. The source stores a decimal with four fractional digits; the destination stores two. Every value with non-zero trailing digits diverges by a small amount, and the divergence is invisible until a total is compared.

Constraint differences. The destination enforces a uniqueness or foreign-key constraint the source does not. The relay hits a rejection, retries, and blocks, because the event stream for that aggregate cannot advance past a row the destination will never accept. Poison events need a quarantine path and an alert, or the migration stalls silently.

Backfill overlap. Rows written by the initial copy and events applied from the stream can both target the same row. Idempotency and version comparison handle this, which is why the backfill described in backfilling a new store must run under the same versioning scheme as the live stream.

Detecting divergence, when the design cannot avoid it, needs a comparison that runs while both stores are live. The cheap version samples recently modified rows by key, reads both sides, and compares only the fields that both are supposed to hold. Sampling by recent activity concentrates the check where divergence is created, rather than spending most of its reads on rows nobody has touched for a year. The expensive version scans a key range and computes aggregate checksums per range, which catches silent gaps that sampling misses. Either way the result should be a number that trends: a divergence rate that is stable and small is tolerable, a rate that grows is a defect in the replication path, and a sudden step indicates a code change that writes one store only. Without that number the only evidence of divergence is a customer complaint, which arrives long after the cause is recoverable.

The practical conclusion is that dual writes are acceptable only when divergence is tolerable and detectable, such as a search index rebuilt nightly, and not for a store that will become authoritative. For the authoritative case, the second write must be derived from a committed record, and the only place that record can live is the same transaction as the first write.