Analyzing InnoDB Locking Behavior under REPEATABLE‑READ and READ‑COMMITTED Isolation Levels

The article, based on MySQL 8.0.32 source code with the InnoDB storage engine, walks through a practical example that illustrates lock behavior under different transaction isolation levels.

1. Preparation

First a test table t2 is created and populated with sample rows.

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

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

2. REPEATABLE‑READ

The session isolation level is set to REPEATABLE-READ (if not already set) and verified.

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

A SELECT ... FOR SHARE query is executed on the range i1 >= 12 AND i1 < 14 :

BEGIN;
SELECT * FROM t2 WHERE i1 >= 12 AND i1 < 14 FOR SHARE;

Lock information is retrieved from performance_schema.data_locks :

SELECT engine_transaction_id, object_name, index_name,
       lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE object_name = 't2' AND lock_type = 'RECORD'\G

The output shows shared Next‑Key locks on the secondary index idx_i1 for rows (12,2), (13,3) and (14,4), and shared record locks on the primary key for rows 2 and 3. The article explains that InnoDB applies the default Next‑Key lock for non‑unique secondary indexes, even when the gap before a matching row could be omitted.

3. READ‑COMMITTED

The isolation level is switched to READ-COMMITTED and verified.

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

The same SELECT ... FOR SHARE statement is run.

BEGIN;
SELECT * FROM t2 WHERE i1 >= 12 AND i1 < 14 FOR SHARE;

Lock information is queried again, revealing shared record locks (S,REC_NOT_GAP) on the secondary index rows (12,2) and (13,3) and on the primary key rows 2 and 3. The article notes that the lock on row (14,4) is released because the row does not satisfy the WHERE condition after being read.

4. Summary

Under READ‑COMMITTED the final lock snapshot may not reflect intermediate lock actions; InnoDB may acquire locks during index scans and release them later when rows are found to be outside the query range. The article highlights that non‑unique secondary indexes always receive Next‑Key locks in REPEATABLE‑READ, which can enlarge the lock footprint compared to the minimal required locking.