Does SELECT FOR UPDATE Lock Rows or the Whole Table in MySQL?

Select queries normally do not acquire locks, but using SELECT ... FOR UPDATE adds a pessimistic lock.

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

To verify, disable autocommit ( set @@autocommit=0;) and run several test cases.

Test case 1

Transaction A selects a row by primary key id=1 and holds the lock; Transaction B attempts to update the same row and is blocked, showing a row lock.

Test case 2

Another transaction updates a different row (id=2) while the first transaction holds a lock, illustrating lock behavior.

Test case 3 (with index)

The table is created with a unique index on the age column; subsequent queries using this indexed column acquire row locks.

Test case 4 (no index)

Using a non‑indexed column code for the query results in a table lock.

Result

If the query condition uses an index or primary key, SELECT ... FOR UPDATE performs a row lock; otherwise it locks the whole table.