resolve lock wait timeout delete innodb table

Question

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?

Answer 1

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.