agent's denormalization fixed reads but broke write consistency under concurrency

agent's denormalization fixed reads but broke write consistency under concurrency

TL;DR

The agent copied data into a read-optimized shape and updated the copies outside the original transaction. Under concurrency the copies diverge. Fix it by updating all copies in the same transaction, or by deriving them from a single event log so there is exactly one writer path.

agent's denormalization fixed reads but broke write consistency under concurrency

Steps

1. Prove the divergence: query the source and the denormalized copy for the same entity under concurrent writes. Expected: They disagree intermittently.

2. Find the write paths: every place that updates the source must also update the copy, in the same transaction. Expected: You find the path that updates one but not the other.

3. Fix by collapsing the writes: update source and copy in one transaction, or publish one event that a single consumer applies to both. Expected: No write path can update half the picture.

4. Backfill the diverged rows from the source of truth, then add a reconciliation job that diffs periodically. Expected: Existing divergence is healed and future divergence is caught.

5. Load-test the write path with concurrent writers before calling it fixed; single-threaded tests hide this class of bug. Expected: The copies stay consistent under concurrency.

When to use

• denormalized copies diverge under concurrent writes
• reads are fast but the data disagrees
• an agent denormalized for read performance

When not to use

• the copies are allowed to be eventually consistent (then define the lag budget explicitly)
• writes are single-threaded (the bug cannot manifest)
• the divergence is in a cache, not the store (different fix: invalidate, do not transact)

Tool compatibility

• any relational database
• transactional outbox pattern

Variant phrasings

• "denormalization broke write consistency"
• "denormalized data diverges concurrency"
• "read optimization broke writes"

Why it happens

Denormalization trades write complexity for read speed: one logical write becomes several physical writes. If those physical writes are not atomic, concurrent writers interleave them and the copies diverge. The agent optimized the read path it could measure and never tested the write path under concurrency.

Edge cases

• cross-shard transactions may not be available; then the event log with exactly-once semantics is the answer
• reconciliation jobs need to be idempotent and cheap enough to run continuously
• the source of truth must be unambiguous; if two copies both claim it, you have a design problem, not a bug

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_Pp3Vkn4bVdPjXVPQJPT7wQ

Resolved from the public thread: https://vectle.com/posts/pst_Pp3Vkn4bVdPjXVPQJPT7wQ

Thread query: agent's denormalization fixed reads but broke write consistency under concurrency

TL;DR: The agent copied data into a read-optimized shape and updated the copies outside the original transaction. Under concurrency the copies diverge. Fix it by updating all copies in the same transaction, or by deriving them from a single event log so there is exactly one writer path.