Skip to content
Tutorials Center
My learning

Topics

Lesson 57 of 75 ยท Transactions and Concurrency

Isolation Levels and MVCC

Understand MySQL isolation levels and InnoDB MVCC: dirty, non-repeatable and phantom reads, REPEATABLE READ vs READ COMMITTED, snapshots and undo logs.

When two transactions run at the same time, a question appears: how much of one transaction's work should the other see? If you see everything immediately, you may act on data that is later rolled back. If you see nothing until you finish, you pay in waiting or in stale data. The isolation level is the setting that picks a point on this scale, and InnoDB implements it with a technique called MVCC, multi-version concurrency control.

This lesson explains the three classic anomalies (dirty, non-repeatable and phantom reads), the four SQL isolation levels, why MySQL's default is REPEATABLE READ, how snapshots and the undo log work, and how to choose and set a level. Because isolation needs two simultaneous sessions, every example here is a two-session script: you read it as a timeline, one column per client. Nothing on this page runs in the in-browser engine; you can reproduce each script with two mysql client windows (Using the mysql Command-Line Client). First read Transactions and ACID in InnoDB if START TRANSACTION is new to you.

Three anomalies

Dirty read You read data another transaction has not committed.
Non-repeatable read The same row changes between two reads.
Phantom read A repeated query returns new or missing rows.
1 / 3

The three anomalies

Imagine a table accounts(id, balance) where account 1 holds 100. Each anomaly is a way for a transaction to get a surprising answer.

Dirty read. Session B reads a value that session A has changed but not yet committed. If A then rolls back, B acted on data that never existed.

Step Session A Session B
1 START TRANSACTION;
2 UPDATE accounts SET balance = 0 WHERE id = 1;
3 SELECT balance FROM accounts WHERE id = 1; returns 0
4 ROLLBACK; (B has used a value that was never committed)

Non-repeatable read. Session B reads the same row twice in one transaction and gets two different answers because A committed a change in between.

Step Session A Session B
1 START TRANSACTION; then SELECT balance ... id = 1; returns 100
2 UPDATE accounts SET balance = 50 WHERE id = 1; COMMIT;
3 SELECT balance ... id = 1; returns 50

Phantom read. Session B runs the same range query twice, and the second run includes a row that A inserted and committed in between.

Step Session A Session B
1 START TRANSACTION; then SELECT COUNT(*) FROM accounts WHERE balance >= 100; returns 1
2 INSERT INTO accounts VALUES (9, 500); COMMIT;
3 the same COUNT(*) returns 2

The difference between the last two is the unit: a non-repeatable read changes a row you already saw, a phantom adds or removes rows from a set you already queried.

The four isolation levels

The SQL standard defines four levels by which anomalies each one allows:

Level Dirty read Non-repeatable read Phantom read
READ UNCOMMITTED possible possible possible
READ COMMITTED prevented possible possible
REPEATABLE READ prevented prevented possible by the standard
SERIALIZABLE prevented prevented prevented

InnoDB follows this table with one useful improvement: at REPEATABLE READ, plain (non-locking) reads use a snapshot, which also hides phantoms in practice, and locking reads use gap locks to block them (Locking, Gap Locks and Deadlocks). The honest summary is that InnoDB's REPEATABLE READ is stronger than the standard demands, but not completely free of surprises, as the write-skew section below shows.

READ UNCOMMITTED is almost never a good idea. It exists for rough statistics where wrong numbers are acceptable. SERIALIZABLE turns plain SELECT statements into SELECT ... FOR SHARE when autocommit is off, so readers block writers and deadlocks become common. Use it only for narrow, measured cases.

The two levels that matter in practice are REPEATABLE READ, MySQL's default, and READ COMMITTED, the default of PostgreSQL, Oracle and SQL Server.

MVCC: how InnoDB keeps readers and writers apart

InnoDB never overwrites a row in place without keeping the old version. Each row carries hidden fields: the ID of the transaction that last changed it, and a pointer into the undo log, where earlier versions of that row are stored as a chain. When a transaction needs to read a row, it follows the chain until it finds a version that is visible to it.

Visibility is decided by a read view, a snapshot that records which transactions were still active when it was created. A version written by a transaction that was not yet committed at that moment, or that began later, is invisible. The reader keeps walking back through the undo chain until it finds an older version it is allowed to see.

sequenceDiagram
    participant A as Session A
    participant R as Row (id 1)
    participant U as Undo log
    participant B as Session B
    B->>R: START, first SELECT creates read view
    R-->>B: balance = 100
    A->>R: UPDATE balance = 50, COMMIT
    R->>U: old version (100) kept in undo chain
    B->>R: SELECT again
    R->>U: current version is newer than read view
    U-->>B: balance = 100 (older, visible version)

Session B keeps seeing balance 100 because its read view predates A's commit.

You can model the idea with an ordinary table. Below, every change appends a new version of the row, stamped with the number of the transaction that created it (created_tx) and, once superseded, the one that replaced it (ended_tx). A reader holding a snapshot taken after transaction 10 sees only versions created at or before 10 and not yet ended at or before 10. This is a stand-in for what InnoDB does with the undo log, not InnoDB itself:

SQL Model of MVCC: versions and a read view

Press Run (or Ctrl + Enter) to see the result here.

The first reader gets 100 and the second 50, from the same table at the same time: each reader sees "its" version. That is why a plain read never has to wait for a writer.

The same model shows why a long transaction hurts. The old version (100) can only be removed once no reader with a snapshot older than tx 12 remains:

SQL Model of purge: which versions can be removed?

Press Run (or Ctrl + Enter) to see the result here.

Nothing is purgeable here: the 100 version ended at tx 12, which is after the oldest snapshot (11), so that reader might still need it. Once that old snapshot closes, whole chains become purgeable.

Two consequences follow:

  • Plain reads take no row locks. A consistent read never waits for a writer and never blocks one. This is why InnoDB handles mixed read-write workloads well.
  • Long transactions are costly. InnoDB cannot purge an old row version while any open read view might still need it. One transaction that stays open for hours makes the undo log grow, slows reads that must walk long chains, and bloats the tablespace.

REPEATABLE READ: one snapshot per transaction

At REPEATABLE READ, the read view is created by the first consistent read in the transaction (not by START TRANSACTION itself, unless you use START TRANSACTION WITH CONSISTENT SNAPSHOT) and then reused for every later plain SELECT:

Step Session A Session B (REPEATABLE READ)
1 START TRANSACTION;
2 SELECT balance FROM accounts WHERE id = 1; returns 100
3 UPDATE accounts SET balance = 50 WHERE id = 1; COMMIT;
4 SELECT balance FROM accounts WHERE id = 1; still returns 100
5 COMMIT;
6 SELECT balance FROM accounts WHERE id = 1; returns 50 (new transaction, new snapshot)

This is excellent for reports: a long report sees one consistent picture of the data, even if the business keeps writing. It also protects the "read, compute, write" patterns that go wrong when the data shifts under you.

A surprise: reads see the snapshot, writes see the latest

The snapshot applies to plain reads only. UPDATE, DELETE and locking reads (SELECT ... FOR UPDATE, FOR SHARE) always operate on the latest committed data, because they must change or lock the current version of the row. That can produce a result that looks impossible:

Step Session A Session B (REPEATABLE READ)
1 START TRANSACTION; SELECT balance ... id = 1; returns 100
2 UPDATE accounts SET balance = 50 WHERE id = 1; COMMIT;
3 UPDATE accounts SET balance = balance - 10 WHERE id = 1; sets it to 40 (50 - 10)
4 SELECT balance ... id = 1; returns 40

In step 3, B's update works on the committed 50, and from then on B sees its own change (40), so B's later SELECT shows a value that is neither the snapshot value (100) nor a value B ever read. Mixing snapshot reads and writes inside one transaction is therefore a source of subtle bugs. When a decision depends on the current value, read it with SELECT ... FOR UPDATE so the read and the write agree (Locking, Gap Locks and Deadlocks).

READ COMMITTED: a fresh snapshot per statement

At READ COMMITTED, every consistent read creates its own new read view. A second SELECT in the same transaction sees whatever has been committed since the first, so non-repeatable reads and phantoms can happen:

Step Session A Session B (READ COMMITTED)
1 START TRANSACTION; SELECT balance ... id = 1; returns 100
2 UPDATE accounts SET balance = 50 WHERE id = 1; COMMIT;
3 SELECT balance ... id = 1; returns 50

Why would anyone choose it?

  • Fewer locks. At READ COMMITTED, InnoDB mostly locks only the rows it matches and does not use gap locks for searches and index scans (foreign-key and duplicate-key checks still take gap locks). This reduces lock waits and deadlocks on busy tables with range updates.
  • Familiarity. Applications written for PostgreSQL or Oracle often assume it.
  • Smaller purge backlog. A statement-level read view lets the purge advance sooner, which helps with long-running transactions.

The cost is that a transaction can no longer assume its reads are stable. If you use READ COMMITTED, also use binlog_format = ROW, because statement-based logging is not safe at this level (MySQL 8 already defaults to ROW; see The Binary Log and Replication).

Setting the level

SQL
SELECT @@transaction_isolation;         -- REPEATABLE-READ by default

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- this connection, later transactions
SET GLOBAL  TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- new connections only

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;             -- next transaction only
START TRANSACTION;

To fix the default in the configuration file, set transaction_isolation = READ-COMMITTED under [mysqld]. Note the spelling: the variable uses hyphens (READ-COMMITTED) while the SET TRANSACTION statement uses spaces. Frameworks often issue the SET statement for you on connect, so check what your driver actually does before you debug.

Write skew and other limits

Snapshot isolation still has gaps. A classic one is write skew: two transactions each read overlapping data, each decides the rule is satisfied, and each updates a different row, so together they break the rule.

Step Doctor A Doctor B
1 START TRANSACTION; SELECT COUNT(*) FROM on_call; returns 2 START TRANSACTION; SELECT COUNT(*) FROM on_call; returns 2
2 DELETE FROM on_call WHERE doctor = 'A'; DELETE FROM on_call WHERE doctor = 'B';
3 COMMIT; COMMIT; (nobody is on call!)

Neither plain read took a lock, and the deletes touch different rows, so nothing conflicts. The remedies are to lock what you read (SELECT ... FROM on_call FOR UPDATE so the second transaction waits), to make the rule a constraint the database can check, or to run that transaction at SERIALIZABLE.

Choosing a level

  • Keep REPEATABLE READ unless you have a reason to change. It is the most tested level in MySQL and gives stable reads.
  • Consider READ COMMITTED for write-heavy systems with many range updates and lock conflicts, with ROW binary logging.
  • Use SELECT ... FOR UPDATE for "read, then update based on what I read" logic at either level.
  • Keep transactions short at every level so old versions can be purged.
  • Avoid READ UNCOMMITTED, and use SERIALIZABLE only when you have proof you need it.

What ran live here

Only two small models of row versions and purging, written as plain SQL tables. Isolation levels, read views and the undo log are InnoDB features that need two connections, and the in-browser engine has a single connection. The tables above are scripts to replay in two mysql sessions. The single-session basics (BEGIN, COMMIT, ROLLBACK) can be tried live in Transactions and ACID in InnoDB.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which anomaly is prevented by READ COMMITTED but not by READ UNCOMMITTED?

    READ COMMITTED guarantees you only see committed data, which rules out dirty reads. Non-repeatable reads and phantoms are still possible at that level.
  2. 2. In InnoDB at REPEATABLE READ, when is the snapshot for plain SELECTs created?

    The read view is created by the first consistent read (or by START TRANSACTION WITH CONSISTENT SNAPSHOT) and then reused until the transaction ends.
  3. 3. A REPEATABLE READ transaction reads balance 100, another session commits 50, and then the first runs UPDATE ... SET balance = balance - 10. What does the update start from?

    Writes and locking reads always work on the latest committed version of a row, not on the snapshot. The result is 40.
  4. 4. Why can one very long transaction hurt even a read-only workload?

    Purge cannot remove versions that an open read view might still need, so undo grows and reads that walk long version chains slow down.
  5. 5. What is the best fix for write skew between two doctors going off call?

    A plain snapshot read takes no lock, so both transactions pass the check. Locking what you read makes the second transaction wait and then see the updated data.

Summary

  • Isolation levels decide which of dirty, non-repeatable and phantom reads can happen; MySQL's default is REPEATABLE READ.
  • InnoDB implements isolation with MVCC: old row versions live in the undo log and a read view picks the visible one.
  • Plain SELECTs read a snapshot and take no locks; UPDATE, DELETE and SELECT ... FOR UPDATE use the latest committed data.
  • READ COMMITTED uses a fresh snapshot per statement and fewer gap locks; use it with row-based binary logging.
  • Snapshots do not prevent write skew; lock what you read or enforce the rule with a constraint.
  • Keep transactions short so undo can be purged.

Next: Locking, Gap Locks and Deadlocks.