Why does innodb lock more records when range deletion – Mysql

I am using mysql innodb database. I thought I had understood the locking mechanism of mysql. But I found an example, which conflicts with my understanding.See the example below (verified in version 5.7.32 with rc isolation level).

create table

Create Table: CREATE TABLE `ep` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '',
  `e_id` int(11) NOT NULL COMMENT '',
  `name` varchar(255) NOT NULL COMMENT '',
  `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
  `update_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '',
  PRIMARY KEY (`id`),
  KEY `idx_e_id` (`e_id`) USING BTREE,
  KEY `idx_create_at` (`create_at`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

initial data

insert into ep(e_id, name, create_at) values(100, 'shijie', '2022-12-01 00:00:00'),(100, 'jianfeng', '2022-12-02 00:00:00'),(100, 'syx', '2022-12-03 00:00:00');

Before the next case testing, there are 3 records in the ep table.

 id: 1
 e_id: 100
 name: shijie
 create_at: 2022-12-01 00:00:00
 update_at: 2023-01-01 05:31:13

 id: 2
 e_id: 100
 name: jianfeng
 create_at: 2022-12-02 00:00:00
 update_at: 2023-01-01 05:31:13

 id: 3
 e_id: 100
 name: syx
 create_at: 2022-12-03 00:00:00
 update_at: 2023-01-01 05:31:13

• test case 1

concurrent transactions

SHOW ENGINE INNODB STATUS OUTPUT

• test case 2

concurrent transactions

SHOW ENGINE INNODB STATUS OUTPUT

• expectation

In my understanding, when deleting according to e_id and create_at, the data will be locked in the following steps.

Step 1: find ids with e_id=100(get ids 1,2,3,4,5)

Step 2: find ids with create_at <= ‘2022-12-03 00:00:00”(get ids 1,2,3)

Step 3: get the intersection of the result ids of step 1 and step 2(get ids 1,2,3)

Step 4: lock on ids 1, 2 and 3

The order of steps 1 and 2 can be reversed.
So test case 2 is what I expected.

• question

In test case 1, when the deletion range of create_at is large, session1 tries to acquire the primary key lock of session2 inserting data(id=5). In fact, the data inserted in session2 is out of the deletion range of create_at in session1.

In test case 2, reduce the deletion range of create_at(2022-12-03 00:00:00 -> 2022-12-02 00:00:00), and session 1 will no longer acquire any locks of the data inserted by session 2(neither the primary key lock nor the secondary index lock).

In fact, when deleting according to the primary key index range, such as delete from ep where id <= id_num, there also has the problem of large-scale deletion to obtain more lock inserted by other concurrent sessions and small-scale deletion not to obtain more lock.

Can anyone explain the cause of this problem or provide related articles.