How MySQL Implements Transaction Isolation: Locks, MVCC, and Gap Locks Explained

Preface

When discussing database transactions, people often recall ACID properties, isolation levels, and problems like dirty reads, non‑repeatable reads, and phantom reads, but few understand how these features are actually implemented in MySQL.

This article first examines the implementation of transaction isolation in MySQL, focusing on the InnoDB engine.

Definition

Isolation means that the effect of concurrently executed transactions appears as if they were run serially; a transaction should only see changes it made itself.

Standard SQL Isolation Levels

The simplest implementation would serialize all transactions, but that hurts concurrency. SQL defines four isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. The following summary shows which anomalies each level permits:

Read Uncommitted : allows dirty reads, non‑repeatable reads, and phantom reads. Read Committed : prevents dirty reads, but allows non‑repeatable reads and phantom reads. Repeatable Read : prevents dirty and non‑repeatable reads, but allows phantom reads. Serializable : prevents all three anomalies.

In InnoDB, MVCC solves dirty reads at the Read Committed level, and gap locks eliminate phantom reads at the Repeatable Read level.

Implementation Principles

Standard SQL Isolation via Locks

Concurrency control is typically achieved with pessimistic locking. Locks are acquired and released frequently, leading to potential read‑write conflicts.

Using only locks requires many lock/unlock operations and can cause blocking, e.g., a transaction updating a row blocks another transaction reading that row until commit.

MySQL introduces MVCC to avoid such blocking for read operations.

InnoDB Isolation Implementation

Key concepts:

Locking reads : SELECT ... LOCK IN SHARE MODE or SELECT ... FOR UPDATE, which acquire shared or exclusive row locks.

Consistent (non‑locking) reads : InnoDB provides a snapshot of the database at a point in time, visible to the transaction without acquiring locks.

Current reads vs. snapshot reads : Current reads (UPDATE, DELETE, INSERT, SELECT ... LOCK IN SHARE MODE, SELECT ... FOR UPDATE) see the latest version and lock rows; snapshot reads (plain SELECT) see a version from the transaction’s start time.

Implicit vs. explicit locking : InnoDB automatically acquires locks based on the isolation level (implicit), and also supports explicit LOCK TABLE / UNLOCK TABLE statements.

select ... lock in share mode // shared lock
select ... for update // exclusive lock

lock table
unlock table

InnoDB’s MVCC eliminates most read‑write conflicts and, together with gap locks, resolves phantom and non‑repeatable reads, greatly improving concurrency.

Common Misconceptions

Does phantom read include deletes?

Phantom reads occur when a query returns different row sets at different times, typically due to inserts. In Repeatable Read, InnoDB locks the rows it reads, preventing deletes that would cause phantom rows, so deletes are not considered phantom reads at this level.

Can MVCC alone solve phantom reads?

MVCC alone cannot prevent phantom reads; gap locks are required. The following example demonstrates a phantom read occurring with MVCC only:

begin;
-- initial empty table
select * from users; -- snapshot read, empty
-- another transaction inserts a row
select * from users; -- snapshot still empty
update users set name='mysql' where id=1; -- current read, creates a new version
select * from users; -- snapshot now sees the inserted row
commit;

Using a locking read with gap locks prevents the insert until the transaction finishes, eliminating the phantom.

Original source: https://segmentfault.com/a/1190000025156465 (author: X先生)