Understanding InnoDB Locks and Indexes: From Record to Gap Locks

1 Background

With the development of the Internet and the rise of high‑concurrency services, the fine‑grained row lock of MySQL InnoDB has become a core feature. When used improperly under heavy concurrency, it can cause severe performance problems. For example, the fine‑grained row lock is implemented on index records, but if the index is not used, it falls back to a table lock, which is disastrous for performance. Below we introduce the InnoDB lock mechanism from the perspective of indexes.

2 InnoDB Index Review

In InnoDB there are two kinds of indexes: the primary (clustered) index and secondary (non‑clustered) indexes.

Primary index:

Each table has only one primary index, stored as a B+Tree where leaf nodes contain the primary key value and the full row data; non‑leaf nodes store only the primary key values. If a table has no explicit primary key, MySQL automatically adds a hidden rowid (default 4 bytes) as the primary key and builds the clustered index on it.

Secondary index:

A table can have multiple secondary indexes, also B+Tree structured. Leaf nodes store the indexed column values and the corresponding primary key values; other nodes store only the indexed column values. Thus secondary indexes differ from the clustered index.

Every InnoDB table has a clustered index:

If a PK is defined, it is the clustered index.

If no PK, the first non‑null UNIQUE column becomes the clustered index.

If neither exists, InnoDB creates an implicit row_id as the clustered index.

The following diagram illustrates the difference between the two index types (example table with four rows, Id as primary index, Name as secondary index).

5, Gates, Microsoft
7, Bezos, Amazon
11, Jobs, Apple
14, Ellison, Oracle

InnoDB data retrieval process

The table has two indexes: id as primary, name as secondary. To query id=14, only the primary index is used. To search name='Ellison', two steps are required:

Search the secondary index to find the row with name='Ellison' and obtain id=14.

Search the primary index for id=14 to retrieve the full record. This secondary‑index lookup is called a “back‑lookup” (回表) in MySQL.

MyISAM data retrieval process

Find the record address from the index.

Use the address to fetch the data row.

Comparison shows that InnoDB prefers primary‑key lookups, which require only one index access. Using a secondary index incurs an extra back‑lookup, making it slower.

3 Important InnoDB Locks

★ InnoDB’s default transaction isolation level is REPEATABLE READ (RR); all the following explanations assume this level.

Record Locks : lock a single row record; InnoDB uses them to implement row‑level locking, allowing concurrent access to different rows.

Gap Locks : lock a range between index records (or before the first or after the last record) to prevent phantom reads.

Next‑Key Locks : a combination of a gap lock and a record lock that locks the range covered by the query condition, designed to prevent phantom reads.

3.1 Record Locks

Record locks lock index records, e.g.: select * from table where id=5 for update; This locks the index record with id=5, preventing other transactions from inserting, updating, or deleting that row.

By contrast, a plain SELECT without FOR UPDATE performs a snapshot read and does not acquire locks.

3.2 Gap Locks

Gap locks lock the interval between index records (or before the first or after the last record). Continuing the previous example:

# Table structure
table (Id PK, Name, Company);

# Data
5, Gates, Microsoft
7, Bezos, Amazon
11, Jobs, Apple
14, Ellison, Oracle

Executing:

select * from table
where id between 7 and 13
for update;

locks the range to prevent other transactions from inserting a row with id=8, thus avoiding phantom rows. Gap locks are disabled when the isolation level is lowered to READ COMMITTED.

3.3 Next‑Key Locks

Next‑Key locks lock both the index record and the surrounding gap (left‑open, right‑closed). Example range diagram:

(-∞, 1]
(1, 7]
(7, 9]
(9, +∞]

With InnoDB under RR, the engine uses next‑key locks by default to guarantee data integrity and consistency in concurrent environments. They also become ineffective when the isolation level is changed to RC.

4 Summary

InnoDB stores row data together with the clustered index; MyISAM uses the index address to locate rows, which is less efficient.

InnoDB’s clustered index stores the full row; secondary indexes store only the primary key, so a secondary‑index lookup requires two passes.

Record locks lock the specific indexed record.

Gap locks lock the interval to prevent other transactions from inserting within the gap.

Next‑Key locks combine record and gap locks to prevent phantom reads.

SELECT … FOR UPDATE locks in various cases:

Primary‑key column: row lock.

Unique index column: row lock.

Secondary index column: row lock.

Primary‑key range: multiple row locks.

Ordinary column: table lock.

Empty result set: no lock.

Row lock vs. table lock: if a transaction holds a row lock, other transactions targeting the same row wait; if a table lock is held, any operation on any row waits.