Analysis of Lock Behavior under REPEATABLE-READ and READ-COMMITTED Isolation Levels

Author Cao Shengchun, a technical expert, presents a tutorial based on MySQL 8.0.32 InnoDB source code, demonstrating how the REPEATABLE‑READ and READ‑COMMITTED isolation levels affect locking behavior for simple SELECT … FOR SHARE statements.

Preparation

Create a test table t4 and insert sample rows:

CREATE TABLE `t4` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `i1` int DEFAULT '0',
  `i2` int DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uniq_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `t4` (`id`,`i1`,`i2`) VALUES
(1,11,21),(2,12,22),(3,13,23),(4,14,24),(5,15,25),(6,16,26);

1. REPEATABLE‑READ

Set the isolation level to REPEATABLE‑READ, start a transaction, and execute a SELECT … FOR SHARE on a row with primary‑key id = 10. The performance_schema.data_locks view shows a shared record lock (S,REC_NOT_GAP) on the primary‑key record.

SET transaction_isolation = 'REPEATABLE-READ';
SHOW VARIABLES LIKE 'transaction_isolation';
BEGIN;
SELECT * FROM t1 WHERE id = 10 FOR SHARE;
SELECT engine_transaction_id, object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE object_name = 't1' AND lock_type = 'RECORD'\G

Explanation: under REPEATABLE‑READ, a SELECT that touches a primary‑key equality condition acquires a shared Next‑Key lock, but because the key is unique, MySQL can replace it with a plain shared record lock, which is sufficient to guarantee repeatable reads.

2. READ‑COMMITTED

Switch the isolation level to READ‑COMMITTED and repeat the same SELECT … FOR SHARE. The lock information is identical—a shared record lock on the primary‑key row—because READ‑COMMITTED always uses a plain shared record lock for such queries.

SET transaction_isolation = 'READ-COMMITTED';
SHOW VARIABLES LIKE 'transaction_isolation';
BEGIN;
SELECT * FROM t1 WHERE id = 10 FOR SHARE;
SELECT engine_transaction_id, object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE object_name = 't1' AND lock_type = 'RECORD'\G

Explanation: READ‑COMMITTED does not create Next‑Key locks for simple primary‑key lookups; it directly places a shared record lock, which matches the observed output.

Summary

Both REPEATABLE‑READ and READ‑COMMITTED lock the same record with a shared record lock when the query uses a unique primary‑key condition, but the underlying locking logic differs: REPEATABLE‑READ would normally use a Next‑Key lock, while READ‑COMMITTED simplifies to a plain record lock. Understanding this distinction helps developers predict lock contention in InnoDB.