MVCC: Readers Do Not Block Writers

The problem with locks. If every read takes a lock, a long report blocks the writers, and if every write takes an exclusive lock, one writer blocks every reader. A busy system spends its time waiting. MVCC removes the biggest source of waiting: reading and writing do not conflict.

Versions instead of overwrites. An update does not change a row in place. It marks the current version as ended by this transaction and inserts a new version created by this transaction. Each version records who created it and, if it is gone, who ended it. The table now holds a short history of every row.

A snapshot decides what you can see. When a transaction begins (or each statement begins, depending on the isolation level) it notes which transactions have committed. A version is visible if it was created by a transaction that committed before the snapshot, and it has not been ended by one that committed before the snapshot. Everything newer is invisible, so your reads are consistent for the whole transaction.

# each version: (value, created_by, ended_by) ; transaction ids grow with time
versions = [('x=1', 1, 6)]      # created by tx 1, replaced by tx 6
versions.append(('x=2', 6, None))
committed = {1, 6}              # tx 6 commits at time T

def visible(version, snapshot):
    value, created, ended = version
    made = created in snapshot
    gone = ended is not None and ended in snapshot
    return made and not gone

def read(snapshot):
    return [v[0] for v in versions if visible(v, snapshot)]

reader_before = {1}              # a reader that began before tx 6 committed
reader_after = {1, 6}            # a reader that began after
print(read(reader_before))      # ['x=1']  still sees the old value, no waiting
print(read(reader_after))       # ['x=2']

Writers still conflict with writers. Two transactions that update the same row cannot both win. Under snapshot isolation the first to commit wins and the other is aborted and must retry ('first committer wins'), or the second waits on a row lock until the first finishes. Reads never take part in this.

What snapshot isolation does not prevent: write skew. Two doctors are on call, and the rule says at least one must stay. Each transaction reads 'two on call', so each sees it is safe to go off call, and each updates a different row. Neither wrote the same row, so no conflict is detected, and now nobody is on call. Preventing this needs a stronger level (serializable), an explicit lock on the rows you read, or a constraint that checks the rule.

Cleanup. Old versions pile up. A background process (called vacuum in PostgreSQL, purge in other engines) removes versions that no running transaction can still see. A transaction left open for hours pins old versions and bloats storage, which is why long-running transactions are a real operational hazard.

Where you meet it. PostgreSQL, MySQL's InnoDB, Oracle and many others use MVCC, each with a different layout for the old versions. Learn the idea once (versions, snapshots, visibility, cleanup) and the vendor details become easy to read.