Why Updating Non‑Indexed Columns Can Lock the Whole Table in MySQL

The author revisits questions raised by a previous post on MySQL locks and runs systematic experiments on MySQL 5.7.26 (InnoDB) to clarify how locks behave when the locked column lacks a secondary index.

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

A simple table yes with only a primary‑key index is created:

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;

Two transactions (A and B) each execute SELECT * FROM yes WHERE name='yes' FOR UPDATE without committing. Although they lock different values of name, both end up waiting because the engine must scan the primary‑key index; the first matching row (id = 1) is locked, causing B to block on A’s lock. The lock type is an X row‑level lock on the primary‑key index, and lock_data shows the locked record’s id.

When the same scenario is repeated with the order of statements reversed, the same blocking occurs, confirming that the lock is taken on the primary‑key record encountered during the full‑table scan.

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

Under REPEATABLE READ, the same two‑transaction pattern again blocks, but this time the lock persists for the whole transaction even after the row no longer matches the predicate. The lock remains on the primary‑key record (id = 1) until the transaction commits, demonstrating that REPEATABLE READ does not release locks early.

Experiment 3 – READ COMMITTED, locking an indexed column

The table is recreated with a secondary index on name ( KEY idx_name (name)). Transactions A and B now lock rows using the name index; they do not block each other because the lock is placed on the secondary index entry.

When a third transaction C attempts to insert a row with name='yes', it blocks because the insertion must acquire a gap lock on the index position occupied by the existing yes record.

Experiment 4 – REPEATABLE READ, locking an indexed column

With the same indexed table, transactions A and B that insert rows around the indexed value yes block each other due to next‑key (record + gap) locks placed before the transaction commits. After A and B commit, a new transaction C can insert without blocking, confirming that gap locks are released when the conflicting transaction finishes.

Key Takeaways

Locks are always applied to an index; if a secondary index can be used, the lock is placed there, otherwise the primary‑key (clustered) index is used, leading to full‑table scans.

Under READ COMMITTED, SELECT … FOR UPDATE locks rows during the scan and releases them immediately if the predicate fails; UPDATE first checks the predicate and locks only matching rows.

Under REPEATABLE READ, both SELECT … FOR UPDATE and UPDATE lock every row they scan, regardless of whether it satisfies the condition, and hold the locks until the transaction commits.

Gap (next‑key) locks appear in REPEATABLE READ when inserting near existing indexed values, causing insert‑blocking even when the exact row is not locked.

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