Snapshots without locks
InnoDB uses multi version concurrency control so a plain SELECT reads a consistent snapshot without taking locks and without blocking writers. Each row carries hidden system columns: a transaction id and a roll pointer into the undo log.
Read views
When a transaction needs a consistent read it builds a read view capturing which transaction ids are committed at that moment. For each row InnoDB checks the row's transaction id against the read view:
- If the version is too new to be visible, InnoDB follows the roll pointer through undo to find an older version the snapshot may see.
- This way readers never wait for writers and writers never wait for readers.
Isolation levels
- REPEATABLE READ, the InnoDB default, builds one read view at the first read so the whole transaction sees a stable snapshot.
- READ COMMITTED builds a fresh read view for each statement, so each statement sees the latest committed data.
The cost
Old versions live in undo until no read view needs them, after which a background purge thread removes them. Long running transactions hold back purge and let undo grow.
Key idea
InnoDB MVCC uses per transaction read views plus undo versions so reads see a consistent snapshot without locking, with purge reclaiming old versions once no snapshot needs them.