Why does a mapper with a tracked set flush automatically before running a query, and when does that not help?
answer
- the engine sees rows, not objects
- read-after-write inside one transaction
- written out before the query runs
- only for queries the layer issues
- a read that can raise a write error
basics
~20 sA query is evaluated by the database, which knows nothing about unwritten in-memory edits, so its filters ignore them. Many layers therefore flush pending changes before an affected query; that cannot help statements the layer never sees.
solid answer
~40 sPending changes live in the tracked set, not in any table, so a query that filters, joins or aggregates over the changed data would evaluate against the stored rows and quietly contradict what the caller already holds. To close that gap, many layers flush before executing a query whose result the pending changes could affect — some before every query they run, some only when the query touches tables with pending writes, and some not at all unless asked. The guarantee only covers queries issued **through the layer's own query path**. A statement executed directly on the connection, or from another transaction, is invisible to that machinery, so you flush yourself first. It also means a query is a place where writes and their errors can happen.
go deeper
Remember the shape of the problem: the database answers queries from stored rows, so an edit still sitting in memory cannot affect the result until a statement has been sent.
Explain the guarantee and its limits — pending changes written before a query the layer runs, nothing done for statements issued outside that path, and behaviour that differs by configuration.
Show that you treat an automatic flush as a write point: locks taken during a read, errors attributed to the query, and mixed hand-written statements needing an explicit flush first.
Argue the policy. Implicit writes during reads trade a class of stale-read bugs for a class of surprising-error and lock-timing bugs; say which you would rather have your teams debug.
## The database cannot see your objects A tracked change is a fact about an object in memory. A query is evaluated by the database against the rows it has stored. Nothing bridges those two automatically: if you raise a price in memory and then ask for "everything priced above the threshold", the engine has no way to include or exclude the row you changed, because from its side the change has never happened. That is not a rare corner. It bites any read that follows a write in the same piece of work: - a filter over a column that was just changed - a count or a sum over rows that were just added or removed - a join or an existence check against a row created a moment ago - an ordering over a value the code has already updated ## What an automatic flush before a query means To close the gap, tracking layers write out pending changes before running a query. The **guarantee**, stated precisely, is: for a query executed through the layer's own query path, changes accumulated in the tracked set have already been sent as statements inside the open transaction, so the engine evaluates the query against them. How eagerly that happens varies, and the difference matters when you read someone else's code: | behaviour | what it does before a query | |---|---| | flush before every query | always writes out pending changes, whatever the query reads | | flush only when relevant | tries to work out which tables the query touches and flushes only if they overlap pending writes | | flush only at commit | never writes out for a query; the caller must flush explicitly | None of these is universal, so "it flushes for me" is a statement about a configuration, not about data-access layers in general. ## Where the automatic flush does not save you 1. **Statements the layer never sees.** A query or command executed straight on the connection bypasses the query path entirely, so nothing is flushed for it. Mixed codebases hit this constantly: the layer-issued query is correct and the hand-written statement beside it reads stale rows. 2. **A query whose reach the layer cannot work out.** When the flush is table-scoped, a query the layer cannot analyse — a string it did not build, an indirection through a view or routine — may not be recognised as affected. 3. **Another transaction.** A second connection sees nothing until this transaction commits, no matter how thoroughly this one flushed. 4. **Values the database computes.** A flush pushes state outward. Defaults, generated keys and values a trigger or an expression produced still have to be read back; flushing alone does not refresh the object. 5. **A layer configured to flush only at commit.** Then the query genuinely runs against pre-change rows unless you flush yourself. ## The side effect nobody expects: a read that writes An automatic flush makes a query a **write point**. Consequences worth stating out loud: - a line that looks like a read can raise a constraint violation, because it sent the insert that violated it - a line that looks like a read can take row locks and hold them for the rest of the transaction - the reported error points at the query, while the cause is the edit some layers of code above - validation or auditing hung off the write path suddenly runs during what the author thought was a read This is the main argument the sceptics of automatic flushing make, and it is a fair one. ## Working with it deliberately - Know which behaviour is configured before you reason about any read-after-write in that codebase. - When you mix layer queries with hand-written statements in the same transaction, flush explicitly before the hand-written one; do not rely on the automatic path you have just stepped outside of. - Prefer not to depend on the automatic flush for correctness in code that matters. An explicit flush at the point of the write states the dependency, survives a configuration change, and reads honestly. - If a read-after-write returns something impossible, order the suspects: was the change flushed, is the query going through the layer at all, and is it even the same transaction. - Remember what the guarantee does *not* cover: it is about the state the query is evaluated against, not about anything being durable. None of it is committed yet. ## The one-line version Pending changes are invisible to the engine, so the layer sends them before asking a question whose answer depends on them — but only for the questions it is the one asking.
- Why do some layers try to flush only when the query's tables overlap pending changes?Because flushing before every query turns every read into a write point: locks are taken earlier, statements go out in smaller groups, and errors surface in odd places. Scoping the flush to relevant tables keeps the guarantee where it is needed and leaves unrelated reads cheap — at the price of having to analyse each query, which is not always possible.
- After a flush, does the object hold values the database generated during it?Only the ones the layer explicitly reads back, such as an assigned key it needs for the identity of a new row. A flush pushes state outward; column defaults and values computed by the engine are not fetched unless the layer refreshes the object, so code that must see them needs a re-read.
- How would you make a read-after-write correct without depending on the automatic flush?Flush explicitly at the write, immediately before the read that depends on it, and say why in a comment. That makes the dependency visible at the call site, survives a change to the layer's flush configuration, and works identically whether the read goes through the layer or is a hand-written statement.
saying these in an interview costs you the question
- Believes the engine can see in-memory edits without a statement.
- Assumes every layer flushes automatically before a query.
- Thinks the automatic flush covers hand-written statements too.
- Expects a flush to pull database-computed values back into objects.
- Treats a query as read-only, so a constraint error there is baffling.
- Thinks flushing before a query makes the write visible to other transactions.