0

In an application I work with, DELETEs are rare, though they seldom happen. When they do, they remove few rows but from several related tables, like 8 to 10.

Most of the times, mainly when the application is under heavy usage, they end up in nothing deleted, though they delete data on which nobody is INSERTING or UPDATING, that's for sure - with a 99% of confidence - so they should go smooth as silk.

Last time it happened to me, I checked SHOW engine innodb status output, finding this:

------------
TRANSACTIONS
------------
[...]
---TRANSACTION 391233707, ACTIVE 54 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 16360 lock struct(s), heap size 1515728, 735693 row lock(s), undo log entries 4
MySQL thread id 357052, OS thread handle 140376376604416, query id 148538838 cloudsqlproxy~34.141.116.83 myuser preparing
DELETE FROM travel
        where mission_id in (
            select id
            from mission
            where work_day_id = '76e7e272-e97e-4149-89e7-c2f814f31625'
        )
Trx read view will not see trx with id >= 391233707, sees < 391233682
------- TRX HAS BEEN WAITING 48 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 337 page no 1927 n bits 104 index PRIMARY of table `app`.`mission` trx id 391233707 lock mode S locks rec but not gap waiting
Record lock, heap no 32 PHYSICAL RECORD: n_fields 81; compact format; info bits 0
 0: len 30; hex 36636565366666342d656263612d343435342d393265642d653333646432; asc 6cee6ff4-ebca-4454-92ed-e33dd2; (total 36 bytes);
 1: len 6; hex 00001741dc97; asc    A  ;;
 2: len 7; hex 3b0000038c26a6; asc ;    & ;;
 [...]
 79: SQL NULL;
 80: SQL NULL;

------------------
---TRANSACTION 391233682, ACTIVE 75 sec starting index read
mysql tables in use 10, locked 0
24 lock struct(s), heap size 3520, 30 row lock(s)
MySQL thread id 2352977, OS thread handle 140376366597888, query id 148539136 213.82.40.221 myuser Sending data
SELECT vcwofa.work_day_id AS vcwofa_work_day_id, vcwofa.work_day_activity_id AS vcwofa_work_day_activity_id, vcwofa.giornata AS vcwofa_giornata, vcwofa.risorsa_id AS vcwofa_risorsa_id, vcwofa.ordine_lavoro AS vcwofa_ordine_lavoro, vcwofa.reparto_tec AS vcwofa_reparto_tec, vcwofa.parent_numb_int AS vcwofa_parent_numb_int, vcwofa.giustificativo AS vcwofa_giustificativo, vcwofa.sub_account AS vcwofa_sub_account, vcwofa.indennita_flag AS vcwofa_indennita_flag, vcwofa.causal_type AS vcwofa_causal_type, vcwofa.breakfast AS vcwofa_breakfast, vcwofa.launch AS vcwofa_launch, vcwofa.reimbursement_bottom
Trx read view will not see trx with id >= 391233683, sees < 391233683
--------
FILE I/O
--------
[...]

That's not easy for me to understand what is exactly helding the lock that makes the DELETE wait (and I can't find enough information in the docs), but an EXPLAIN on the waiting query:

explain         DELETE FROM travel
    where mission_id in (
        select id
        from mission
        where work_day_id = '76e7e272-e97e-4149-89e7-c2f814f31625'
    )

Gives:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 DELETE travel ALL 215615 100.0 Using where
2 DEPENDENT SUBQUERY mission unique_subquery PRIMARY,fk_mission_work_day1_idx PRIMARY 137 func 1 5.0 Using where

that, with a travel table made like this:

CREATE TABLE `travel` (
  `id` varchar(45) NOT NULL COMMENT 'Definito tramite un UUID',
  `mission_id` varchar(45) NOT NULL,
  `day` datetime DEFAULT NULL,
  /* ... other columns ... */
  PRIMARY KEY (`id`),
  KEY `fk_travel_mission1_idx` (`mission_id`),
  CONSTRAINT `fk_travel_mission1` FOREIGN KEY (`mission_id`) REFERENCES `mission` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

and a mission:

CREATE TABLE `mission` (
  `id` varchar(45) NOT NULL COMMENT 'Definito tramite un UUID',
  `work_day_id` varchar(45) NOT NULL,
  `fdt` varchar(45) DEFAULT NULL,
  `day` datetime DEFAULT NULL,
  `cid` varchar(45) DEFAULT NULL,
  /* ... other columns ... */
  `document_manag_ref` varchar(50) DEFAULT NULL,
  `document_manag_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_mission_work_day1_idx` (`work_day_id`),
  KEY `mission_fdt_day_cid` (`fdt`,`day`,`cid`),
  KEY `mission_fdt_work_day_id` (`fdt`,`work_day_id`),
  KEY `index1` (`work_order_code`),
  KEY `index2` (`day`),
  CONSTRAINT `fk_mission_work_day1` FOREIGN KEY (`work_day_id`) REFERENCES `work_day` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

surprised me to find that type=ALL, a full table scan.

In fact, the SELECT instead yields:

explain SELECT * FROM travel
    where mission_id in (
        select id
        from mission
        where work_day_id = '76e7e272-e97e-4149-89e7-c2f814f31625'
    )

predictably yields:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE mission ref PRIMARY,fk_mission_work_day1_idx fk_mission_work_day1_idx 137 const 1 100.0 Using index
1 SIMPLE travel ref fk_travel_mission1_idx fk_travel_mission1_idx 137 app.mission.id 1 100.0

since all the values refer to indexes coulums.

Thus I tried to convert the WHERE ... IN in a JOIN:

EXPLAIN DELETE travel FROM travel JOIN mission ON travel.mission_id = mission.id 
        WHERE mission.id IN (
        select id
            from mission
            where work_day_id = '76e7e272-e97e-4149-89e7-c2f814f31625'
        )

obtaining:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE mission ref PRIMARY,fk_mission_work_day1_idx fk_mission_work_day1_idx 137 const 1 100.0 Using index
1 SIMPLE mission eq_ref PRIMARY PRIMARY 137 app.mission.id 1 100.0 Using index
1 DELETE travel ref fk_travel_mission1_idx fk_travel_mission1_idx 137 app.mission.id 1 100.0

Am I on the right path here? Is this the right way to handle this situation?

2
  • Please provide SHOW CREATE TABLE for each table involved in the issue.
    – Rick James
    Nov 12, 2021 at 0:45
  • @RickJames Done, thanks.
    – watery
    Nov 12, 2021 at 8:57

1 Answer 1

0
DELETE FROM travel
    where mission_id in (
        select id
        from mission
        where work_day_id = '76e7e272-e97e-4149-89e7-c2f814f31625'
    )

Change this to a multi-table DELETE and be sure to have an indexes on work_day_id and on mission_id.

2
  • Do you mean like the DELETE in my last EXPLAIN, where I've rewritten it with a JOIN? All the involved columns have mandatory indexes, since they're used in FOREIGN KEYs constraints.
    – watery
    Nov 12, 2021 at 8:57
  • @watery - Just a JOIN. IN (SELECT ...) is notoriously slow (in some situations). JOIN, with adequate indexes is, usually fast.
    – Rick James
    Nov 12, 2021 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.