Understanding SELECT … FOR UPDATE: Table, Row, and Gap Locks in MySQL

Environment Preparation

Transaction isolation level is set to REPEATABLE READ and MySQL version is 5.7.26. All indexes are integer for simplicity.

CREATE TABLE user (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  user_no int(11) NOT NULL COMMENT '用户编号',
  user_name varchar(16) DEFAULT NULL COMMENT '用户名',
  age int(3) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (id),
  UNIQUE KEY un_idx_user_no (user_no),
  KEY idx_age (age)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

Insert sample rows:

insert into user values(1, 10, '楼仔', 18);
insert into user values(4, 15, '二哥', 28);
insert into user values(8, 20, '一灰', 38);

Common commands used during the tests:

start transaction;   -- start a transaction
commit;               -- commit
rollback;             -- rollback
select @@transaction_isolation;  -- view isolation level
select @@version;                -- view MySQL version
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;  -- view locks

Scenario Classification

Primary Key (value exists)

select * from user where id = 1 for update;

Attempt to update the same row:

update user set user_name = "楼仔小弟" where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Lock information shows an exclusive row lock:

lock_mode = X (exclusive)

lock_type = RECORD (row‑level)

Conclusion: primary‑key query with existing value → row lock.

Primary Key (value missing)

select * from user where id = 2 for update;

Attempt to insert a row with that primary key:

insert into user values(3, 14, '楼仔小弟', 28);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Lock information shows a gap lock on the interval (1,4) of the id column:

lock_mode = X (exclusive) + Gap

lock_type = RECORD

Conclusion: primary‑key query with missing value → gap lock.

Unique Index (value exists)

select * from user where user_no = 10 for update;

Attempt to update the same row:

update user set user_name = "楼仔小弟" where user_no = 10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Conclusion: unique‑index query with existing value → row lock.

Unique Index (value missing)

select * from user where user_no = 11 for update;

Attempt to insert a row with that unique key:

insert into user values(3, 14, '楼仔小弟', 28);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Gap lock covers the interval (1,4) of the user_no column:

Conclusion: unique‑index query with missing value → gap lock.

Normal Index (value exists)

select * from user where age = 18 for update;

Attempt to update rows with that age:

update user set user_name = "楼仔小弟" where age = 18;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Insert with the same age also hits the lock; the locked interval is [18, 28) on the age column.

Conclusion: normal‑index query with existing value → gap lock.

Normal Index (value missing)

select * from user where age = 19 for update;

Attempt to insert a row with that age:

insert into user values(3, 14, '楼仔小弟', 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The lock covers the interval (18, 28) on the age column.

Conclusion: normal‑index query with missing value → gap lock.

Index Range Query

select * from user where id > 1 for update;

Attempt to insert a new row:

insert into user values(3, 14, '楼仔小弟', 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The lock spans three intervals on id: (1,4], (4,8], and (8,∞).

Conclusion: range query on any index → gap lock.

No Index

select * from user where user_name = "楼仔" for update;

Attempt to insert a row with that name:

insert into user values(3, 14, '楼仔小弟', 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Although the lock appears as a row lock, it actually locks the whole table.

Conclusion: query without an index → table lock.

Locking Rules

Lock Types

Record Lock – row lock.

Gap Lock – locks a range without the record itself.

Next‑Key Lock – combination of row lock and gap lock (half‑open interval, e.g., (1,5]).

Principles (from MySQL expert Lin Xiaobin)

Principle 1: The basic locking unit is a next‑key lock, which is a half‑open interval.

Principle 2: Only objects accessed during the search are locked.

Optimizations

Optimization 1: For an equality query on a unique index, the next‑key lock degrades to a row lock.

Optimization 2: For an equality query that traverses rightward and the last examined value does not satisfy the equality, the next‑key lock degrades to a gap lock.

Analysis Using the Principles and Optimizations

Applying Principle 1 and Optimization 1 explains why primary‑key or unique‑index queries that find a row result in row locks. Optimization 2 explains why the same queries that do not find a row result in gap locks. The same reasoning applies to normal indexes and range queries, leading to gap locks. When no index is used, the whole table is locked.

Final Summary (REPEATABLE READ isolation)

Primary key or unique index with existing value → row lock.

Primary key or unique index with missing value → gap lock.

Normal index → gap lock.

Range query on any index → gap lock.

No index → table lock.