A request writes a row, then a read-only unit routed to a replica reads it back stale - what causes this and what fixes it?

## What actually happened Three independent facts combine into the bug: 1. **Replication is asynchronous.** A commit on the primary returns before replicas have applied it. The window is usually milliseconds and occasionally seconds, and it widens exactly when the system is busy. 2. **Routing is per unit of work.** The layer chooses a datasource from what the unit declares at its boundary. A read-only unit declares that it will not write; it does not declare anything about how fresh its data must be. 3. **Units in one request do not know about each other.** The second unit has no idea the first one wrote the very row it is about to read. So the write lands on the primary, the read is served from a replica that is a few hundred milliseconds behind, and the response shows the pre-write value. The user, who has just pressed save, sees their change vanish. Nothing errored, and every component did its job. ## Why this is the layer's problem, not just the database's A replica being behind is expected and normal. What turns it into a defect is the routing rule: **read-only was treated as a synonym for stale-tolerant**. Those are different properties. A list of last month's invoices is both; a re-read of the row the user just edited is read-only but absolutely not stale-tolerant. ## Pinning: the request-scoped fix The mechanism that data-access layers use is a pin held for the life of the request: 1. A unit of work commits a write. 2. The layer sets a **request-scoped flag** - conceptually "this request has written". 3. At every later boundary in that request, the routing layer sees the flag and returns a primary connection, whatever the unit's own marking says. 4. The flag dies with the request. It is deliberately coarse. It does not track which rows were written, so it over-pins: an unrelated read later in the same request also goes to the primary. That is the point - the cheap version is correct and the precise version needs to know what the read will touch, which the layer does not. | approach | freshness it buys | what it costs | |---|---|---| | pin the rest of the request to the primary | own writes always visible | primary carries the tail of the request | | pin a user for a short window after a write | own writes visible across requests too | needs shared state and a window guess | | wait on a replica for a recorded replication position | fresh reads on the replica itself | added latency, and a fallback when it lags | | route this one read explicitly to the primary | precise | someone must know which reads need it | ## The traps around the pin - **It must travel with the request.** If the pin lives in state attached to the handling thread and the work continues on another, it is gone and the read is silently routed away again. Anything asynchronous needs it propagated deliberately. - **Background work has no request.** A job triggered by a write has no request scope to carry a pin; it either reads from the primary by default or tolerates lag by design. - **The pin cannot fix cross-request reads.** The next request from the same user starts clean. If own-write visibility must survive that, the window has to be held somewhere shared, keyed by user, with all the expiry questions that follow. - **Caches and identity maps are the second door.** A copy loaded on one datasource must not answer a read routed to another; if a shared cache is not keyed by datasource, pinning to the primary buys nothing because the stale copy is served without any query at all. The mirror case also bites: a cache populated from a replica keeps serving the pre-write value after the pin has already sent queries to the primary. ## Diagnosing it in production The signature is a read-your-own-write failure that is intermittent, load-correlated, and impossible to reproduce on a single-node development setup. Useful evidence: - replication delay metrics correlated with the complaint timestamps; - which datasource served the failing read, which is worth logging per unit; - whether the value appears correct on a retry seconds later, which distinguishes lag from a genuinely lost write; - whether the row is right in the database but wrong in the response, which points at a cache rather than at routing. That last check matters, because a dropped write inside a mis-marked read-only unit produces the same user complaint with a completely different cause: there, the row on the primary is wrong too. ## The judgement to state out loud Pinning trades replica offload for correctness on the one path where users notice most. Take it by default and remove it only where an endpoint has an explicit, agreed tolerance for staleness. Freshness is a property of the endpoint's contract, not of the repository method it happens to call.