When Does SELECT FOR UPDATE Use Row Locks vs Table Locks? 20 MySQL Scenarios Tested

Background

Whether SELECT ... FOR UPDATE acquires a row‑level lock or a table‑level lock depends on the MySQL version and the transaction isolation level. Conflicting statements in the literature arise from testing different versions (5.7 vs 8.0) and isolation levels (REPEATABLE READ vs READ COMMITTED).

Environment preparation

Create a simple user table and insert 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;

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

Set the desired isolation level and disable autocommit before each test:

SET GLOBAL transaction_isolation = REPEATABLE READ;   -- or READ COMMITTED
SET SESSION transaction_isolation = REPEATABLE READ;  -- or READ COMMITTED
SET @@autocommit = 0;

Test methodology

In session 1, run SELECT … FOR UPDATE with a specific condition.

In session 2, try to update the same row (or insert a row that matches the condition).

Observe whether the second statement blocks – a blocked statement means the lock was acquired.

Query lock information:

MySQL 5.7: SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; MySQL 8.0: SELECT * FROM performance_schema.data_locks; Optionally test updates/inserts on other rows to detect gap locks.

1. Primary‑key condition ( WHERE id = 1 )

Both MySQL 5.7 and 8.0 acquire an exclusive row lock (lock_mode X , lock_type RECORD ). Updates on other rows succeed, confirming row‑level locking.

SELECT * FROM user WHERE id = 1 FOR UPDATE;

2. Unique‑index condition ( WHERE user_no = '0001' )

Lock behavior is identical to the primary‑key case: a single row‑level exclusive lock.

SELECT * FROM user WHERE user_no = '0001' FOR UPDATE;

3. Ordinary‑index condition ( WHERE user_name = 'user01' )

REPEATABLE READ : MySQL adds a gap lock in addition to the row lock (lock_mode X,GAP ). The gap lock blocks inserts whose user_name would fall into the locked range.

READ COMMITTED : The gap lock disappears; only the row is locked.

SELECT * FROM user WHERE user_name = 'user01' FOR UPDATE;

4. No‑index condition ( WHERE address = '北京' )

REPEATABLE READ : MySQL escalates to a table‑level lock – multiple X locks covering the whole table are observed.

READ COMMITTED : The lock degrades to row‑level locks on the matching rows only.

SELECT * FROM user WHERE address = '北京' FOR UPDATE;

5. Range query with an index ( WHERE id > 1 )

MySQL uses next‑key (gap) locks. Under REPEATABLE READ, inserts that generate an id satisfying the range are blocked. Under READ COMMITTED the gap lock may be omitted (MySQL 8.0 RC does not block such inserts).

SELECT * FROM user WHERE id > 1 FOR UPDATE;

Observed lock types

Row lock : lock_mode = X, lock_type = RECORD.

Gap lock : lock_mode = X,GAP – blocks inserts that would fall into the gap.

Next‑key lock (pseudo‑record “supremum”): combination of row and gap lock used to prevent phantom rows.

Table lock (intent‑exclusive IX + many X on records) : appears when the query has no usable index under REPEATABLE READ.

REC_NOT_GAP (MySQL 8.0): row‑level lock without the gap component.

Conclusions

For primary‑key, unique‑index, and ordinary‑index conditions, SELECT … FOR UPDATE always acquires a row‑level exclusive lock.

With REPEATABLE READ, an ordinary‑index condition also adds a gap lock, causing inserts that satisfy the condition to block.

If the query has no usable index, REPEATABLE READ escalates to a table‑level lock; READ COMMITTED keeps row‑level locks.

Range queries that use an index lock the specified range. In most cases inserts into that range are blocked, except MySQL 8.0 RC where inserts are allowed.

Key takeaways

Lock type is determined by both index availability and isolation level.

REPEATABLE READ may introduce gap locks (or full table locks when no index is usable).

READ COMMITTED generally yields row‑level locks even without an index.

Understanding these nuances helps avoid unexpected blocking and performance degradation in InnoDB.