# Excessive Locks

**URL:** <https://forums.percona.com/t/excessive-locks/29588>\
**Category:** MySQL & MariaDB\
**Tags:** mysql\
**Created:** [April 7, 2024, 11:39am UTC](https://forums.percona.com/t/excessive-locks/29588 "2024-04-07T11:39:13Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Andre1976](https://avatars.discourse-cdn.com/v4/letter/a/a587f6/32.png) [@Andre1976](https://forums.percona.com/u/Andre1976)\
**Post date:** [April 7, 2024, 11:39am UTC](https://forums.percona.com/t/excessive-locks/29588/1 "2024-04-07T11:39:13Z")

</div>

Hello Mysql Gurus

Good morning, I have an IOT System with massive I/O operations on the database and I must to purge ( 400 millions rows ) in the one of these tables with 1,2 billions rows with heavy use .

I have tried a batch process but it is not working properly making the system with a lot of locks . So , I come up with you requesting your suggestion to minimize the locks operations because I am purging distinct ids however the mysql still applying locks as you can see on the csv attached .

Moreover , I can’t stop the system to try an approach of create a new table without these rows, drop the old one and rename it . My mysql is 5.7 version because I can’t upgrade per code system incompatibility in codes in Python codes , Django and so on ..

Master Server 32 cores Cpu / 158 gb  
2 Slaves Server 32 cores Cpu / 158 gb

CREATE TABLE `a` (  
`id` bigint(20) NOT NULL AUTO\_INCREMENT,  
`start_date` datetime NOT NULL,  
`end_date` datetime DEFAULT NULL,  
`total_duration` int(10) unsigned NOT NULL,  
`total_connections` int(10) unsigned NOT NULL,  
`type` smallint(6) NOT NULL,  
`aggregation_period` smallint(6) NOT NULL,  
`sim_card_id` int(11) NOT NULL,  
PRIMARY KEY (`id`),  
KEY `a_cd820fa0` (`sim_card_id`),  
KEY `a_all_keys` (`sim_card_id`,`type`,`aggregation_period`,`start_date`,`end_date`),  
KEY `a_start_date_aggregation_period` (`start_date`,`aggregation_period`),  
CONSTRAINT `a_sim_card_id_15f9e2a7abb17d93_fk_bid` FOREIGN KEY (`b_ID `) REFERENCES `B` (`id`)  
) ENGINE=InnoDB AUTO\_INCREMENT=8134496596 DEFAULT CHARSET=latin1

Purge routine each 30 seg

SET session TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  
set foreign\_key\_checks=0 ;

DELETE a FROM a INNER JOIN ( SELECT id FROM a\_temp order by id desc LIMIT 100000 ) b On a.id =b.id

Ps : In table a\_tem I have around 300 milions id to be deleled

set foreign\_key\_checks=1 ;

####################################################################################

### This statement is triggered by system under some rules , I dont have control over this operation that has being impacted

#################################################################################

SET session TRANSACTION ISOLATION LEVEL READ COMMITTED;  
UPDATE a AS a  
INNER JOIN  
(SELECT  
id  
FROM  
a  
WHERE  
(`a`.`id` = 618844  
AND `a`.`type` = 5  
AND `a`.`start_date` \<= ‘2024-04-07 04:44:51’  
AND `a`.`end_date` \>= ‘2024-04-07 04:44:51’)) b ON a.id = b.id  
SET  
total\_connections = a.total\_connections + 1,  
total\_duration = a.total\_duration + 1

 ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/1/1c39dadf8a1254ffec0a65218debcfa31feb7928.png)

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [April 7, 2024, 1:07pm UTC](https://forums.percona.com/t/excessive-locks/29588/2 "2024-04-07T13:07:57Z")

</div>

Hello @Andre1976,  
I would drop the trigger, which appears to just be tracking some database stats, and then add it back after the purging is done.

> [@Andre1976](#):
>
> I have tried a batch process

This is the correct way. The larger the delete transaction, the longer the locks are held, the more undo pages are created/purged, etc. Smaller batches are better.

> [@Andre1976](#):
>
> 2 Slaves Server 32 cores Cpu / 158 gb

Since you have two replicas, you can drop the indexes and constraints on table `a` on 1 replica, do all the deletes, then promote that replica to be the new source. From that new source, recreate 2 new replicas. (This process is the _traditional_ way of making alter’s to big tables, eg “do it on the replica, and then promote the replica”)

---

<div class="post-metadata">

**Author:** ![Andre1976](https://avatars.discourse-cdn.com/v4/letter/a/a587f6/32.png) [@Andre1976](https://forums.percona.com/u/Andre1976)\
**Post date:** [April 7, 2024, 4:38pm UTC](https://forums.percona.com/t/excessive-locks/29588/3 "2024-04-07T16:38:04Z")

</div>

Hello @matthewb

Thanks for your support but I Can’t . The update rule is to summarize ( increase ) the Gb Internet plan used per our customers and I have already request to change this rule but they wont to change for while .

I have already deleted 25 million rows , I am enjoyng Sunday to try to reduce it but thank you so much for your feedback again !

Regards  
Andre Rodrigues