Understanding SELECT ... FOR UPDATE Locks in MySQL

In MySQL, SELECT ... FOR UPDATE is a pessimistic locking mechanism that can lock a single row, multiple rows, an entire table, or no rows at all, depending on the conditions used in the WHERE clause.

Why Use Row Locks?

When multiple transactions try to update the same account balance concurrently, without proper locking the final amount can be incorrect. Row locks ensure that only one transaction modifies a specific row at a time, preventing lost updates.

1. Primary Key

If the WHERE clause targets the primary key, a row lock is acquired. Example:

BEGIN;
SELECT * FROM user WHERE id=1 FOR UPDATE;
UPDATE user SET age=22 WHERE id=1;

Another transaction attempting to update the same row will wait until the first transaction commits.

2. Unique Index

Using a unique index (e.g., code='101' ) also results in a row lock, behaving the same as the primary‑key case.

BEGIN;
SELECT * FROM user WHERE code='101' FOR UPDATE;
UPDATE user SET age=22 WHERE code='101';

3. Normal Index

When a normal (non‑unique) index is used, MySQL still acquires a row lock for the matching rows.

BEGIN;
SELECT * FROM user WHERE name='周星驰' FOR UPDATE;
UPDATE user SET age=22 WHERE name='周星驰';

4. Primary‑Key Range

Queries that specify a range of primary‑key values (e.g., id IN (1,2) ) lock each row in the range individually.

BEGIN;
SELECT * FROM user WHERE id IN (1,2) FOR UPDATE;
UPDATE user SET age=22 WHERE id IN (1,2);

5. Non‑Indexed Field

If the WHERE clause uses a column without an index (e.g., age=22 ), MySQL escalates to a table lock, causing all transactions on that table to wait.

BEGIN;
SELECT * FROM user WHERE age=22 FOR UPDATE;
UPDATE user SET age=22 WHERE age=22;

6. Empty Result Set

When the query matches no rows (e.g., id=66 that does not exist), no lock is taken, and subsequent transactions proceed without waiting.

BEGIN;
SELECT * FROM user WHERE id=66 FOR UPDATE;
UPDATE user SET age=22 WHERE id=66;

Summary

Primary‑key fields → row lock.

Unique‑index fields → row lock.

Normal‑index fields → row lock.

Primary‑key range → multiple row locks.

Non‑indexed fields → table lock.

Empty result set → no lock.

If a transaction holds a row lock and does not commit, any other transaction trying to modify the same row will wait until the lock is released or times out. If a table lock is held, all operations on that table will be blocked.