Understanding the INSERT Locking Process in MySQL by Analyzing the Source Code

Read MySQL source to understand INSERT lock flow

The author revisits a question about possible phantom reads when a select ... lock in share mode is executed before or after an INSERT under REPEATABLE READ isolation, and explains why the scenario cannot produce phantom reads.

INSERT locking analysis

When an INSERT is issued, MySQL first checks for conflicting insert‑intention locks; if none exist, it acquires an exclusive record lock after writing the row. A preceding select ... lock in share mode acquires a GAP lock, which does not conflict with the insert‑intention lock, so no phantom read occurs. The article also discusses the concept of implicit lock conversion, where a transaction that inserted a row without an explicit lock can have its lock materialized when another transaction attempts to lock the same record.

Compiling MySQL from source

The author describes downloading MySQL 5.6.40 source code, installing required tools (CMake, Bison, Visual Studio), and configuring the build with cmake -G "Visual Studio 11 2012 Win64" .. . Minor source modifications are needed, such as converting sql/sql_locale.cc to Unicode and changing an assertion in sql/mysqld.cc .

Debugging INSERT lock steps

Using two client sessions, one runs insert into t(id) value(30) while the other runs select * from t where id = 30 lock in share mode . By setting breakpoints in lock_rec_insert_check_and_lock and tracing the call stack ( mysql_parse → mysql_execute_command → mysql_insert → write_record → handler::ha_write_row → innobase::write_row → row_insert_for_mysql ), the author observes where insert‑intention locks and GAP locks are applied.

The article highlights the distinction between lightweight latches (RW‑LOCK) used for page access and traditional row/table locks. INSERT acquires an RW‑X‑LATCH on the target page, performs the lock‑conflict check, writes the row, and releases the latch. SELECT with lock in share mode acquires an RW‑S‑LATCH, checks for active transactions on the record, and may convert an implicit lock to an explicit exclusive lock.

Conclusion

Both INSERT and select ... lock in share mode operations are protected by latches and lock conversion mechanisms, ensuring that phantom reads do not occur. The detailed source‑level walkthrough clarifies the exact points where MySQL acquires insert‑intention locks, GAP locks, and exclusive record locks.