A request drives a tracking mapper and a separate row-mapping layer side by side; what must be shared and what goes stale?

## Why teams end up with both Mixing is normal, not a smell. A team keeps the full mapper for the write model, where a tracked set and cascade earn their keep, and reaches for a query builder or a row-mapping layer for the reporting queries, the bulk import and the export. The problem is that the two layers are two independent pieces of machinery, and unless you arrange otherwise, each will happily obtain its own connection and open its own transaction. ## What must be shared: one connection, one transaction If the mapper writes on connection A and the row-mapping layer writes on connection B, you no longer have one transaction — you have two, and everything that follows from that is bad: - **Half-commit.** One can commit while the other rolls back. The request either succeeds partially or leaves the data in a state no code path expects. - **Invisibility.** Under normal isolation, uncommitted work on connection A is not visible to a read on connection B. The second layer reads the *pre-change* row and behaves as if the first layer had never run. - **Self-deadlock.** Both connections want write locks on the same rows. Connection B waits for A, and A cannot proceed until the request finishes, which it cannot do while B is blocked. The request hangs until the lock timeout fires — and it will pass every test that exercises only one of the two layers. So the first requirement is mechanical: both layers must run on the same connection for the duration of the request, and exactly one component must own begin, commit and rollback. Whether that owner is the mapper's transaction scope or a boundary you wrote is a design choice; that there is only one owner is not. ## What goes stale: everything already loaded Even on a shared connection, the mapper's tracked set knows nothing about a statement it did not emit. Two directions of staleness matter, and they matter differently: | Situation | What the mapper believes | What you must do | |---|---|---| | A raw statement updated rows the mapper had loaded | Its objects still hold the pre-update column values | Discard or re-read those objects; do not write them back | | A raw statement deleted rows the mapper had loaded | The objects still look alive | Drop them; a later write would resurrect or fail | | The mapper has pending changes not yet sent | They are safely recorded | Push them before the other layer reads the same rows, or accept reading the old values | | Any cache built over the untracked layer | Nothing — the mapper never told it | Invalidate it yourself at the write site | The nastiest version is the first combined with a later mapper write: the tracked object still carries the old values, dirty checking compares against the old snapshot, and the mapper writes stale columns straight over the raw update. The raw write appears to succeed and then silently vanishes. ## An arrangement that works 1. Own the transaction in one place, above both layers, and inject the same connection into each. 2. Sequence the request so that raw work happens either entirely before the mapper loads anything, or entirely after the mapper's pending changes have been pushed. 3. Treat objects that a raw statement touched as invalid from that point on: drop them, or re-read them and use only the fresh copy. 4. Never let the two layers write the same table in the same request unless one clearly owns it — pick an owner per table and route the write through it. 5. Invalidate any hand-built cache at the write site, because there is no flush event to hang it on. ## What interviewers listen for The strong answer names both hazards, and in the right order: **the transaction and connection must be shared, or nothing else you say matters**, and then **anything already loaded is stale after the other layer writes**. Weaker answers stop at *it might be slow* or assert that a transaction manager will automatically reconcile two connections. It will not: coordinating two connections is a distributed-transaction problem, and adopting one to solve an in-process ordering issue is a much larger commitment than reusing one connection. A last point worth making: mixing is easiest to keep safe when the split is **by direction rather than by table** — the mapper owns writes to a table, the untracked layer owns heavy reads over it — because the staleness window then only opens in one direction, and read results are consumed and discarded rather than written back.