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

The author, a senior architect, explains that a normal SELECT statement does not acquire locks, but SELECT ... FOR UPDATE adds a pessimistic lock; the lock type depends on whether the query uses an indexed column or primary key.

If an index or primary key is used, the statement acquires a row lock; without an index, it escalates to a table lock.

Before testing, automatic commit must be disabled by executing SET @@autocommit=0; , where 0 indicates manual commit.

Table creation SQL used for the experiments:

//id is primary key
//name is 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;

Example 1: A transaction selects a row by primary key id and holds the lock, while a second transaction attempts to update the same row and is blocked, demonstrating a row lock.

Example 2: A second transaction updates a different row (id=2). The lock behavior is observed similarly, confirming row-level locking when the primary key is used.

Example 3 (Indexed field): The age column has a unique index. When queries use this indexed column, the lock remains at the row level.

Example 4 (Non‑indexed field): Using the ordinary column code (no index) for the query causes the SELECT ... FOR UPDATE to lock the entire table, as demonstrated by the blocked updates.

Result: When the query condition uses an indexed column or primary key, SELECT ... FOR UPDATE acquires a row lock; otherwise, it locks the whole table.