Why Does an UPDATE on a Non‑Indexed Column Lock the Whole MySQL Table?

Effect of UPDATE/DELETE on a non‑indexed column

When the WHERE clause of an UPDATE or DELETE statement uses a column that has no index, InnoDB cannot locate the target rows directly. It must perform a full‑table scan, and the locking behavior changes fundamentally.

Lock chain triggered by a full scan

Intention exclusive lock (IX) – a lightweight table‑level lock that signals the transaction intends to modify rows. It does not block other row‑level locks but prevents LOCK TABLES … WRITE on the table.

Next‑key lock – a combination of a row lock (X) and a gap lock. InnoDB creates one next‑key lock for each index entry (or gap) examined during the scan.

Because the scan touches every row, a next‑key lock is created for each range:

CREATE TABLE user (
  id   INT PRIMARY KEY,   -- indexed primary key
  name VARCHAR(100)       -- no index
) ENGINE=InnoDB;
INSERT INTO user VALUES (1,'张三'),(2,'李四'),(3,'王五');

-- Transaction T1
BEGIN;
UPDATE user SET name='张三_new' WHERE name='张三';

During this statement InnoDB acquires:

1 IX lock on the table.

4 next‑key locks covering the ranges (-∞,1], (1,2], (2,3], (3,+∞).

Thus the statement creates **five lock objects** in total.

Why other transactions are blocked

Consider a second transaction that updates a different row:

-- Transaction T2 (blocked)
BEGIN;
UPDATE user SET name='李四_new' WHERE id=2;

Even though the two statements target different rows, T1’s full‑table scan has already placed a next‑key (or gap) lock on the range that includes id=2. Consequently T2 must wait until T1 commits or rolls back. The same effect blocks INSERTs and DELETEs that would affect any scanned range.

InnoDB lock mechanism relies on indexes

If the WHERE condition uses an indexed column, InnoDB can jump directly to the matching rows and lock only those rows. When the condition uses a non‑indexed column, InnoDB must scan the whole table, and the locking behavior changes as described above.

Three logical lock types in the source code

In the InnoDB source code each lock is represented by a lock_t structure. The same structure can act as a row lock, a gap lock, or a next‑key lock depending on the lock_mode flag: LOCK_REC_NOT_GAP – locks only the record (row lock). LOCK_GAP – locks only the gap (gap lock).

no special flag – locks both the record and the preceding gap (next‑key lock).

Therefore the three logical lock types are not separate objects; they are different flag settings on a single lock_t instance.

Lock object count for the example

1 IX lock object (table‑level intention lock).

4 next‑key lock objects (one for each index entry and its preceding gap).

Total **5 lock objects** are created for the UPDATE that scans the whole table.

Design rationale

Using a unified lock_t reduces the number of lock structures and improves concurrency. By toggling the lock_mode bits, the same object can serve as a row lock, a gap lock, or both, which is why InnoDB reports only three logical lock types.

Practical takeaway

MySQL does not issue an explicit table‑level lock (such as LOCK TABLES) for DML on a non‑indexed column, but the mandatory full‑table scan forces InnoDB to acquire a combination of row, gap, and intention locks that block all other write operations. Adding appropriate indexes to columns used in WHERE clauses eliminates the full scan and prevents this “pseudo‑table lock” behavior.