In a data-access layer that maps a version attribute, what does it do on each update, and how does a conflict surface?
answer
- a guard, not a lock
- the loaded value goes in the where clause
- zero rows changed means someone won
- the statement increments, not your code
basics
~20 sThe layer puts the version value it loaded into the update's where clause and advances the version in the same statement. If the statement reports zero rows changed, another writer moved the row on, and the layer raises a conflict error.
solid answer
~40 sA mapped **version attribute** turns every write into a guarded one. The layer emits `UPDATE ... SET col = ?, version = version + 1 WHERE id = ? AND version = ?`, binding the version the object was *loaded* with. If someone else wrote that row in between, the stored version no longer matches, the statement changes zero rows, and the layer converts that row count into a conflict error instead of handing back a number nobody checks. On success it also advances the version held in memory, so a second write inside the same unit compares the new value. Nothing is locked at any point: readers and writers never wait, and the loser simply finds out at write time that it has to re-read and decide.
code
sql · 3 linesUPDATE order_line
SET quantity = ?, price = ?, version = version + 1
WHERE id = ? AND version = ?;go deeper
Remember the shape: an extra version field on the row, compared in the where clause of the update and bumped by the same statement. It stops one save from quietly wiping out another.
Be able to write the statement out and say which version value is bound - the loaded one, not the new one - and explain that zero affected rows is what the layer turns into a conflict error.
Show that you know the guard covers only writes that go through the layer, that a conflict leaves the in-memory object worthless, and that recovery means a fresh read rather than re-saving what you held.
Frame it as a cost choice: one column and one predicate buy conflict detection with zero waiting, but push the resolution decision - report to a human or redo the intent - out to the boundary as a product question.
## What a version attribute is A **version attribute** is one extra column on a table, mapped onto one extra field of the object, whose only job is to change every time the row is written. It usually holds a counter that starts at zero or one and goes up by one per write; some models use a timestamp instead. It carries no business meaning, nobody reads it on a screen, and application code normally never assigns it. Its purpose is to answer one question at write time: *is this row still the way it was when I read it?* Answering that without holding a lock is what **optimistic** means. The write assumes nobody interfered and finds out at the last possible moment whether the assumption held. ## The statement the layer emits When the layer writes a tracked object that carries a version, it does not emit a plain update keyed on the primary key alone. It emits a **guarded** one: ```sql UPDATE order_line SET quantity = ?, price = ?, version = version + 1 WHERE id = ? AND version = ? ``` Two values are bound from the object: the primary key, and **the version the object was loaded with** - not the value the statement is about to write. The new version is produced by the statement itself, so the comparison and the increment are one step at the engine, with no window between checking and writing. Deletes of a versioned row are usually guarded the same way, so removing a row someone else has since edited is a conflict rather than a silent deletion. ## How a conflict is detected The engine reports how many rows a statement changed, and the layer inspects that number: 1. **One row changed.** The stored version still equalled the loaded one, so nothing happened in between. The write stands. 2. **Zero rows changed.** The `WHERE` clause matched nothing: either another writer advanced the version since this object was read, or the row was deleted outright. The row count alone cannot say which, and both are conflicts. 3. **The layer raises an error** rather than returning the count to the caller. That difference matters - a row count nobody inspects loses the write silently, while an error cannot be ignored by accident. Note what did *not* happen. Nobody was blocked, no lock was held across anyone's thinking time, and there was no re-read immediately before the write. The whole cost of the guard is one column and one extra predicate. ## What happens to the copy in memory On success the layer also advances the version **in memory**, so the object matches the row again and a second write inside the same unit of work compares the new number. Without that, the unit's own follow-up write would fail against changes it had just made itself. On failure the in-memory object is out of step with reality, and the accepted move is to abandon it: end the unit, load the row again, and work from the values that actually won. Re-saving the same stale object is not a recovery - it is an attempt to overwrite the winner. ## Guarded versus unguarded writes | Write path | Version compared? | Version advanced? | |---|---|---| | Layer-issued update of a tracked object | yes, against the loaded value | yes, by the statement | | Layer-issued delete of a tracked object | yes, typically | not applicable | | A statement written by hand against the table | only if you write the predicate | only if you write the increment | | A write from another service, job or trigger | no | no | The bottom two rows are the practical hole: the guard is only as strong as the share of writers that go through it. ## What the application does with a conflict There are two honest responses, chosen per use case: - **Report.** Tell the user the record moved on and show the current values. Right whenever the two edits could disagree in a way only a person can settle. - **Redo.** Re-read the row, apply the same *intent* to the new values, and write again. Safe only when the intent still makes sense against whatever the row now holds. ## Where the guard reaches The check compares state as of the moment the object was read into this unit of work. When the read and the write are milliseconds apart inside one transaction, the guard rarely fires. Its real value shows up across long gaps - a record read, shown to a person, and written back much later - and only if the version that travelled out is the one that comes back.
- Why does the layer raise an error instead of returning the affected row count to the caller?Because a row count is easy to ignore. Every call site would have to remember to compare it against one, and the day somebody forgets, the write disappears with no trace. An error propagates by default and forces the boundary to choose between reporting and redoing.
- What happens to the version held in memory after a successful write?The layer advances it to the value the statement wrote, so the object matches the row again. A second write of the same object inside the same unit then compares the new number and succeeds; if the field stayed stale, the unit would conflict with its own earlier change.
- Does the guard protect a row from being deleted underneath an editor?Usually yes: a delete of a versioned row is guarded by the same predicate, so deleting a row that has since changed reports zero rows and is raised as a conflict. In the other direction, an update that finds zero rows may mean the row was deleted rather than edited - the count cannot distinguish the two.
It is a shelf label with a revision number: you write the new price only if the label still reads the revision you saw, otherwise you go back and look at the shelf again.
saying these in an interview costs you the question
- Thinks a version column locks the row while the user edits it
- Expects the layer to merge two conflicting edits automatically
- Assigns the next version by hand in application code
- Calls a re-read just before saving a version check
- Assumes zero rows changed can only mean a version mismatch
- Catches the conflict and re-saves the same stale object