Master MySQL ACID, Isolation Levels, Locks & Indexes – A Deep Dive

The author shares a GitHub repository that collects essential interview material such as MySQL fundamentals, algorithms, Redis, operating systems, and more, inviting readers to star the repo for support.

MySQL Isolation Levels

The SQL standard defines four isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Read Uncommitted can read uncommitted data; Read Committed sees only committed data; Repeatable Read guarantees that the same rows return identical values within a transaction; Serializable enforces a strict ordering to prevent any conflicts.

Repeatable Read is MySQL's default isolation level, ensuring consistent reads by using row‑level shared locks and multi‑version concurrency control (MVCC).

ACID Implementation in InnoDB

Atomicity is achieved via an undo log: before a transaction modifies data, the original state is recorded in the undo log so that a rollback can restore the previous state.

Durability relies on a redo log and a buffer pool. Changes are first written to the redo log (WAL) and the buffer pool; the buffer pool is periodically flushed to disk (flush‑dirty). This design balances random I/O for data pages with sequential I/O for the redo log, improving performance.

If MySQL crashes, the redo log is replayed to recover the database, guaranteeing durability.

Isolation Mechanisms

MySQL enforces isolation using locks (table‑level, row‑level, intention, shared, exclusive) and MVCC. Row‑level shared locks protect reads, while exclusive locks protect writes. Gap locks lock index ranges to prevent phantom reads.

Deadlocks occur when two transactions each hold a lock the other needs (e.g., each holds an exclusive lock on a different resource). InnoDB detects and resolves deadlocks by rolling back one transaction.

Index Fundamentals

MySQL uses B+‑trees for indexes. A B+‑tree is an N‑ary sorted tree with internal nodes storing keys and leaf nodes storing the actual row pointers. Compared to hash tables (no range queries) and skip lists (unsuitable for disk), B+‑trees support efficient range scans and are disk‑friendly.

Compared to other trees:

Balanced binary trees require frequent rotations, making inserts costly.

Red‑black trees store a single key per node, leading to deeper trees and more I/O.

B+‑trees store multiple keys per node, resulting in shallower depth and faster lookups.

B‑trees store both keys and data in internal nodes, while B+‑trees keep data only in leaves, reducing internal node size.

Clustered indexes are built on the primary key and store full row data in leaf nodes. Secondary indexes also use B+‑trees but their leaf nodes contain only the indexed column and the primary key, effectively acting as an index on the primary key.

Lock Types and Gap Locks

MySQL provides table‑level and row‑level locks. From a strength perspective, there are intention shared locks, shared locks, intention exclusive locks, and exclusive locks. SELECT statements are lock‑free by default, but can request shared or exclusive locks using LOCK IN SHARE MODE or SELECT ... FOR UPDATE.

Gap locks lock the range between index entries to prevent phantom reads, but they also increase the risk of deadlocks, especially when different transactions lock overlapping ranges on different indexes.

Interview Tips

When interviewers ask about two related concepts (e.g., clustered vs secondary indexes), clearly explain both similarities and differences. Highlight insights such as “a secondary index is essentially an index on the primary key” to stand out.

For deeper study, explore MVCC details, redo/undo log internals, and B+‑tree variations.