Mysql Lock with different isolation level

0. Preparation

CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  UNIQUE KEY `test_id_IDX` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

1. Read Committed with Unique Key

set session transaction isolation level read committed;
select * from test where id = 20 for update ;

 

select ENGINE, ENGINE_TRANSACTION_ID, THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;

We can see, in READ COMMITTED level, only the record with id = 20 is locked by RECORD LOCK. Others are free. (BTW, actually, in this case, there is an lock degradation happened, when retrived column exists Unique Key.)

2. Repeatable Read with Unique Key

set session transaction isolation level repeatable read;
select * from test where id = 20 for update ;

 In REPEATABLE READ level, a NEXT-KEY LOCK locked indexes between id (20, 30]. Any insertion statement like "INSERT INTO test.test(id, name) VALUES(21, 'bbb')" will be blocked, but insert (30, 'bbb') will be success.