The idea: two kinds of read in one transaction
InnoDB reads a row in one of two ways. A plain SELECT is a consistent read (a snapshot read): it looks at the row through a read view and, if the newest version is too new for that view, follows the roll pointer into the undo log to rebuild an older one. It takes no locks and never waits.
Everything that may change data is a current read (a locking read): SELECT … FOR UPDATE, SELECT … FOR SHARE, UPDATE and DELETE. It ignores the read view, reads the newest committed version and locks it. The isolation level mostly decides how the read views are made and how long the locks are kept.
The page uses a table doctors(id, name, on_call, shifts) with Alice and Bob on call and Carol off, everyone at 10 shifts. Two sessions T1 and T2 run statements against it. The top shows each session's log and read view, the lower left the clustered index record and the older versions in the undo log, the lower right the record locks.
Read views and the undo log
Every clustered index record carries two hidden columns: DB_TRX_ID, the transaction that wrote this version, and DB_ROLL_PTR, a pointer to the undo record that holds the version before it. Following the roll pointers gives the row's history, newest first.
A read view is a small list made when a consistent read starts. It holds m_ids, the ids of transactions that were still running, and m_low_limit_id, the next id to be handed out. A version written by trx X is visible when X is the reader itself, or X is below the low limit and not in m_ids. Anything else was not committed when the view was made, so InnoDB walks back to an older version. The tag above a version on the canvas shows which one a statement used.
REPEATABLE READ vs READ COMMITTED
The two levels differ in when the read view is made:
| REPEATABLE READ (default) | READ COMMITTED | |
|---|---|---|
| Read view | made by the first plain SELECT, kept to COMMIT | a new one for every plain SELECT |
| Same SELECT twice | same answer | may see new commits |
| Locks on scanned rows that do not match WHERE | kept (plus gap locks) | released at once |
| UPDATE meets a locked row | waits | semi-consistent read: skips it if the committed version does not match |
BEGIN itself makes no read view (START TRANSACTION WITH CONSISTENT SNAPSHOT does). Demo: READ COMMITTED, a read view per statement reads 10, then 10 again while T2's change is not committed (T2 is in m_ids), then 11 after T2 commits.
Locking reads are current reads
Under REPEATABLE READ the read view freezes plain SELECTs, but not locking reads. In Demo: snapshot read vs current read (RR), T1 reads 10, T2 adds a shift and commits, and then inside the same T1:
| T1 runs | Returns | Why |
|---|---|---|
SELECT shifts WHERE id=1 | 10 | read view; trx 102 started after it, so the undo version |
… FOR UPDATE | 11 | current read: newest committed version, X lock |
SELECT shifts WHERE id=1 | 10 | the same read view again |
UPDATE SET shifts=shifts+1 | writes 12 | current read of 11, not the snapshot's 10 |
SELECT shifts WHERE id=1 | 12 | a transaction always sees its own changes |
So an UPDATE can change rows, or build on values, that the transaction's own snapshot does not show. After the UPDATE the row is the transaction's own version, and the snapshot suddenly "sees" it. The same happens with rows: an UPDATE … WHERE under REPEATABLE READ also matches rows another transaction inserted and committed after the snapshot, and a plain SELECT then shows them.
Lost update, and FOR UPDATE
A common bug is read-then-write: the application runs SELECT shifts, adds 1 in its own code and writes UPDATE … SET shifts = 11. In Demo: lost update (RR, read then write) both sessions read 10 and both write 11. T2's UPDATE waits for T1's X lock; when T1 commits, InnoDB gives T2 the lock and T2 overwrites 11 with 11. Two increments, one result: a lost update. InnoDB raises no error, unlike PostgreSQL's REPEATABLE READ, which would fail T2 with "could not serialize access due to concurrent update".
Two fixes: write SET shifts = shifts + 1, which is a current read inside the UPDATE, or read with SELECT … FOR UPDATE. In Demo: FOR UPDATE prevents the lost update T2's FOR UPDATE waits for T1's lock, and when it continues it reads 11, T1's committed value, even under REPEATABLE READ. It writes 12.
Semi-consistent reads under READ COMMITTED
UPDATE … WHERE on_call='off' has no index to use, so it scans the clustered index and locks each row it looks at. Under REPEATABLE READ it would stop and wait at any row another transaction has locked. Under READ COMMITTED InnoDB does a semi-consistent read: for a row locked by someone else it reads the last committed version and lets MySQL check the WHERE. If the row does not match, it is skipped without waiting; only a matching row is waited for and read again. Non-matching rows that it did lock are unlocked at once.
In Demo: semi-consistent UPDATE (RC) T1 holds an X lock on Alice. T2's scan skips Alice (committed version: on call), locks and releases Bob, and updates Carol, without ever waiting.
Write skew and SERIALIZABLE
Locks on the rows you write do not protect the rows you only read. In Demo: write skew (RR) each session counts 2 doctors on call from its snapshot, Alice and Bob each go off call (different rows, so no lock conflict) and both commit: nobody is on call ✗.
SERIALIZABLE in InnoDB is REPEATABLE READ plus one rule: with autocommit off, every plain SELECT runs as SELECT … FOR SHARE. Reads take S locks, so a later write by another transaction must wait. In Demo: SERIALIZABLE turns SELECT into FOR SHARE both counts take S locks on all rows. T1's UPDATE of Alice waits for T2's S lock, T2's UPDATE of Bob waits for T1's S lock, and the deadlock detector rolls back the lighter transaction, T2, with ERROR 1213 (40001): Deadlock found when trying to get lock. T1 finishes; T2's retry counts 1 and keeps Bob on call ✓.
Under REPEATABLE READ you get the same protection by hand: count with SELECT … FOR SHARE or FOR UPDATE (InnoDB then also locks the gaps of the range, see InnoDB gap and next-key locks).
Compared with PostgreSQL
| InnoDB | PostgreSQL | |
|---|---|---|
| Old versions | undo log, rebuilt through DB_ROLL_PTR | stay in the heap (xmin / xmax) |
| UPDATE under RR on a row changed since the snapshot | waits, then updates the newest version | error 40001, first updater wins |
| SERIALIZABLE | plain SELECT = FOR SHARE, readers block writers | SSI: SIREAD locks block nobody, abort on a dangerous structure |
See Serializable Snapshot Isolation in PostgreSQL for the other approach, MVCC and Isolation Levels for the anomalies of every level in general, InnoDB undo logs and purge for how long undo versions live, Two-Phase Locking and Deadlock Detection for lock queues and the wait-for graph, and How MySQL Runs a Query for the whole path of a statement.
What the page leaves out
Real InnoDB also takes gap and next-key locks under REPEATABLE READ and SERIALIZABLE (so a locking scan blocks inserts into the range), gives a transaction an id only at its first write (read-only transactions share a fake one), writes one undo record per change, and purges undo versions once no read view needs them. The deadlock victim is chosen by a weight of changed rows and locks, and a lock wait ends after innodb_lock_wait_timeout (50 s) with error 1205. With autocommit on, a plain SELECT under SERIALIZABLE stays a consistent read. Secondary indexes, which have no roll pointers and are checked through the clustered index, are not shown.