Understanding MySQL Transaction Isolation Levels and InnoDB Locking Mechanisms

A mock interview scenario introduces Xiao Zhang, who is asked about MySQL transaction fundamentals. He correctly lists the four ACID properties: Atomicity, Consistency, Isolation, and Durability.

The interviewer then asks about the four isolation levels, which Xiao Zhang enumerates from highest to lowest: SERIALIZABLE, REPEATABLE READ, READ COMMITTED, and READ UNCOMMITTED.

Each isolation level’s typical anomaly is explained: READ UNCOMMITTED can cause dirty reads; READ COMMITTED prevents dirty reads but may produce non‑repeatable reads; REPEATABLE READ solves non‑repeatable reads but can still suffer phantom reads; SERIALIZABLE eliminates all three anomalies but has the lowest performance.

MySQL’s default isolation level is REPEATABLE READ, while Oracle defaults to READ COMMITTED.

To handle phantom reads, InnoDB relies on locking. Three lock types are described: Record Lock (locks a single row), Gap Lock (locks a range between rows), and Next‑Key Lock (combines Record and Gap locks). A note mentions that when a unique index is used, Next‑Key Lock degrades to a Record Lock.

Example DDL and data insertion:

CREATE TABLE T (
  id int,
  name varchar(50),
  f_id int,
  PRIMARY KEY (id),
  KEY (f_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into T SELECT 1,'张三',10;
insert into T SELECT 2,'李四',30;

Transaction A reads and updates a row using a non‑unique index, which triggers a Next‑Key Lock on the range (-∞,10] and (10,30].

select * from t;
update t set name = '李四' where f_id = 10;

When Transaction B attempts to insert rows that fall into the locked gaps, it encounters lock‑wait timeout errors:

INSERT INTO T SELECT 3,'王五',10;  -- blocked by row lock
INSERT INTO T SELECT 4,'赵六',8;   -- blocked by gap lock
INSERT INTO T SELECT 5,'孙七',18;  -- blocked by gap lock

If the indexed column is not indexed, InnoDB applies a gap lock to the entire table, causing all inserts to wait for Transaction A to commit.

The interview concludes with a reminder that understanding isolation levels and InnoDB locking is essential for passing database interview questions.