skip to content

Why do dual writes from application code to both a product database and its search index drift out of sync?

level: middleimportance: must knowfreq 64%

answer

  1. two systems, zero shared transactions
  2. crash between commit and index call
  3. commit order versus arrival order
  4. outbox row in the same transaction

basics

~20 s

The two writes share no transaction: a crash or timeout between them leaves one side stale, and concurrent writers can reach the index out of order. Deriving index updates from committed database changes, through an outbox or change capture, closes the gap.

solid answer

~50 s

A dual write commits the product row and then separately calls the search index, and nothing makes those two steps atomic. If the process crashes or the index call times out after the commit, the index keeps the old value and nothing remembers that a write is owed. Two concurrent writers can also commit in one order and reach the index in the other, so the index ends up holding the older value. Retries make it worse unless every write carries a version. The fix is to make the index a **derived view**: write an **outbox** row in the same transaction as the change (or read the database's commit log), feed those events through a queue to an indexer partitioned by product ID, and apply version-guarded, idempotent writes. A periodic reconciliation job catches whatever still slips through.

code

sql · 5 lines
sql
BEGIN;
UPDATE product SET price = 12.00, version = version + 1 WHERE id = 42;
INSERT INTO outbox (aggregate_id, event_type, payload)
  VALUES (42, 'ProductUpdated', '{"id":42,"price":12.00,"version":8}');
COMMIT;

go deeper

for a junior

Remember that two separate writes are not one atomic operation: if the second fails after the first commits, the systems disagree and nothing flags it.

for a middle

Walk through the three drift modes (crash between writes, racing writers, ambiguous retries), then explain how an outbox or commit-log reader makes the index a derived view.

for a senior

Show the operational side: per-product partitioning, version-guarded idempotent writes, resuming from acknowledged positions, and a reconciliation job with a metric for how many mismatches it repairs.

for a principal

Weigh outbox against log-based capture across teams: who owns the event schema, how coupling to the database log affects migrations, and whether one change stream should feed search, caches and analytics alike.

## What a dual write is A **dual write** is when application code, while handling one business operation, writes the same change to two independent systems. In product search those systems are the **transactional database**, which is the source of truth for the catalog, and the **search index**, which serves queries. The code looks harmless: commit the row, then call the index with the new document. The problem is that the two calls share no transaction. The database guarantees atomicity for its own rows, but nothing guarantees that both systems end in the same state. ## Three ways the copies drift 1. **Partial failure.** The process commits the database transaction and then crashes, is redeployed, or times out before the index call succeeds. The database holds the new price and the index keeps the old one indefinitely, because nothing records that an index write is still owed. Reversing the order (index first, then database) fails the other way: search shows a change that was later rolled back. 2. **Races between concurrent writers.** Writer A sets a price to 10 and writer B sets it to 12. The database commits A, then B, so the truth is 12. The two index calls travel independently, and if B's call arrives first and A's second, the index ends at 10. Neither write says which one is newer. 3. **Ambiguous timeouts and retries.** An index call that timed out may or may not have been applied. Retrying it later can overwrite a newer value that another request wrote in between. Giving up leaves a gap. Each case is rare per request, but a catalog taking millions of updates a day will hit all of them. The drift is also **silent**: no error is raised when the two copies disagree, so nobody notices until a shopper sees a wrong price. ## Why the obvious patches do not close the gap - **One transaction around both writes** would need a distributed commit protocol such as two-phase commit. Search indexes generally do not take part in one, and it would tie write-path latency to index health. - **Retrying harder** turns lost writes into out-of-order writes unless each write carries a version the index can compare. - **Writing the index first** just moves the inconsistency to the other side. - **Catching the exception and logging it** records the failure but repairs nothing. ## Deriving the index from committed changes The robust fix is to stop treating the index as a second write target and make it a **derived view** of the database, built only from changes that actually committed. | Approach | How changes are captured | Main trade-off | |---|---|---| | **Transactional outbox** | The application inserts an outbox row describing the change in the same transaction as the product update; a relay publishes outbox rows to a queue or log | Needs application code and outbox cleanup, but the event exists if and only if the business change committed | | **Log-based change capture** | A connector reads the database's write-ahead or commit log and emits one event per committed row change | No application changes, but depends on log access, log retention and the shape of the log records | ```sql BEGIN; UPDATE product SET price = 12.00, version = version + 1 WHERE id = 42; INSERT INTO outbox (aggregate_id, event_type, payload) VALUES (42, 'ProductUpdated', '{"id":42,"price":12.00,"version":8}'); COMMIT; ``` In both approaches an **indexer** consumes the events and writes to the index. Events are partitioned by product ID so that one product's changes stay in order, and each event carries the row's **version** so a duplicate or late event cannot overwrite newer data. Delivery through a queue is normally **at least once**, which is why the writes must be idempotent. If the indexer crashes, it resumes from its last acknowledged position and nothing is lost, only delayed. ## Reconciliation as a safety net Even a derived pipeline benefits from a periodic **reconciliation job**. It compares a version or checksum per product between the database and the index and re-emits the products that differ. It catches indexer bugs, events dropped during an incident, and documents the index rejected. The order of defences is what interviewers listen for: - derive index changes from commits, not from a second call; - version every index write; - reconcile on a schedule. A design that relies on reconciliation alone accepts drift that lasts as long as the reconciliation interval.

  • Why not wrap the database write and the index write in one distributed transaction?
    Search indexes generally do not take part in a two-phase commit protocol, so the option usually does not exist. Where it could be arranged, it would make every catalog write wait on index availability and latency, turning an index outage into a write outage. Deriving the index from committed changes keeps the write path independent and lets the index catch up after it recovers.
  • What does the outbox relay have to guarantee, and what does it not have to guarantee?
    It must eventually publish every committed outbox row, and publish rows for the same product in commit order, typically by partitioning on the product ID. It does not need exactly-once delivery: publishing a row twice after a crash is acceptable because the indexer's writes are idempotent and version-guarded. Once rows are published and acknowledged, the relay can delete or archive them.
  • If the indexer receives only a product ID and reads the current row itself, is ordering still a concern?
    Less so, because each read returns the latest committed state, which makes the pipeline self-healing. It is not fully solved: two workers can read the row at different moments and write their results in the wrong order. Serializing work per product ID, or writing with the row version as a guard, still matters. The cost is an extra database read per event.

Dual writes are like updating a shop's price on the shelf and then phoning head office: if the call drops, nobody knows the two lists disagree. The outbox is writing the change in the shop's own ledger, which head office reads.

saying these in an interview costs you the question

  • Wrapping both calls in a try-catch makes the two writes atomic.
  • Writing to the index first and the database second removes the inconsistency.
  • Retrying a failed index call is always safe without any version check.
  • Drift is impossible if the index call happens in the same request as the commit.
  • Change capture removes the need for idempotent writes in the indexer.