Is MySQL Insert Truly Concurrent? Experiments Reveal the Truth

1. Table Definition

The test uses a simple userInfo table with an auto‑increment id primary key and a unique index on name:

CREATE TABLE `userInfo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

2. Validation Procedure

Two transactions are opened. Transaction 1 inserts a row but does not commit. Transaction 2 then starts and inserts another row. If inserts were serialized, Transaction 2 would block; if they are concurrent, it proceeds immediately.

3. Experiment Results – Concurrency

Transaction 2 succeeds instantly, receiving auto‑increment ID 2 while Transaction 1 is still open, proving that InnoDB allows concurrent inserts at the transaction level.

The author notes that although inserts are concurrent, the storage engine still locks data pages, so rows on the same page are written serially.

4. Observing Phantom Reads

When the same experiment is repeated with a SELECT before the second insert, the second transaction’s insert no longer appears to cause a phantom read. The article shows screenshots where Transaction 1 can see the row inserted by Transaction 2 before it commits, indicating a phantom‑read scenario.

5. Why Phantom Reads Occur – Lock Types

MySQL does not place gap locks on plain INSERTs, opting for an insert‑intention lock to keep insert concurrency high. The documentation defines this lock as a lightweight lock that allows multiple transactions to insert into the same index gap without blocking each other.

5.1 MVCC and ReadView

For SELECT statements, InnoDB relies on Multi‑Version Concurrency Control (MVCC) and a ReadView to present a consistent snapshot. The ReadView records the IDs of all active transactions ( m_ids), the smallest active ID ( min_trx_id), the next ID to be assigned ( max_trx_id), and the creator’s ID ( creator_trx_id).

m_ids : list of active transaction IDs at ReadView creation.

min_trx_id : smallest ID in m_ids.

max_trx_id : next ID to be allocated.

creator_trx_id : ID of the transaction that generated the ReadView.

Visibility rules:

If a row’s trx_id equals creator_trx_id, the current transaction sees its own changes.

If trx_id < min_trx_id, the row was committed before the ReadView and is visible.

If trx_id ≥ max_trx_id, the row was created after the ReadView and is invisible.

If min_trx_id ≤ trx_id < max_trx_id, the row is visible only if its trx_id is not in m_ids (i.e., the creating transaction has committed).

In READ‑COMMITTED isolation, a ReadView is built on the first SELECT of a transaction; in REPEATABLE‑READ, the same ReadView is reused for all subsequent SELECTs, preventing newly committed rows from appearing.

6. Conclusion

InnoDB inserts are concurrent at the transaction level.

MySQL does not suffer from phantom reads on INSERT because MVCC together with ReadView ensures a consistent snapshot, while insert‑intention locks keep insert concurrency high.