SELECT FOR UPDATE: When MySQL Locks Rows vs. Tables – 20 Tested Scenarios

Background

Many articles disagree on whether SELECT FOR UPDATE creates a row‑level lock or a table‑level lock, and the answer directly impacts performance. This summary consolidates experiments covering 20 scenarios across two MySQL major versions (5.7.x and 8.0.x) and two common isolation levels (REPEATABLE READ and READ COMMITTED).

Environment preparation

A simple user table is created and populated with five rows:

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;

Initial data:

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,'杭州');

Isolation level can be changed with:

SET GLOBAL transaction_isolation = REPEATABLE READ;
SET GLOBAL transaction_isolation = READ COMMITTED;
SET SESSION transaction_isolation = REPEATABLE READ;
SET SESSION transaction_isolation = READ COMMITTED;
SET @@autocommit = 0;   -- disable autocommit
COMMIT;                 -- finish a transaction

Test matrix and conclusions

1. REPEATABLE READ (RR) – MySQL 5.7

Primary key : row‑level exclusive lock (X, RECORD).

Unique index : same row‑level exclusive lock.

Normal index : row‑level lock plus a GAP lock; inserts that match the indexed value are blocked.

No index : a table‑level lock is taken.

Range query (indexed) : next‑key (gap) lock; inserts within the range are blocked.

2. REPEATABLE READ (RR) – MySQL 8.0

Primary key / Unique index : row‑level exclusive lock (X, REC_NOT_GAP).

Normal index : row‑level lock plus an X,GAP gap lock; range inserts are blocked.

No index : table‑level lock (multiple X locks, one per row).

Range query : same as 5.7 – gap lock blocks inserts.

3. READ COMMITTED (RC) – MySQL 5.7

Primary key / Unique index : row‑level lock.

Normal index : row‑level lock only (no gap lock).

No index : row‑level lock (InnoDB scans the clustered primary‑key index and releases locks on rows that don’t satisfy the condition).

Range query : row‑level lock; both updates and inserts that fall inside the range are blocked.

4. READ COMMITTED (RC) – MySQL 8.0

Primary key / Unique index : row‑level lock (X, REC_NOT_GAP).

Normal index : row‑level lock only (gap lock disappears compared with RR).

No index : row‑level lock.

Range query : row‑level lock; inserts are not blocked because the next‑key lock is absent.

Key takeaways

For primary‑key, unique‑index, and normal‑index conditions, MySQL always uses row‑level locks regardless of version or isolation level.

Under REPEATABLE READ, a normal index adds a GAP lock that blocks inserts matching the condition.

When no index is used, REPEATABLE READ falls back to a table‑level lock, while READ COMMITTED uses row‑level locks.

Range queries acquire next‑key (gap) locks in most cases; only MySQL 8.0 RC omits the gap lock, allowing inserts.

The lock behavior depends on both the isolation level and the presence/type of indexes, so simplistic rules such as “indexed → row lock, non‑indexed → table lock” are inaccurate.