MySQL InnoDB Lock Behavior under Repeatable Read and Read Committed Isolation Levels

1. Preparation

Create a test table and insert sample rows.

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

INSERT INTO `t1` (`id`,`i1`) VALUES
(10,101), (20,201), (30,301), (40,401);

2. Repeatable Read

Set the transaction isolation level to REPEATABLE‑READ, execute a SELECT with an index hint and FOR SHARE , then query performance_schema.data_locks to observe the locks.

SET transaction_isolation='REPEATABLE-READ';
-- confirm setting
SHOW VARIABLES LIKE 'transaction_isolation';

BEGIN;
SELECT * FROM t1 IGNORE INDEX(idx_i1)
WHERE id>=10 AND id<30 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

The result shows:

Record lock on id=10 with mode S,REC_NOT_GAP (shared ordinary record lock).

Shared next‑key lock on id=20 with mode S .

Gap lock on id=30 with mode S,GAP .

Because the query uses the primary‑key index for a range scan, InnoDB does not acquire a shared next‑key lock for id=10 and id=30 beyond what the statement needs.

3. Read Committed

Switch the isolation level to READ‑COMMITTED, repeat the same SELECT, and inspect the locks.

SET transaction_isolation='READ-COMMITTED';
-- confirm setting
SHOW VARIABLES LIKE 'transaction_isolation';

BEGIN;
SELECT * FROM t1 IGNORE INDEX(idx_i1)
WHERE id>=10 AND id<30 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

Under READ‑COMMITTED, only id=10 and id=20 retain shared record locks; the lock on id=30 is released because the row does not satisfy the WHERE clause after being read.

4. Summary

In REPEATABLE‑READ, locks acquired by a SELECT … FOR SHARE are held until the transaction commits or rolls back. In READ‑COMMITTED, InnoDB releases locks on rows that no longer match the query condition, which can reduce lock contention but may affect dead‑lock detection.