As a row's version value, how does an integer counter compare with a timestamp, and when does the timestamp break?
answer
- equality is the only requirement
- a counter cannot collide with itself
- same tick, same value
- clock skew and lost precision
- counter guards, timestamp informs
basics
~20 sA counter only has to differ from the previous value, so it is exact and cheap. A timestamp also serves as a last-changed column but breaks on two writes in one clock tick, on clock skew, or on precision lost in transit.
solid answer
~40 sThe guard needs one property from the value: after a write it must not equal what the previous reader loaded. A **counter** delivers that by construction - it is advanced by the statement itself, so a write cannot leave a row holding the value its previous reader loaded, and equality comparison is exact. A **timestamp** delivers it only if the clock has enough resolution and is trustworthy: two writes inside one tick produce identical values, so the second reader's guard compares equal and overwrites, and a clock read on the application side varies between servers and can jump backwards. Timestamps are attractive because they are also human-readable and useful in reports. The clean answer is to use both for different jobs: a counter as the guard, and a separate last-changed column for people.
go deeper
The version just has to change on every write. A counter does that by adding one; a timestamp does it only if the clock ticks between the two writes.
Explain the same-tick collision and why it loses an update silently, and note that a clock read on the application side varies between servers.
Argue the split - counter for correctness, separate timestamp for people - and point at the transit hazard where formatting a date-time breaks an exact-equality comparison.
Weigh a mechanism by how its failures present: a counter fails loudly with a rejected write, a timestamp fails quietly with a vanished edit, and that asymmetry should decide the standard.
## What the guard actually requires of the value The version is compared for **equality** in the update's `WHERE` clause and replaced by the write. From that, only one requirement follows: *the value after a write must differ from the value any earlier reader could have loaded*. It does not need to be ordered, meaningful, or globally unique. Everything below is a consequence of how well each candidate value meets that one requirement. ## The counter A small integer advanced by the writing statement, typically `version = version + 1`. - **Exact.** Integer equality has no rounding, no precision and no formatting to get wrong. - **Self-generating.** The engine computes the new value from the old one inside the same statement, so no external source can produce a duplicate. - **Cheap.** A few bytes per row, and a predicate the engine evaluates on a row it has already located by key. - **Meaningless on its own.** It says how many times a row was written, which nobody wants on a screen, and it tells you nothing about *when*. - **Bounded, in theory.** A narrow integer type on an extremely hot row could in principle wrap; a wide one cannot in any realistic lifetime. If it did wrap, a reader holding the pre-wrap value would compare equal - which is why the type is worth a moment's thought and no more. ## The timestamp A date-time column written on each change, compared the same way. - **Readable.** It answers "when was this last changed?" for support staff and reports, which is why teams reach for it. - **Resolution-bound.** If two writes land inside the same tick of whatever resolution the column stores, they produce the same value. A reader who loaded between them compares equal and overwrites the second write silently - the exact failure the guard exists to stop. - **Clock-dependent.** A value stamped by the application takes the clock of whichever server handled the request; those clocks disagree, and they are adjusted. A value stamped by the engine is at least a single clock, but it is still a clock. - **Fragile in transit.** Formatting a timestamp for a client and parsing it back can lose sub-second digits or shift an offset. Since the comparison is exact equality, any such change makes the token match nothing. - **Not monotonic under correction.** A clock that moves backwards can produce a value a previous write already used. ## Side by side | Property | Counter | Timestamp | |---|---|---| | Guaranteed to change per write | yes, by construction | only above the clock's resolution | | Depends on a clock | no | yes, and on which server reads it | | Survives formatting in a transfer model | trivially | only with exact precision preserved | | Useful to a human | no | yes | | Storage | a few bytes | a few more | ## When a timestamp is still reasonable It is not always wrong. Where writes to a single row are naturally spaced far apart, the value is stamped by one clock, the stored precision is fine-grained, and the token is never reformatted on the way out and back, a timestamp is workable and buys the readable column for free. The trouble is that every one of those conditions is an assumption about the future: a batch job that suddenly writes the same row twice in a millisecond breaks the first one, and a new client that formats dates its own way breaks the fourth. ## The practical split The recommendation that survives review is boring: 1. Use a **counter as the guard**, because correctness should not depend on clock behaviour. 2. Keep a separate **last-changed timestamp** for humans, reports and troubleshooting, with no correctness duty at all. 3. If a stored value must serve both roles - a table you do not own, say - be explicit about the resolution you are relying on and test two writes inside one tick, because that is the case that fails silently rather than loudly. ## The failure mode to remember A counter that is wrong tends to fail loudly: the token does not match, the write is rejected, someone notices. A timestamp that is wrong fails **quietly**: two values that should differ compare equal, the guard passes, and one user's edit disappears with no error anywhere. When choosing between mechanisms, prefer the one whose failure is visible.
- Why does the same-tick problem cause a lost update rather than a false conflict?Because the two writes leave the row holding a value identical to the one an earlier reader loaded. That reader's guard compares equal, the update matches, and its stale fields overwrite the later write - with no error raised, since from the statement's point of view nothing changed.
- Can a version value be generated by the application rather than the writing statement?It can, but it gives up the property that makes the counter safe: two application servers can produce the same or an out-of-order value. Letting the statement derive the new value from the stored one keeps generation single-threaded per row, at the engine.
saying these in an interview costs you the question
- Thinks a timestamp version cannot collide because time always moves
- Uses one timestamp column as both the guard and the audit field
- Assumes every server's clock agrees closely enough
- Formats the version for the client without preserving precision
- Believes the version must be ordered or globally unique