Understanding InnoDB Locking: Repeatable Read vs. Read Committed Isolation Levels

Preparation

Create a test table t2 with an auto‑increment primary key and a secondary index idx_i1 , then insert several 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);

1. REPEATABLE‑READ

Set the transaction isolation level to REPEATABLE‑READ (if not already set) and verify the setting.

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

Start a transaction and execute a SELECT * FROM t2 WHERE i1 = 13 FOR SHARE query.

BEGIN;
SELECT * FROM t2 WHERE i1 = 13 FOR SHARE;

Query performance_schema.data_locks to see the locks that were taken.

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 result shows:

On secondary index idx_i1 , record (i1=13, id=3) holds a shared Next‑Key lock ( lock_mode = S ).

On the primary key, record (id=3) holds a shared record lock ( lock_mode = S,REC_NOT_GAP ).

On secondary index idx_i1 , record (i1=14, id=4) holds a shared gap lock ( lock_mode = S,GAP ) to prevent phantom inserts.

Explanation: Under REPEATABLE‑READ, InnoDB locks the matching secondary‑index record with a shared Next‑Key lock, then follows the primary key to lock the primary record with a shared record lock, and finally locks the gap before the next secondary‑index record to avoid phantom rows.

2. READ‑COMMITTED

Switch the isolation level to READ‑COMMITTED and verify.

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

Run the same SELECT … FOR SHARE statement inside a new transaction.

BEGIN;
SELECT * FROM t2 WHERE i1 = 13 FOR SHARE;

Inspect the locks again.

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 record locks on both the secondary index record (i1=13, id=3) and the primary key record (id=3) (both with lock_mode = S,REC_NOT_GAP ), but no gap lock on the next secondary‑index record because READ‑COMMITTED does not need to prevent phantom rows.

Explanation: In READ‑COMMITTED, InnoDB only needs to protect the rows that are actually read, so it acquires shared record locks on the matching secondary‑index row and the corresponding primary‑key row, without adding gap locks.

3. Summary

The article illustrates the different locking behavior of InnoDB under the two isolation levels: REPEATABLE‑READ adds shared Next‑Key and gap locks to guarantee repeatable reads, while READ‑COMMITTED adds only shared record locks because phantom protection is not required.