Isolation Levels, Consistent Reads, Locking Reads, MVCC, and Anomalies

Learning outcomes

Concurrency becomes understandable when you separate two questions: which committed row version does my read see? and which rows or index ranges does my statement lock against other writers? InnoDB implements multi-version concurrency control (MVCC), meaning normal consistent reads can often read an earlier committed version without blocking a writer.

Standalone prerequisite for this lesson

If you arrived here without running Lessons 1–3, create servicehub_write_lab with the Lesson 1 setup first. The experiments assume InnoDB tables parts_inventory and work_orders. Open two or three independent mysql client sessions so each has its own transaction state.

MVCC mental model: current row versus visible row version

When a transaction updates an InnoDB row, undo information allows other transactions to reconstruct older committed versions as required by their read view. A normal SELECT is usually a consistent nonlocking read. A locking read (FOR SHARE or FOR UPDATE) asks for current rows with locks because the application intends to coordinate subsequent changes.

REPEATABLE READ: one transaction, stable consistent-read snapshot

Use two sessions. First reset one inventory value:

UPDATE parts_inventory SET on_hand=20 WHERE part_id=1; COMMIT;

Session A:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;START TRANSACTION;SELECT part_id,on_hand FROM parts_inventory WHERE part_id=1;-- expected: 20-- Leave the transaction open.

Session B:

UPDATE parts_inventory SET on_hand=19 WHERE part_id=1;COMMIT;SELECT part_id,on_hand FROM parts_inventory WHERE part_id=1;-- expected in B: 19

Back in Session A:

SELECT part_id,on_hand FROM parts_inventory WHERE part_id=1;-- expected: still 20 in the same REPEATABLE READ snapshotCOMMIT;SELECT part_id,on_hand FROM parts_inventory WHERE part_id=1;-- new transaction sees 19

This does not mean Session A locked the row. It means its consistent read uses a stable snapshot. Session B was able to update and commit.

READ COMMITTED: each consistent read gets a fresh snapshot

Repeat the experiment with Session A using READ COMMITTED. After Session B commits 19, the second SELECT in Session A sees the newly committed value because each consistent read gets its own snapshot.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;START TRANSACTION;SELECT on_hand FROM parts_inventory WHERE part_id=1;-- Have Session B update and COMMIT here.SELECT on_hand FROM parts_inventory WHERE part_id=1;-- second result can reflect B's committed updateROLLBACK;

This exposes a nonrepeatable-read style effect by design. READ COMMITTED can improve some concurrency patterns, but it changes what application code can assume across repeated reads.

READ UNCOMMITTED and SERIALIZABLE: understand the ends of the spectrum

READ UNCOMMITTED permits dirty reads: a transaction can observe a row version another transaction has changed but not committed. It is rarely a sound default for business logic because the observed value can disappear on rollback. Demonstrate it only in this local lab.

SERIALIZABLE is the strongest standard isolation level. In InnoDB, when autocommit is disabled, plain SELECT statements are treated as locking reads in circumstances documented by MySQL, increasing blocking so concurrent execution behaves more like a serial order. Stronger isolation is not “free correctness”; it trades concurrency for stricter interaction rules.

Locking reads: when the next step depends on the current row

Suppose a dispatcher must reserve one part only if stock is positive. A consistent read followed later by an UPDATE leaves a race. Lock the row inside the same transaction:

START TRANSACTION;SELECT part_id,on_handFROM parts_inventoryWHERE part_id=1FOR UPDATE;UPDATE parts_inventorySET on_hand=on_hand-1WHERE part_id=1 AND on_hand>0;-- verify, then COMMIT or ROLLBACK

If Session B tries to update that same row before A commits, B waits for the conflicting lock. From a diagnostic session:

SELECT *FROM performance_schema.data_lock_waits\GSELECT ENGINE_TRANSACTION_ID,THREAD_ID,OBJECT_NAME,INDEX_NAME,       LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATAFROM performance_schema.data_locksWHERE OBJECT_SCHEMA='servicehub_write_lab'  AND OBJECT_NAME='parts_inventory'ORDER BY ENGINE_TRANSACTION_ID,LOCK_STATUS;

The lock tables show a live relationship, not the business meaning of the operation. Your application still owns the invariant “stock must not become negative.”

Range locking: record, gap, and next-key behavior

InnoDB row locks are index-record locks. Under the default REPEATABLE READ isolation level, locking searches and index scans can use next-key locks: a record lock plus a lock on the preceding index gap. The purpose is to prevent another transaction from inserting a new row into a locked range and creating a phantom for a locking operation.

Create a small indexed queue:

CREATE TABLE IF NOT EXISTS dispatch_queue (  job_id INT NOT NULL,  priority INT NOT NULL,  status VARCHAR(12) NOT NULL,  PRIMARY KEY(job_id),  KEY ix_dispatch_status_priority(status,priority,job_id)) ENGINE=InnoDB;DELETE FROM dispatch_queue;INSERT INTO dispatch_queue VALUES (10,1,'open'),(20,1,'open'),(30,2,'open');

Session A under REPEATABLE READ:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;START TRANSACTION;SELECT job_id,priorityFROM dispatch_queueWHERE status='open' AND priority=1FOR UPDATE;-- keep open

A concurrent insert that falls into the locked index range may wait because of gap/next-key locking. Under READ COMMITTED, gap locking is disabled for ordinary searches/index scans except cases such as foreign-key and duplicate-key checking. Do not summarize this as “READ COMMITTED has no gap locks whatsoever.”

Common mistake: expecting a normal snapshot SELECT to serialize a read-modify-write workflow

An even stronger pattern for simple inventory decrements can be one atomic statement:

UPDATE parts_inventorySET on_hand=on_hand-1WHERE part_id=1 AND on_hand>0;SELECT ROW_COUNT() AS reserved_one;-- 1 means this statement changed one row; 0 means no qualifying stock row was available.

This removes the application-side read/write gap altogether.

Hands-on isolation matrix

1. Run the REPEATABLE READ two-session snapshot experiment and record both sessions’ outputs.
2. Repeat under READ COMMITTED and identify the changed second-read behavior.
3. Use FOR UPDATE to create a lock wait and inspect it from a third session.
4. Run the range-lock queue experiment under REPEATABLE READ, then repeat using READ COMMITTED and compare insert blocking.
5. Finish every open transaction with COMMIT or ROLLBACK and restore the inventory seed.

Anomaly map: name the behavior you are trying to prevent

A dirty read observes data another transaction has changed but not committed. Nonrepeatable read describes a transaction reading the same logical row twice and seeing a different committed value. A phantom is a newly qualifying or disappearing row in a repeated predicate/range query. These names are useful only when attached to a concrete application rule.

InnoDB’s MVCC and locking behavior means “phantom prevention” needs careful wording. Under REPEATABLE READ, ordinary consistent reads use a snapshot, so a repeated SELECT can remain stable without locking the range. Locking reads and DML need current rows and can use next-key locks to prevent inserts into qualifying index ranges. Those are different mechanisms serving different needs.

Also remember that a transaction always sees its own writes. A REPEATABLE READ transaction can therefore have a view that combines its own newer modifications with older snapshot versions of rows changed by other transactions. Do not describe MVCC as a frozen copy of the whole database; it is a visibility rule over row versions.

Isolation is one layer of correctness. Unique keys, CHECK constraints, foreign keys, atomic conditional UPDATE statements, explicit locks, and idempotency keys can encode stronger business invariants than relying on an isolation label alone.

Production judgment and next step

Choose isolation and locking from concrete anomalies your application must prevent, not from slogans about “stronger is better.” Keep transactions short, index locking predicates, and prefer atomic conditional DML when it can encode the invariant directly. Monitor lock waits because an otherwise correct transaction can still degrade under contention.

Lesson 5 turns that contention into deliberate experiments: a lock-wait timeout, a deterministic deadlock, diagnosis, lock ordering, and bounded idempotent retries.

Authoritative references

• MySQL 8.4 Reference Manual — Transaction Isolation Levels
• MySQL 8.4 Reference Manual — Consistent Nonlocking Reads
• MySQL 8.4 Reference Manual — Locking Reads
• MySQL 8.4 Reference Manual — InnoDB Locking
• MySQL 8.4 Reference Manual — Performance Schema data_locks