When Does SELECT … FOR UPDATE Lock Rows vs. Tables in MySQL?

In MySQL a normal SELECT statement does not acquire any lock, but SELECT ... FOR UPDATE adds a pessimistic lock to the rows it reads.

The lock type depends on whether the query uses an indexed column or the primary key: if an index or primary key is used, a row‑level lock is taken; otherwise a table‑level lock is applied.

To test this, create a table with a primary key and an indexed column:

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

Disable automatic commit so that the lock is held until you explicitly commit: set @@autocommit=0; (0 = manual commit, 1 = automatic commit).

Verification

Example 1: Query by primary‑key id in one transaction, then start another transaction that tries to update the same row. The second transaction blocks because the row is locked.

Image 1 shows the first transaction holding the lock, Image 2 shows the blocked second transaction, and Image 3 shows the timeout error.

Example 2: Open a transaction that updates a row with id=2. The lock behavior follows the same rule as Example 1.

Example 3 (Index): The table has an index on age. Queries that filter by age use the index and therefore acquire row locks.

Example 4: Use a non‑indexed column code in the WHERE clause. Without an index, the statement locks the whole table.

Result

If the query condition uses an index or primary key, SELECT ... FOR UPDATE acquires a row lock.

If the query condition uses a non‑indexed column, the same statement acquires a table lock.

Thanks for reading, hope this helps! (Source: blog.csdn.net/qq_42956376/article/details/109544539)