What Locks Does SELECT FOR UPDATE Use? 20 MySQL Scenarios Revealed

Background

Many articles disagree on whether SELECT FOR UPDATE acquires row locks or table locks, and the answer directly impacts system performance. The conclusions vary because they often ignore MySQL version and transaction isolation level. This article tests four major cases (two MySQL versions × two isolation levels) across 20 scenarios and provides a systematic methodology.

Environment Preparation

Table definition:

<code>CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_no` varchar(16) DEFAULT NULL COMMENT '用户编号',
  `user_name` varchar(16) DEFAULT NULL COMMENT '用户名',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `address` varchar(128) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_idx_user_no` (`user_no`),
  KEY `idx_user_name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;</code>

Initial data:

<code>insert into user values(null,'0001','user01',18,'北京');
insert into user values(null,'0002','user02',19,'上海');
insert into user values(null,'0003','user03',20,'广州');
insert into user values(null,'0004','user04',21,'深圳');
insert into user values(null,'0005','user05',22,'杭州');</code>

MySQL versions used:

<code>SELECT @@VERSION;   -- 5.7.22
SELECT @@VERSION;   -- 8.0.18</code>

Transaction isolation levels:

<code>SELECT @@TRANSACTION_ISOLATION;   -- REPEATABLE-READ</code>

Supported isolation levels:

READ_UNCOMMITTED

READ_COMMITTED (RC)

REPEATABLE_READ (RR)

SERIALIZABLE

Global and session isolation can be set with:

<code>SET GLOBAL transaction isolation level REPEATABLE READ;
SET SESSION transaction isolation level READ COMMITTED;</code>

Auto‑commit is disabled for the tests:

<code>SET @@AUTOCOMMIT=0;</code>

Scenario 1.1: V5.x‑RR‑Primary Key

Operation: SELECT * FROM user WHERE id = 1 FOR UPDATE then try to update the same row.

Result: the update is blocked, indicating a lock. Querying INFORMATION_SCHEMA.INNODB_LOCKS shows lock_mode = X , lock_type = RECORD , meaning a row‑level exclusive lock.

Updating a different row succeeds, confirming it is a row lock.

Conclusion : With a primary‑key condition, SELECT FOR UPDATE acquires a row‑level lock.

Scenario 1.2: V5.x‑RR‑Unique Index

Condition on the unique column user_no . The lock information is identical to the primary‑key case.

Conclusion : Unique‑index condition also results in a row‑level lock.

Scenario 1.3: V5.x‑RR‑Ordinary Index

Condition on user_name . The lock shows lock_mode = X and lock_type = RECORD plus a GAP lock, i.e., an exclusive gap lock.

Inserting a new row whose user_name matches the condition is blocked, demonstrating the gap lock.

Conclusion : Ordinary‑index condition yields a row lock together with an exclusive gap lock; inserts that satisfy the condition are blocked.

Scenario 1.4: V5.x‑RR‑No Index

Condition on address = '北京' (no index). The lock appears as a table‑level lock in the lock table, but lock_type is still RECORD.

Conclusion : Without an index, SELECT FOR UPDATE results in a table‑level lock under RR.

Scenario 1.5: V5.x‑RR‑Range Query

Condition id > 1 (range on indexed column). The lock shows a supremum pseudo‑record (next‑key lock) and a GAP lock.

Inserting a row whose generated id satisfies the range is blocked.

Conclusion : Range queries on indexed columns use gap/next‑key locks that block matching inserts.

Scenario 2.x: MySQL 8.0‑RR

All primary‑key, unique‑index and ordinary‑index cases behave similarly to 5.7‑RR, but lock inspection uses performance_schema.data_locks . The lock hierarchy includes an IX intention lock at the table level and an X lock (or X,REC_NOT_GAP) at the record level.

Ordinary‑index still adds a GAP lock; no‑index still yields a table lock.

Scenario 3.x: MySQL 5.7‑RC

Switching isolation to READ‑COMMITTED.

Primary‑key, unique‑index and ordinary‑index conditions all produce row‑level locks. The ordinary‑index case no longer has a GAP lock, so inserts with the same user_name succeed.

Without an index, SELECT FOR UPDATE still results in row‑level locks because MySQL scans the clustered primary key and locks each matching row, unlocking non‑matching rows during the scan.

Scenario 4.x: MySQL 8.0‑RC

Results mirror 5.7‑RC: primary‑key and unique‑index give row locks; ordinary‑index gives only row locks (no GAP); no‑index also gives row locks.

Range queries lock the matching rows but do not block inserts that fall into the range (no next‑key lock in this version/level).

Summary Table (textual)

MySQL 5.7‑RR: primary/unique → row lock; ordinary → row + gap lock; no index → table lock; range → row + gap (insert blocked).

MySQL 8.0‑RR: primary/unique → row lock; ordinary → row + gap lock; no index → table lock; range → row + gap (insert blocked).

MySQL 5.7‑RC: primary/unique/ordinary → row lock; ordinary → no gap; no index → row lock; range → row lock (insert blocked).

MySQL 8.0‑RC: primary/unique → row lock; ordinary → row lock (no gap); no index → row lock; range → row lock (insert not blocked).

Key Takeaways

When the query condition uses a primary key, unique index, or ordinary index, SELECT FOR UPDATE acquires row‑level locks.

Under REPEATABLE‑READ, an ordinary‑index condition adds an exclusive gap lock, causing inserts that match the condition to block.

Under REPEATABLE‑READ, a condition without any index results in a table‑level lock.

Under READ‑COMMITTED, a condition without an index results in row‑level locks, not a table lock.

Range queries on indexed columns generally use next‑key or gap locks; only MySQL 8.0‑RC does not block inserts for such ranges.

These conclusions help developers predict locking behavior and avoid performance pitfalls when using SELECT FOR UPDATE in MySQL.