Understanding SELECT ... FOR UPDATE Locking in MySQL: Row vs. Table Locks

In MySQL, a plain SELECT statement does not acquire locks, but using SELECT ... FOR UPDATE adds a pessimistic lock.

Whether the lock is a row lock or a table lock depends on whether the query uses an indexed column or primary key; without an index it locks the whole table, otherwise it locks the specific rows.

Verification

Table creation SQL:

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;

Disable autocommit to use manual commit:

set @@autocommit=0; (0 = manual commit, 1 = automatic).

Example 1

Transaction A selects a row by primary key id , then Transaction B attempts to update the same row; the update is blocked, demonstrating a row lock.

Diagram 1: Transaction A opened but not committed.

Diagram 2: Transaction B attempts update and is blocked.

Diagram 3: Transaction B eventually times out.

Example 2

Another transaction updates a different row (id=2).

Example 3 (Indexed)

The table has a unique index on the age column.

Example 4 (Non‑indexed)

Operations on a regular column code without an index.

Result: If the SELECT ... FOR UPDATE query uses an indexed column or primary key, MySQL applies a row lock; if it uses a non‑indexed column, it applies a table lock.