What Locks Does SELECT … FOR UPDATE Acquire Under Different Isolation Levels?

Introduction

The author explores the locking behavior of SELECT ... FOR UPDATE in MySQL 8.0+, showing how different isolation levels (RC and RR) and index types affect whether row locks, table locks, or gap locks are taken.

1. Environment Setup

Prepare the test database:

mysql> set global transaction isolation level read committed;
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+

mysql> set @@autocommit=0;  -- disable autocommit
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+

CREATE TABLE `user_info_tab` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `status` varchar(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_name` (`user_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570072 DEFAULT CHARSET=utf8mb3;

INSERT INTO user_info_tab(`user_name`,`age`,`city`,`status`) VALUES('杰伦',18,'深圳','1');
INSERT INTO user_info_tab(`user_name`,`age`,`city`,`status`) VALUES('奕迅',26,'湛江','0');
INSERT INTO user_info_tab(`user_name`,`age`,`city`,`status`) VALUES('俊杰',28,'广州','1');

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.31    |
+-----------+

2. RC Isolation Level (Read Committed)

2.1 Unique Index

Set isolation to RC and run a SELECT ... FOR UPDATE on the unique column user_name. The second transaction trying to update the same row blocks.

Lock analysis using SELECT * FROM performance_schema.data_locks\G shows three locks:

IX (intent‑exclusive) table‑level lock

X lock on the unique index row

X lock on the primary‑key row

The IX lock does not block other rows, so only the targeted row is locked.

2.2 Primary Key

Querying by primary key id also acquires an IX lock and a single X lock on the primary‑key row.

Lock view confirms two locks (IX + X).

2.3 Normal Index

After adding a normal index on city, a SELECT ... FOR UPDATE on that column acquires an IX lock, an X lock on the index row, and an X lock on the primary‑key row.

If the query adds an additional non‑matching condition (e.g., status='0') and matches no rows, only the IX lock is taken.

2.4 No Index

When the WHERE clause uses an unindexed column ( age), the statement still acquires an IX lock and an X lock on each matching row (via the clustered primary key). MySQL scans the whole table but releases locks on rows that do not satisfy the predicate.

3. RR Isolation Level (Repeatable Read)

3.1 Unique Index

Under RR, the same unique‑index query adds an additional gap lock. The lock set includes IX, X on the primary‑key row, and X on the unique‑index row.

3.2 Primary Key

Querying by primary key under RR yields the same two locks as RC (IX + X).

3.3 Normal Index

With a normal index, RR adds a gap lock in addition to IX and X locks, preventing phantom inserts.

3.4 No Index

When the WHERE clause uses an unindexed column, RR acquires an IX lock, X locks on every row, and an additional X lock on the “supremum pseudo‑record”, effectively locking the whole table.

The supremum pseudo‑record represents a virtual row greater than any real index value, so the next‑key lock on it locks the gap after the largest index entry, which behaves like a full‑table lock.

Conclusion

Different query conditions (unique index, primary key, normal index, no index) cause SELECT ... FOR UPDATE to acquire distinct combinations of IX, X, and gap locks under RC and RR isolation levels.

Locks can be inspected with SELECT * FROM performance_schema.data_locks\G after executing the target statement.

Understanding these lock patterns helps avoid deadlocks and design safer concurrent transactions.