Why does innodb lock more records when range deletion-CodePudding

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

Session1	Session2	Locks
begin;	begin;
insert into ep(e_id, name, create_at) values(100, 'stt', '2022-12-04 00:00:00');
	insert into ep(e_id, name, create_at) values(100, 'ssd', '2022-12-04 00:00:00');
select * from ep\G; 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 id: 4 e_id: 100 name: stt create_at: 2022-12-04 00:00:00 update_at: 2023-01-01 05:41:47	select * from ep\G; 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 id: 5 e_id: 100 name: ssd create_at: 2022-12-04 00:00:00 update_at: 2023-01-01 05:44:46
delete from ep where e_id=100 and create_at <= '2022-12-03 00:00:00';		lock waiting

SHOW ENGINE INNODB STATUS OUTPUT

test case 2

concurrent transactions

Session1	Session2	Locks
begin;	begin;
insert into ep(e_id, name, create_at) values(100, 'stt', '2022-12-04 00:00:00');
	insert into ep(e_id, name, create_at) values(100, 'ssd', '2022-12-04 00:00:00');
select * from ep\G; 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 id: 4 e_id: 100 name: stt create_at: 2022-12-04 00:00:00 update_at: 2023-01-01 05:41:47	select * from ep\G; 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 id: 5 e_id: 100 name: ssd create_at: 2022-12-04 00:00:00 update_at: 2023-01-01 05:44:46
delete from ep where e_id=100 and create_at <= '2022-12-02 00:00:00';		no lock waiting

SHOW ENGINE INNODB STATUS OUTPUT

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.

CodePudding user response：

MySQL locks the rows it looks at during execution (and potentially frees them again).

In your case, based on the behaviour, for the second test case, MySQL uses the index on create_at to find the rows, for the first case, it doesn't.

So for your second delete, MySQL will not even look at ids 4 and 5 (because they are outside of the date range), and thus doesn't have to wait for locks on ids 4 and 5.

For your first delete, MySQL doesn't use the index, but either the primary key or the index on e_id, and thus has to wait for the lock.

Repeat the experiment without the index, and it might match your understanding (although you didn't actually specify what you did expect).

CodePudding user response：

Replace idx_e_id with

INDEX(e_id, created_at)

So that there will be a useful index for the DELETE.