Why do tracked objects go stale after an insert-or-update statement issued outside the tracked set?
answer
- the layer sees only its own writes
- snapshot in memory, row moved on
- version behind means clobber or conflict
- overlap is the whole problem
- reorder, refresh, or isolate
basics
~20 sThe layer only knows about writes it made itself. A statement sent around it moves the rows while tracked objects keep their old values and old version numbers, so a later write-out can overwrite the newer row.
solid answer
~50 sA tracked set is a cache of what the layer believes the rows contain. Sending a single insert-or-update statement on the same connection but outside that set changes the rows without telling it, so three things drift. Field values in memory are now older than the row, and writing the tracked object later reinstates them - a lost update. A mapped **version** in memory is behind the row's, so the next guarded update either fails on the version check or, if the bulk statement never bumped the version, succeeds and clobbers. For inserted rows there is no in-memory object at all, and a load in the same unit of work may still hand back a cached instance. The fixes are ordering and invalidation: do the bulk write before loading anything, or refresh or discard the affected objects afterwards.
go deeper
The takeaway is that a data-access layer only knows about the writes it makes. A statement sent around it leaves the objects already in memory holding older values than the rows they came from.
Name what drifts: field values, the version value, generated keys and identity within the unit of work. Be able to say that a later write-out of a stale object silently reverses the bulk change.
Distinguish the loud failure from the silent one. A bulk statement that bumps the version forces stale writers to fail; one that does not lets them clobber, and that is the bug you should design the statement to prevent.
Treat it as ownership, not technique. Decide which path owns writes to a table, make the boundary explicit in the code layout, and require bulk statements to maintain whatever guards the tracked path relies on.
Most applications end up with two write paths: objects that the layer tracks and writes for you, and statements the code sends directly because a set-based write is the right tool. Both are legitimate. The trouble is that only one of them is visible to the tracked set. ## Why the layer cannot see it A tracked set is a **cache with a write-back plan**. For each managed object it holds the values it believes the row contains - often a snapshot taken at load time - plus whatever the application has since changed. Nothing in that design observes the store. A statement the layer did not emit produces no callback, no invalidation and no event; the rows move and the layer's picture does not. This is true even when the direct statement runs on the same connection inside the same transaction. Same connection means the change is *visible to a fresh read*; it does not mean the objects already in memory are updated. ## The four things that go stale 1. **Field values.** The object still holds the pre-statement values. If anything later marks that object as changed, the write-out sends the whole tracked state and quietly reverses the bulk write for those rows. This is a lost update with no conflict reported, because from the layer's point of view nothing conflicted. 2. **The version value.** Where a mapped version column guards updates, the in-memory version is now behind the row's. Two sub-cases matter, and they fail in opposite directions: - the bulk statement **did** bump the version: the next guarded update matches zero rows and the layer raises a conflict. Noisy, but safe. - the bulk statement **did not** bump it: the guarded update matches, and the older in-memory state overwrites the newer row with no error at all. Silent, and worse. 3. **Generated keys.** A row the bulk statement inserted has no object behind it. If the application built an object for that data and expected the save path to fill in the key, it is still keyless, and code that treats a keyless object as new can insert the row a second time. 4. **Identity.** Within one unit of work, a repeat load of a key already in the set can be answered from memory rather than from the store, so the "re-read to check" that a developer reaches for may hand back exactly the stale object they were trying to escape. ## What does not go stale Rows nobody has loaded are simply fine. So is a unit of work opened *after* the bulk statement committed, because its first load reads current values. The problem is strictly the **overlap**: objects loaded before the statement and still tracked afterwards. ## Making the two paths safe together | Approach | What it does | When it fits | |---|---|---| | Order the work | run the bulk statement first, then load and work with objects | batch jobs and imports where the phases are separable | | Invalidate afterwards | refresh or evict the objects the statement touched | a targeted statement whose affected rows are known | | Isolate the statement | run it in its own short unit of work with nothing else tracked | maintenance and administrative writes | | Split ownership | let one path own a table's writes, not both | high-volume tables where a mistake is expensive | Refreshing has a cost worth naming: it discards unwritten edits on those objects, which is correct here - those edits were computed from stale values - but it is a real loss if the code assumed they would survive. ## Diagnosing it after the fact The symptom is usually "the bulk update ran, the row count was right, and an hour later the old values were back". Look for a unit of work that spans the direct statement, and check whether the affected rows were already loaded in it. A version conflict raised shortly after a maintenance script is the *good* version of this bug - the guard did its job. The silent variant, where the version was never bumped, is the one to hunt for, and the fix is usually to make the bulk statement bump the version column itself so that stale writers are forced to fail.
- Does running the direct statement on the same connection and transaction avoid the problem?No. Same connection makes the change visible to a new read, but the tracked objects are in memory and nothing refreshes them. In fact it is slightly worse: because everything is one transaction, the stale write-out lands on the same rows at commit with no isolation error to hint at what happened.
- Should a bulk statement update the version column, and why?Yes, where one is mapped. Bumping it converts the dangerous silent case into the safe loud one: any stale in-memory object that tries to write afterwards fails its version check instead of overwriting the newer row. It costs nothing in the statement and turns an invisible lost update into a conflict the caller can retry.
- Why can re-reading the row fail to give you fresh values?Within one unit of work, a lookup by a key the set already holds can be answered from the set itself rather than from the store, so you get the same stale object back. Getting current values requires an explicit refresh of that object, or discarding it from the set first, or doing the read in a new unit of work.
saying these in an interview costs you the question
- Assumes the tracked set notices statements it did not issue
- Says the same connection keeps in-memory objects current
- Believes re-reading in the same unit of work always hits the store
- Thinks a version column protects a write that never bumped it
- Forgets that a directly inserted row has no object or key in memory