Why Does MySQL Lock Non‑Indexed Columns to the Primary Key? Experiments Explained

Hello, I am Su San.

Earlier I wrote an article about MySQL locks, and many readers raised representative questions, so I conducted a set of experiments to investigate the facts.

The previous article mentioned record locks, i.e., locks on records, specifically on indexed records.

The statement that locks are applied to indexes can be confusing, especially regarding whether secondary index locks cause blocking under REPEATABLE READ and READ COMMITTED isolation levels.

Below are the experiments (MySQL version 5.7.26).

Experiment 1: READ COMMITTED, locking a non‑indexed column

First, create a simple table with only a primary key index and no secondary index.

CREATE TABLE `yes` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `address` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

Set the isolation level to READ COMMITTED and disable autocommit.

Start transaction A and execute a SELECT ... FOR UPDATE on a non‑indexed column (name) without committing.

Then start transaction B and run a similar SELECT on a different name value.

Although the two queries lock different name values, transaction B is blocked because both queries lock the primary key record (id=1). The lock information shows an X lock on the primary key index.

After committing transaction A, transaction B proceeds.

Conclusion: when a query locks a non‑indexed column, InnoDB must lock the corresponding primary‑key (clustered) index record, and the lock is held until the transaction commits.

The clustered index’s non‑leaf nodes contain only primary‑key values, so a full‑table scan is performed. If a scanned record is already locked, the transaction blocks even if that record is not the target.

Experiment 2: REPEATABLE READ, locking a non‑indexed column

Using the same table and data, start transaction A with a SELECT ... FOR UPDATE (READ COMMITTED) and transaction B similarly. Under REPEATABLE READ, transaction B is blocked, and the lock remains on the scanned record (id=1) throughout the transaction.

Further experiments with transactions C and D (executed in reverse order) show that the lock data is still the supremum record, indicating that InnoDB locks the maximum record when inserting at the end of an auto‑increment primary key.

When inserting a row with a specific id that falls between existing rows, the insert is blocked by the record lock of the higher id (id=6) held by another transaction.

Key observations:

Locking a non‑indexed column forces InnoDB to lock the primary‑key index, leading to full‑table record locking.

Under READ COMMITTED, SELECT ... FOR UPDATE locks records during the scan and releases them immediately if the row does not satisfy the condition.

Under REPEATABLE READ, once a record is scanned it remains locked for the whole transaction, even if it does not match the predicate.

Experiment 3: READ COMMITTED, locking an indexed column

Create the same table but add a secondary index on the name column.

CREATE TABLE `yes` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `address` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

With the secondary index in place, two concurrent SELECT ... FOR UPDATE statements on different name values do not block each other because the lock is applied to the secondary index record.

However, when a third transaction attempts to UPDATE a row that matches the indexed value, it blocks because the secondary index record is locked.

Thus, when a secondary index can be used, locks are placed on that index rather than the primary key.

Experiment 4: REPEATABLE READ, locking an indexed column

Under REPEATABLE READ, two concurrent SELECT ... FOR UPDATE on the same indexed value cause blocking due to next‑key (gap) locks around the matching record.

When the first transaction commits, a subsequent INSERT that would fall into the locked gap proceeds without blocking.

If another transaction then tries to insert a row that falls between the locked record and its neighbour, it is blocked by a record lock plus a gap lock (next‑key lock).

Summary

When an index can be used, InnoDB locks only that index. If no secondary index matches, the lock is forced onto the clustered primary key, causing full‑table record locking.

Under REPEATABLE READ, any record scanned (whether it satisfies the predicate or not) is locked for the duration of the transaction.

Under READ COMMITTED, SELECT ... FOR UPDATE locks records during the scan and releases them immediately if they do not meet the condition, while UPDATE locks only rows that actually match.

Consequently, inserts are blocked in REPEATABLE READ when a gap lock exists, but not in READ COMMITTED unless the insert would conflict with a locked primary‑key record.

All experiments were performed on MySQL 5.7.26 with the InnoDB storage engine.