zlacker

>>andren+(OP)
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.

>>kerbla+Z21
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.