The usual assumption: when an agent checks an order's status, sees active, and ships an order the customer cancelled a second earlier, the model hallucinated. Fix the prompt.
In a lot of transactional workflows that's the wrong layer. The cancellation committed on the primary. The agent's tool read from an async read replica that hadn't applied it yet. The model reasoned correctly over a row that was already out of date.
Sending agent, RAG and tool traffic to replicas is a sensible way to protect the primary. Fine for dashboards. For anything that reads a row and then acts on it, it's a trap: a human double-checks a stale dashboard, an agent just acts.
Why lag shows up as bad reasoning
With async replication the primary confirms the write first and ships it to replicas afterwards. Until a replica applies it, reads there return the old value. AWS says Aurora replica lag is usually well under 100 ms, but it grows under heavy writes. Two things matter more than the typical size: it's never zero, and nothing guarantees an upper limit.
Kleppmann's Designing Data-Intensive Applications names the anomalies this causes. Three map straight onto agents: read-your-writes (your change goes missing), monotonic reads (two steps hit different replicas and time runs backward), and consistent prefix reads (a dispute shows up before its invoice).
One subtlety catches people. Read-your-writes is a promise to the client that wrote. When a customer cancels and a separate dispatch agent reads, a fix keyed on the user won't help.
What I'd put in the data layer
- Route by risk, pin by entity. Analytical reads can stay on replicas. Reads that drive an action go to the primary, at least for a short window after the entity changes. Key the pin on the order ID, not the user. A 10-second window is a heuristic, not a bound.
- Wait on the log position. Capture the LSN on the primary after the write, then have the replica confirm it has replayed it before you read. PostgreSQL 19 adds
WAIT FOR LSN(brand new, still at release candidate); MySQL hasWAIT_FOR_EXECUTED_GTID_SET. On timeout, your code reads the primary. Don't fake it with aWHERE pg_last_wal_replay_lsn() >= ...filter. That returns zero rows, and the agent concludes the order doesn't exist. - Carry freshness, enforce it in code, make the action conditional. Tools return source LSN, read time and whether they hit the primary. The gateway refuses stale reads; a system-prompt rule is a request, not enforcement. And the write itself checks state:
UPDATE ... WHERE status = 'active'. Zero rows means don't dispatch.
None of this certifies the answer. A fresh read from the wrong table is still wrong, pinning adds load on the primary, and a stale cache in front of it all undoes half the work.
The full article has the diagrams, the Aurora caveats, the sources and the complete four-check audit for your agent tools: https://abivarma.medium.com/the-replication-lag-trap-why-your-ai-agent-hallucinates-on-phantom-data-d19fee072790
