MySQL Transaction Isolation Levels and MVCC Explained

SQL defines four isolation levels. Read Uncommitted allows reading uncommitted changes — "dirty reads". Read Committed prevents dirty reads but not "non-repeatable reads" — if transaction A reads a row twice and transaction B commits an update in between, A sees different values. Repeatable Read prevents non-repeatable reads — the same row returns the same value throughout the transaction — but the SQL standard still allows "phantom reads" where a range query returns different row counts. Serializable serializes everything with locks; it's correct but has terrible concurrency. InnoDB's default is Repeatable Read, and MySQL is unusual — it prevents phantom reads at RR too, using MVCC for snapshot reads and gap locks for locking reads. MVCC is how InnoDB avoids blocking readers with writers. Each row has two hidden columns: trx_id, the ID of the transaction that last modified it, and roll_pointer, pointing into the undo log at the previous version. Undo log entries chain together — every update pushes the old version onto the chain. On a snapshot read, InnoDB creates a Read View — under RR, the Read View is built once at the first read of the transaction and reused; under RC, a new Read View is built for every statement. The Read View stores the list of active (uncommitted) transaction IDs plus a low-water mark (min active) and high-water mark (max trx ID at that moment). Visibility rule: a row version is visible if its trx_id committed before the Read View was created — specifically, trx_id < low-water mark, OR trx_id == this transaction, OR (trx_id < high-water mark AND trx_id not in active list). If invisible, walk the roll_pointer chain to an older version and re-check. This gives each transaction a stable view of the database consistent with its Read View timestamp, without blocking writers. Current reads like SELECT ... FOR UPDATE don't use MVCC — they read the latest committed version and take row/gap locks to prevent concurrent modification.