Question: Why would I need more than one extra version of the same row? I would think that with transactional locking everybody else is waiting on the first update to commit before getting their own changes in, unless the db is somehow trying to lock columns-per-row instead of entire rows.
That would require all queries, including read only queries, to participate in strict two phase locking. That has very poor performance under even very mild contention, not to mention all that mutual locking and unlocking overhead between read only queries is largely un-needed.
So what MVCC databases do is keep enough versions to cover the oldest running query instead. Now read only queries don't need to hold any locks at all, they just prune the newest version older than the transaction id the query started at.
If my query started 1000 ms ago, and every 200ms a transaction completed, I'm perfectly fine with getting results of some/most/all of those 5 commits. I usually don't need the database to enforce a 1000-ms-old snapshot for my own sake, which is why I'm using read-committed isolation instead of repeatable read etc. Are we saying that not enforcing this delay would break the database somehow even if I'm fine with it?
Edit: I should clarify that I recognize the need for one extra version, since read-committed txns shouldn't see it until it is committed. Other writes must wait for the commit until they can write, though - it seems like there's some optimistic-writing thing where we let a bunch of writes queue up for one record knowing that we're going to have to a problem when one of them commits and the others find out they should have waited before trying to write or something, because we didn't force them to acquire a write lock before writing.
It gets complex. There are a lot of tradeoffs between short and long running transactions, and different choices in the design space each have pluses and minuses.
Of course if you're running reduced consistency things are easier. Wrong answers are often faster. But when people select a transactional database, it's usually because they at least need snapshot consistency, and often they require full serializability.
