How does a data-access layer decide whether a saved object needs an insert or an update, and what breaks when the application assigns keys?
answer
- the layer has to guess
- an empty identifier means never written
- a version is absent before the first write
- an assigned key is never empty
- prefer an explicit insert call
basics
~20 sUsually by a marker meaning never-written: an empty identifier, an absent version, or the layer's own tracking. An application-assigned key is populated from birth, so the layer reads a new object as existing and updates a row that never existed.
solid answer
~40 sA layer that offers one save entry point has to guess the object's state. The common tests are: the identifier still holds its empty value; a version field is absent, which is only true before the first write; the layer itself remembers the object because it was loaded or explicitly registered; or, most expensively, a lookup by key before it decides. An application-computed identifier defeats the first test outright — the key is set in the constructor, so the object looks stored, and the layer emits an update whose predicate matches nothing. That either passes silently as a zero-row update or is reported as a lost-update conflict. The remedies are an explicit insert call instead of a guessing save, a guess based on the version rather than the key, or accepting the pre-read.
go deeper
Know that a single save call has to work out whether your object is new, and that it decides from what the object itself carries rather than by asking the store.
Explain the markers — empty identifier, absent version, the layer's own tracking, a pre-read — and why each of the first three is a heuristic rather than a fact.
Show the production failure: with application-assigned keys every create becomes an update matching no rows, and unless affected rows are checked the create disappears without an error.
Decide the policy. An explicit insert-versus-update surface removes the guess but pushes state knowledge to callers; one save call keeps callers simple but obliges the model to carry a trustworthy never-written marker for good.
Many data-access layers offer a single "save this" entry point that works for both new and existing objects. Convenient — but the layer has to work out which statement to emit, and it does so from what the object carries, not from the store. The test it uses is a heuristic, and it is the heuristic that an application-assigned key breaks. ## The markers a layer can use | marker | what it means | why it can be wrong | |---|---|---| | the identifier holds its empty value | never written, because the store fills it in | wrong the moment the application supplies keys | | a version field is absent | never written, because the layer sets it on first write | needs a version field that can be absent | | the layer's own tracking | this object came from a load, or was registered as new | only valid inside the unit of work that tracked it | | a lookup by key | the store is asked directly | one extra read per save, and the answer can go stale | The first is the default in most layers because it costs nothing and is right whenever the store generates keys. The second is stronger, because the version is written *only* by the layer: the application never sets it, so its absence is real evidence about storage rather than about the caller. The third is the most accurate and the least available — tracking does not survive the object leaving the unit of work. The fourth is a fact rather than a guess, and it is priced accordingly. ## Why an application-assigned key defeats the identifier test If the model computes an identifier in the constructor, the key is populated from the object's first instant. The layer's test asks "is the identifier empty?", gets "no", and concludes the row exists. It emits an update whose predicate names an identifier that no row carries. What happens next depends on the layer: 1. **Silent success.** The statement is valid and affects zero rows. If nothing checks the affected-row count, the caller believes the object was stored, and the row is simply missing — discovered later, usually by a read that returns nothing. 2. **A reported conflict.** Layers that check the affected-row count as part of concurrency control see zero rows and report it as a lost update — a confusing message, because nothing was lost; the row never existed. 3. **A correct insert anyway**, if the layer used one of the other markers, or if it was told explicitly which statement to emit. Only the third is a good outcome, and it is the one you have to arrange deliberately. ## The remedies - **Call an explicit insert.** Where the layer distinguishes "insert this" from "save this", use the specific one. It removes the guess entirely, at the cost of the caller having to know the object's state — which, at the point of construction, it does. - **Base the guess on the version, not the key.** A version field left absent until the first successful write is a marker the application never touches. - **Configure the marker.** Some layers let you nominate the value that means "not yet written" for a key, so a model using, for example, a sentinel rather than an empty reference is still classified correctly. - **Accept the pre-read** for low-volume paths, where one extra lookup per save is not worth arguing about — and reject it for bulk paths, where it doubles the statements. ## The wider point The three key timings each pull the "is this new?" question in a different direction. A store-assigned key makes the identifier test free and correct. Pre-allocated blocks and application-computed keys buy an identifier that exists before the write — and pay for it here, because the very property that makes the key useful early makes it useless as a marker of whether the row exists. That is a fair trade, but it must be a conscious one: the model needs some other way to say "never written", and if nobody chooses it, the layer will guess and the guess will be wrong in one direction for every create. ## Where layers differ Data-access layers differ in how much they guess and how loudly they complain: some always inspect the object, some require you to name the operation, some check the affected-row count on every write and some only when a version column is mapped. The safest habit across all of them is to make creation explicit and to treat a zero-row update as an error rather than a no-op.
- Why is a version field a better new-object marker than the identifier?Because the layer alone maintains it. The version is written on the first successful write and never set by the application, so its absence really does mean the row has never been stored. An identifier can be supplied by the caller, imported from another system, or defaulted by the model, so its presence proves nothing.
- Why can an application-assigned key make a create fail silently?The layer chooses an update, whose predicate names an identifier no row carries. The statement is valid and affects zero rows. Unless the layer checks the affected-row count — many do so only when a version column is mapped — nothing is reported and the caller believes the object was stored.
- What does a pre-read cost, and when is it acceptable?One lookup per save, plus the chance that the answer is stale by the time the write runs. It is reasonable for low-volume administrative writes and for reconciling imported data, and unreasonable in a bulk path, where it roughly doubles the work for a fact the caller usually already knows.
saying these in an interview costs you the question
- Thinks a save call always consults the store before choosing
- Assumes an update matching zero rows was a successful save
- Sets the identifier by hand and expects an insert anyway
- Believes a version field serves concurrency and nothing else
- Says a zero-row update means the data was already identical