Understanding MySQL SELECT ... FOR UPDATE: When It Locks Rows vs. Tables

In MySQL a normal SELECT statement does not acquire any lock, but appending FOR UPDATE turns the query into a pessimistic lock that can block other transactions.

Whether the lock is applied to a single row or the whole table is determined by the presence of an index or primary key on the columns used in the WHERE clause: if an indexed/primary‑key column is used, a row lock is taken; otherwise the engine falls back to a table lock.

Verification:

Below is the table definition used for the experiments. The id column is the primary key and age has a unique index.

CREATE TABLE `user` (
  `id`   INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL,
  `age`  INT(11) DEFAULT NULL,
  `code` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8;

Because SELECT ... FOR UPDATE runs inside a transaction, automatic commit must be disabled (e.g., SET @@autocommit=0; ), otherwise the lock is released immediately.

Example Scenarios

Scenario 1 : Query by primary‑key id=1 in one transaction, then start a second transaction that tries to update the same row. The second transaction blocks, confirming a row lock.

Scenario 2 : Query by primary‑key id=2 and update in another transaction; the update succeeds, showing the lock is limited to the targeted row.

Scenario 3 : Query using the indexed column age . The lock is still a row lock because the index is used.

Scenario 4 : Query using a non‑indexed column code . The lock escalates to a table lock, causing other transactions that touch any row to be blocked.

Result

If the SELECT condition uses an indexed or primary‑key column, SELECT ... FOR UPDATE acquires a row‑level lock; if the condition uses a plain column without an index, the statement locks the entire table.