Understanding MVCC and Lock Mechanisms in MySQL

MVCC

MySQL 5.5+ uses InnoDB with row‑level locking, improving concurrency but introducing phenomena such as dirty reads, non‑repeatable reads and phantom reads.

Why MVCC is needed

InnoDB’s default isolation level is REPEATABLE READ (RR). MVCC together with gap locks resolves phantom reads. MVCC works by maintaining an undo‑log version chain and a read view for each transaction, avoiding lock‑based conflicts.

Undo log version chain

The undo log forms a linked list of row versions ordered by transaction commit time; the newest version is at the head.

Each row stores hidden fields db_trx_id (6 bytes) and db_roll_ptr (7 bytes) that point to the latest transaction and its undo record.

Read view

A read view contains:

m_ids – set of currently active transaction IDs

min_trx_id – smallest active transaction ID

max_trx_id – next allocated transaction ID

creator_trx_id – ID of the transaction that created the snapshot

In REPEATABLE READ, a snapshot is taken once per transaction; in READ COMMITTED a new snapshot is built for each SELECT.

Database Locks

MySQL locks are classified by granularity: global, table, and row.

Global lock

A global lock makes the whole instance read‑only; DDL/DML are blocked. It is typically used for logical backups.

flush tables with read lock

unlock tables

Table‑level locks

Shared read lock

Blocks DDL/DML, allows concurrent SELECT.

lock tables xxx read;

Exclusive write lock

Only the locking transaction can execute DQL/DML/DDL.

lock tables xxx write;

Metadata lock (MDL)

MySQL automatically acquires MDL to protect table metadata. Types include SHARED_READ, SHARED_WRITE, and EXCLUSIVE, which are compatible with each other as described.

Intention locks

Intention shared (IS) is compatible with table shared locks but conflicts with exclusive locks. Intention exclusive (IX) conflicts with both shared and exclusive table locks.

Row‑level locks

InnoDB locks index records, not the raw data rows.

Record lock (S/X, REC_NOT_GAP)

Prevents other transactions from updating or deleting the locked row. Shared (S) allows reads; exclusive (X) allows updates.

SELECT * FROM test_table WHERE id = 1 LOCK IN SHARE MODE;

Gap lock (S/X, GAP) / Next‑key lock

Gap locks protect the interval between index records to prevent phantom rows. Next‑key locks combine a record lock with the preceding gap.

Examples demonstrate how gap locks block inserts into a protected range while allowing updates to rows outside the gap.

Summary

The article shows how MySQL uses MVCC, read views, and various lock types (global, table, metadata, intention, row, gap, next‑key) to achieve isolation under different isolation levels and avoid phantom reads, while proper index usage can reduce lock contention.