Understanding MySQL Transactions, MVCC, Isolation Levels, and Lock Types

1. ACID Properties

Atomicity : All operations in a transaction succeed or all are rolled back.

Consistency : A transaction transforms the database from one consistent state to another.

Isolation : Concurrent transactions are isolated from each other.

Durability : Once committed, changes are permanent.

2. Transaction Isolation Levels

SQL defines four isolation levels:

READ UNCOMMITTED : Allows dirty reads, non‑repeatable reads, and phantom reads.

READ COMMITTED : Prevents dirty reads; phantom reads and non‑repeatable reads are allowed.

REPEATABLE READ : Prevents dirty and non‑repeatable reads; phantom reads are allowed.

SERIALIZABLE : Highest level; all three anomalies are prevented.

MySQL’s default isolation level is REPEATABLE READ .

3. MVCC (Multiversion Concurrency Control)

In InnoDB each row stores two hidden values: a creation version and an expiration version. The values are transaction IDs, not timestamps.

Operations under MVCC:

Insert : Sets the creation version to the current transaction ID; expiration is undefined.

Select : Returns rows whose creation version ≤ current transaction ID and whose expiration version is undefined or > current transaction ID.

Delete : Sets the expiration version to the current transaction ID; actual removal is performed later by a background thread.

Update : Implemented as an Insert of the new version plus a Delete of the old version.

Two read concepts:

Snapshot read : Non‑locking SELECT that may return historical data.

Current read : Reads that acquire locks (e.g., SELECT … FOR UPDATE, INSERT, UPDATE, DELETE).

Snapshot read example

select * from table where ?;

In READ COMMITTED each SELECT creates a new snapshot; in REPEATABLE READ the first SELECT after the transaction starts defines the snapshot.

Current read examples

select * from table where ? lock in share mode; (shared lock)

select * from table where ? for update; (exclusive lock)

insert into table values (...); (intent‑share lock)

update table set ... where ?; (exclusive lock)

delete from table where ?; (exclusive lock)

4. Lock Types and Modes

MySQL locks consist of a lock type (granularity) and a lock mode (shared or exclusive).

Lock Types

Table lock : Locks the whole table; lowest concurrency.

Row lock : Locks individual rows; used by InnoDB.

Row‑level sub‑types: gap lock, record lock, next‑key lock, insert‑intent GAP lock.

Lock Modes

Shared lock (S) : Allows other transactions to read but not modify the locked row.

Exclusive lock (X) : Allows the holder to read and modify; blocks all other locks.

Lock Conflict Matrix

Row lock ↔ table lock – conflict

Row lock ↔ same row lock – conflict

Row lock ↔ gap lock – no conflict

Gap lock ↔ insert‑intent GAP lock – conflict

Shared lock ↔ shared lock – no conflict

Shared lock ↔ exclusive lock – conflict

Exclusive lock ↔ exclusive lock – conflict

5. Practical Lock Analysis for Sample SQL Statements

Assume InnoDB, manual transaction control (autocommit disabled), and the REPEATABLE READ isolation level.

Scenario 1 : select * from table where id = 1; – snapshot read, no lock.

Scenario 2 : select * from table where id = 1 lock in share mode; – shared row lock.

Scenario 3 : select * from table where id = 1 for update; – shared lock upgraded to exclusive lock.

Scenario 4 : update user set username = 2 where id = 1; – shared lock then exclusive lock on the row.

Scenario 5 : update user set username = 2 where name = 1; – lock on the unique index entry, then lock on the primary key row.

Scenario 6 : update user set name = 2 where username = 5; – exclusive lock on the index record plus GAP lock on the surrounding range, then exclusive lock on the primary key row.

Scenario 7 : update user set name = 2 where username > 5; – if an index is used, lock each qualifying row as in Scenario 6; otherwise full‑table scan with table‑level locks.

Scenario 8 : update user set name = 2 where username = 5; with no index – full table scan, resulting in a table lock and table‑level gap lock.

When a row does not exist, InnoDB acquires a gap lock on the index position to prevent phantom inserts.

6. Summary

The article covered MySQL transaction basics, MVCC mechanics, lock types and modes, and demonstrated how different SQL statements acquire locks under InnoDB, helping developers understand and troubleshoot concurrency issues.