A set-based UPDATE changes thousands of rows without loading them — where does that leave each cache tier?
answer
- the layer never saw the rows change
- every tier keeps pre-image state
- a later flush can undo the bulk write
- sequence first, then evict what is left
basics
~20 sHolding pre-update state, at every tier that already had those rows. The statement runs in the database and maps nothing, so tracked objects, shared entries, statement results and application-held copies stay as they were until something is told.
solid answer
~40 sThe layer did not load those rows and generally cannot say which ones the predicate matched, so nothing is updated for it. Objects loaded earlier in the open unit of work still hold pre-update values — and worse, a later flush of a modified one can write those values back over the bulk change unless a version column catches it. Entries in the tier shared across units of work still hold pre-update state. Any result held against a statement that read those tables is now wrong. The application's own store never hears about it at all. The fixes are ordering and eviction: run the statement before loading anything of that type, discard what is tracked afterwards, evict the shared entries for the affected tables, and invalidate the application store yourself.
go deeper
Remember the shape of it: a statement that changes rows the layer never loaded leaves every copy the layer is holding out of date, because nothing told those copies anything.
Go tier by tier and say what each still holds and who can fix it, including the fact that objects tracked before the statement keep their pre-update values.
Lead with the lost-update risk and the sequencing that avoids it, then treat eviction as the fallback for whatever was already loaded or already cached.
Weigh the coordination cost against the statement's saving: the more tiers are switched on, the more a bulk write costs the system even as it costs the database less.
## What the statement actually does A set-based write — one `UPDATE ... WHERE` or `DELETE ... WHERE` that changes many rows in the database, without loading any of them — is the cheapest way to change a lot of data and the fastest way to desynchronise every cache tier at once. The reason is structural. The layer did not load those rows, did not build objects for them, and in the general case does not know which rows the predicate matched. It sent a statement; the database changed rows; nothing came back that identifies them. So the question "where does that leave each tier?" has the same answer for all of them — **holding pre-update state** — and different consequences in each. | Tier | State after the write | Who can fix it | |---|---|---| | Unit-of-work map | objects loaded earlier still hold pre-update values | discard them, or open a fresh unit of work | | Shared tier | entries for affected rows still hold pre-update state | eviction, if the layer is told which rows or tables | | Statement-result tier | results over the affected tables are now wrong | the layer, when it knows the statement's tables | | Application store | entirely unaware the write happened | only the application | ## The trap that actually bites: the write is undone The stale reads are the obvious half. The dangerous half is a write. If a unit of work loaded and modified some of the affected objects **before** the set-based statement ran, those objects are still tracked, and their in-memory state still reflects the pre-update row. When the unit of work flushes, it emits updates built from that state — and those updates can overwrite the columns the set-based statement just changed. The bulk write appears to succeed and is then silently reverted for exactly the rows someone had open. Two things reduce the blast radius, neither completely: - a **version column** checked in the update's where clause turns the silent overwrite into a failed update the layer can report; - layers that flush pending changes before executing a statement at least ensure the set-based write sees them, rather than racing them — but that ordering does nothing for objects modified *after* the statement. ## Restoring agreement, tier by tier 1. **Sequence the work.** Run the set-based statement before the unit of work has loaded anything of the affected type. The cheapest invalidation is having nothing to invalidate. 2. **Drop what is tracked.** After the statement, discard the affected objects from the unit of work — or finish the unit of work entirely — so nothing can flush pre-update values over the change. 3. **Tell the shared tier.** If the layer's own bulk-write facility is used, it usually knows which tables were touched and can evict the matching entries or regions. A statement sent through the raw escape hatch gives it nothing, so the eviction becomes your call to make. 4. **Void the statement-result entries.** Anything held against a statement that read the affected tables is now suspect; the layer voids what it knows about, and the rest is manual. 5. **Invalidate the application store yourself.** No mechanism inside the mapper will ever reach it. ## What this implies about design - A set-based write is not simply "the fast version" of a loop of updates. It is a different contract: it trades the layer's bookkeeping for the database's efficiency, and the bookkeeping is what kept the tiers honest. - The more tiers a system has switched on, the more expensive a set-based write becomes in coordination, even though the statement itself gets cheaper. Nightly batch work usually accepts this by running with the tiers cold or evicting wholesale afterwards; request-path bulk writes usually cannot. - If a bulk update is routine on a type, that type is a poor candidate for a shared tier at all. Widespread invalidation on every write is a tier that costs memory and delivers few hits. - A stale entry after a bulk write looks exactly like a correct one to the code that reads it. The evidence is external: the count of rows the statement reported, against what the application subsequently displays. ## The honest caveats How much a layer can do for you here varies. Some let a bulk operation declare the tables it touched and invalidate accordingly; some invalidate a whole region on any write to it; some do nothing at all and expect the application to know. Set-based statements written by hand and sent through the escape hatch get the least help everywhere, because the layer never parsed them. Assume nothing is invalidated for you until you have seen the layer do it.
- How can a bulk write end up silently reverted rather than merely stale?An object loaded and modified before the statement is still tracked with pre-update values. Flushing it emits an update built from that state, which can overwrite the columns the bulk statement changed. A version column in the update's where clause turns the silent overwrite into a failed update instead.
- Why does a hand-written bulk statement get less help than the layer's own bulk facility?Because the layer never parsed it. Its own facility knows which type, and therefore which tables and cache regions, the operation touched, and can evict accordingly. A statement sent through the escape hatch is opaque text, so no tier can be told anything about it automatically.
- What does routine bulk updating imply about admitting a type to a shared tier?That it is probably the wrong type to admit. Every bulk write forces broad invalidation, so the tier spends memory holding entries that are repeatedly voided before they earn hits. Types that are read heavily and written rarely are the ones worth the space.
saying these in an interview costs you the question
- Assumes the layer refreshes tracked objects after a bulk statement
- Thinks only the shared tier goes stale, not the open unit of work
- Expects the application's own store to be invalidated by the mapper
- Treats a set-based write as just a faster loop of updates
- Never considers a flush overwriting the bulk change afterwards