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

In MySQL, a normal SELECT query does not acquire locks, but appending FOR UPDATE turns it into a pessimistic lock.

The lock type depends on whether the query condition uses an indexed column or primary key: with an index it locks the specific rows, without an index it locks the entire table.

Verification

Table creation SQL used for testing:

//id为主键  
//name 为唯一索引
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

To test locking behavior, disable automatic commit with SET @@autocommit=0; (0 = manual commit, 1 = automatic).

Example Scenarios

Example 1

Query by primary key id=1 in one transaction, then start another transaction to update the same row; the second transaction is blocked, demonstrating a row lock.

Example 2

Update a different row (e.g., id=2 ) in a separate transaction; if the update succeeds, it confirms row-level locking, otherwise a table lock occurs.

Example 3 (Index)

The age column has a unique index; queries using this indexed column acquire row locks.

Example 4 (Non‑indexed field)

When using the non‑indexed code field, the SELECT ... FOR UPDATE statement locks the whole table.

Result

If the query condition utilizes an index or primary key, SELECT ... FOR UPDATE acquires a row lock; otherwise, it locks the entire table.

Feel free to discuss and share your viewpoints; you can also contact the author for further questions.