Optimistic Concurrency with Version Columns
A row can be read correctly, modified correctly, and still be written incorrectly. The problem appears when another transaction changes the same logical record between the read and the write. A plain UPDATE often has no memory of the state on which the new values were based, so the later writer can replace an earlier change without detecting the race. A version column turns that hidden assumption into a predicate. The update says, in effect, that the write is valid only while the row remains at the version that the caller observed. The database then evaluates the state check and the mutation as one atomic statement.