Why can a native read miss your transaction's pending changes, and what must you do after a native write?
answer
- two views, one transaction
- the database sees only what was sent
- flush before, discard after
- the layer cannot read your SQL
- routine writes have unknown blast radius
basics
~20 sPending changes live in memory until the layer sends them, so a native read misses them unless the layer flushes first. After a native write, the tracked copies are stale and must be discarded or refreshed.
solid answer
~50 sA unit of work accumulates changes in memory and sends them as statements later. A native statement, by contrast, goes straight to the database, which knows only what it has already received — so a native read can miss your own uncommitted work. Layers that flush automatically before a query do so by guessing which tables are affected, and for opaque native text they must either **flush everything** or be **told** which tables the statement touches; if neither happens, the read is silently wrong. The mirror problem applies to writes: a native `UPDATE` or `DELETE` changes rows behind the layer's back, so objects already loaded, and any cache the layer keeps beyond the unit of work, still hold pre-write values. The safe bracket is **flush, execute, then discard or refresh** the affected tracked state. Keep the statement's blast radius small enough that you know what to refresh.
go deeper
Hold on to the shape of it: changes you make in memory are not in the database until the layer sends them, and raw SQL only ever sees what the database has already been told.
Explain both directions — flushing before a raw read so it sees pending work, and discarding tracked copies after a raw write — and why the layer cannot work out the affected tables from opaque text.
Show the diagnosis and the bracket in production terms: a report wrong only in requests that also edited something, values that revert after a bulk statement, and a cache beyond the unit of work still serving old rows.
Set the pattern rather than the fix: raw work runs in its own unit of work with a named scope, so correctness never depends on someone remembering a complete eviction list.
## Two clocks, one transaction Above a mapper there are two places the truth can live: the **tracked set** in memory, holding objects you have created or modified but whose statements have not been sent, and the **database**, holding everything that *has* been sent within your transaction. Object-level operations keep those two views reconciled for you. A native statement talks to only one of them — the database — and knows nothing about the other. Every hazard in this topic is a consequence of that one fact. ## Why a native read misses your own changes Suppose your code sets an order's status in memory and then runs a native statement counting orders with that status. Nothing has been sent yet, so the database counts the old world and your count is wrong — not by a race, deterministically, every time. Mappers deal with this by **flushing before a query**: sending accumulated changes so the database is current before the read runs. For a query the layer itself generated, it knows the tables involved and can flush selectively. For native text it does not, so the available behaviours are: - **Flush everything** before any native statement — always correct, sometimes expensive, and it makes the statement's timing sensitive to unrelated pending work. - **Flush only what the caller declares** — the layer accepts a list of affected tables (a "synchronization" set) and flushes just those. - **Flush nothing** — fast, and correct only when there is nothing pending that the statement could see. Know which of these your layer does by default and do not leave it to luck. The failure is quiet: a report that is right in a test with a fresh unit of work and wrong in the request that also edited something. ## Why a native write leaves you holding stale objects Run a native statement that updates or deletes rows and the database moves while your memory does not. Afterwards: 1. **Loaded objects for those rows hold pre-write values.** Nothing told the layer they changed. 2. **Change detection now compares against a stale snapshot.** If such an object is later modified and flushed, the layer writes the fields it believes changed, potentially undoing part of your native write. 3. **Deleted rows can still be represented in memory** by live objects, which will behave as though the row exists until something tries to reach it. 4. **Any cache the layer keeps beyond the current unit of work** may also serve pre-write values to other callers, and it has no reason to invalidate an entry for a statement it did not interpret. ## The bracket 1. **Flush** — get pending changes to the database so the statement sees them. 2. **Execute** the native statement. 3. **Discard or refresh** — evict the affected objects from the tracked set, or reload them, and invalidate the corresponding entries in any longer-lived cache. | Statement | Flush first | Discard after | |---|---|---| | Native read that could see pending work | yes | no | | Native read of untouched tables | not needed | no | | Native write | yes, if pending work overlaps | yes, for everything it touched | | Routine call that writes | yes | yes, and the blast radius may be unknown | The routine row is the awkward one: a routine's writes are opaque, so "what did it touch" may not be answerable from the call site. Where the answer is genuinely unknown, the honest options are to clear the tracked set wholesale after the call, or to run the call in a unit of work of its own that holds nothing worth keeping. ## Doing it well in practice - **Keep the raw statement's scope narrow and named.** A statement you can describe in one sentence is a statement whose refresh list you can write down. - **Prefer a fresh unit of work** for a batch of raw work over surgically evicting objects from a busy one. - **Do not skip the discard because the value "looks right".** It looks right because it is a cached pre-write value. - **Order matters even within a transaction.** Flush-then-execute is not about commit; both halves are inside the same transaction, and the point is only what the database has been *told* so far. - **Test the interleaving, not just the statement.** A test that edits an object and then runs the native read in the same unit of work is the one that catches a missing flush; a test that runs the statement alone never will. ## The one-line summary A native statement sees only what has been sent, and tells the layer nothing about what it did. So send before, and forget after.
- Why can a layer flush selectively for its own queries but not for native text?It generated its own query, so it knows which mapped types and tables are involved and can send only the pending changes that could affect the result. Native text is opaque, so it must flush everything, be told which tables to synchronise, or flush nothing.
- After a native delete, what is wrong with the loaded objects for the deleted rows?They are still live in the tracked set and behave as though their rows exist. Reaching further data through them can fail, and flushing a modification to one can produce an update that matches no row. Evict them, or work in a unit of work that never held them.
- Why is running raw work in its own unit of work often better than evicting objects afterwards?Eviction requires knowing exactly what the statement touched, which is guesswork for anything non-trivial and impossible for an opaque routine. A unit of work that holds nothing you care about cannot go stale, so correctness stops depending on that list being complete.
It is like emailing a colleague a spreadsheet edit while your own unsaved draft is still open on screen. They act on the last version you actually sent, not on your draft, and once they save their change your open copy is out of date until you reload it.
saying these in an interview costs you the question
- Believes the database can see changes the layer has not sent yet
- Thinks flushing is the same as committing the transaction
- Runs a native update and keeps using the objects loaded before it
- Assumes the layer parses native SQL to work out affected tables
- Forgets that a longer-lived cache also holds pre-write values
- Treats a routine call as harmless because it returned no rows