Re: MySQL 5.7 : InnoDB. 2 Deletes query using index but... too many rows locked and cause deadlock
Many thanks for the help Olivier !
I'll try the EXPLAIN FOR CONNECTION during the connection, I quite sure I can catch it.
Further info before answering your questions.
1=> in the meantime, I've create the combined index (unique) and now, this new index "id_id_env_case" is used in the explain (done just now, not during the real delete). So maybe it will improve
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'DELETE', 'cid_operation', NULL, 'range', 'PRIMARY,id_id_env_case,FK_CID_OPERATION_ENV_CASE', 'id_id_env_case', '9', 'const,const', '100', '100.00', 'Using where'
2=> I found that the record causing the issue is not in the scope of my 2 deletes :
Transac 1 WHERE (id_env_case = 4448) AND (id BETWEEN 15006449 AND 15007342)
Transac 2 WHERE (o.id_env_case = 4446) AND (id_cid_operation BETWEEN 14479175 AND 14479961)
And converting the hex of the row locked, I found that it was the id 13933335, which is linked to the id_env 4447... is it a next-key row phenomena ??
To answer your questions :
SHOW CREATE TABLE cid_operation\G
*************************** 1. row ***************************
Table: cid_operation
Create Table: CREATE TABLE `cid_operation` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
`ID_ENV_CASE` int(10) unsigned DEFAULT NULL COMMENT 'Link with the case.',
`ID_CID_AIRCRAFT` int(10) unsigned NOT NULL COMMENT 'Link with the aircraft.',
`ID_CID_AIRPORT_DEPARTURE` int(10) unsigned DEFAULT NULL COMMENT 'Link with the airport departure.',
`ID_CID_AIRPORT_DESTINATION` int(10) unsigned DEFAULT NULL COMMENT 'Link with the airport destination.',
`ID_CID_RUNWAY_DEPARTURE` int(10) unsigned DEFAULT NULL COMMENT 'Link with the runway departure.',
`ID_CID_RUNWAY_DESTINATION` int(10) unsigned DEFAULT NULL COMMENT 'Link with the runway destination.',
`FLIGHT_ID` varchar(12) COLLATE utf8_bin DEFAULT NULL COMMENT 'The flight ID',
`OP_TYPE` varchar(1) COLLATE utf8_bin DEFAULT NULL COMMENT 'A/D',
`TRACK_CODE` varchar(12) COLLATE utf8_bin DEFAULT NULL COMMENT 'Id of a 2D track.',
`SUBTRACK_CODE` int(11) DEFAULT NULL COMMENT 'Id of a 2D sub track.',
`PROFILE` varchar(12) COLLATE utf8_bin DEFAULT NULL COMMENT 'Id of a vertical profile.',
`INFLIGHT_PROFILE` varchar(30) COLLATE utf8_bin DEFAULT NULL,
`CRUISE_ALT` double DEFAULT NULL,
`HEADWIND` double DEFAULT NULL,
`NUM_OPS_DAY` double unsigned DEFAULT '1' COMMENT 'This is the coefficient to group an aerial move into an operation (default value is 1). Operation = coef x move.',
`NUM_OPS_EVENING` double DEFAULT NULL COMMENT 'This is the coefficient to group an aerial move into an operation (default value is 1). Operation = coef x move.',
`NUM_OPS_NIGHT` double DEFAULT NULL COMMENT 'This is the coefficient to group an aerial move into an operation (default value is 1). Operation = coef x move.',
`STAGE_LENGTH` varchar(1) COLLATE utf8_bin DEFAULT NULL COMMENT 'Stage length is a number that defines the initial weight of an aircraft (integer between 1 to 9).',
`DATE_DEPARTURE` date DEFAULT NULL COMMENT 'Time at the end of navigation. hh:mm:ss',
`TIME_DEPARTURE` time DEFAULT NULL COMMENT 'Time of the start of the navigation. hh:mm:ss',
`DATE_DESTINATION` date DEFAULT NULL COMMENT 'Date at the end of navigation. DD-MM-YYYY',
`TIME_DESTINATION` time DEFAULT NULL,
`CREATED_AT` datetime DEFAULT NULL COMMENT 'Creation date.',
`CREATED_BY` int(11) DEFAULT NULL COMMENT 'Creation author.',
`UPDATED_AT` datetime DEFAULT NULL COMMENT 'Last Update date.',
`UPDATED_BY` int(11) DEFAULT NULL COMMENT 'Date of the last update.',
`DEP_TRACK_CODE` varchar(12) COLLATE utf8_bin DEFAULT NULL,
`DES_TRACK_CODE` varchar(12) COLLATE utf8_bin DEFAULT NULL,
`DEP_SUBTRACK_CODE` int(11) DEFAULT NULL,
`DES_SUBTRACK_CODE` int(11) DEFAULT NULL,
`DEP_PROFILE` varchar(12) COLLATE utf8_bin DEFAULT NULL,
`DES_PROFILE` varchar(12) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `id_id_env_case` (`ID_ENV_CASE`,`ID`),
KEY `FK_CID_OPRT_CID_AIRP_DEPR` (`ID_CID_AIRPORT_DEPARTURE`),
KEY `FK_CID_OPRT_CID_AIRP_DEST` (`ID_CID_AIRPORT_DESTINATION`),
KEY `FK_CID_OPRT_CID_RUNW_DEPR` (`ID_CID_RUNWAY_DEPARTURE`),
KEY `FK_CID_OPRT_CID_RUNW_DEST` (`ID_CID_RUNWAY_DESTINATION`),
KEY `FK_CID_OPERATION_CID_AIRCRAFT` (`ID_CID_AIRCRAFT`),
KEY `FK_CID_OPERATION_ENV_CASE` (`ID_ENV_CASE`),
CONSTRAINT `FK_CID_OPERATION_CID_AIRCRAFT` FOREIGN KEY (`ID_CID_AIRCRAFT`) REFERENCES `cid_aircraft` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CID_OPERATION_ENV_CASE` FOREIGN KEY (`ID_ENV_CASE`) REFERENCES `env_case` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CID_OPRT_CID_AIRP_DEPR` FOREIGN KEY (`ID_CID_AIRPORT_DEPARTURE`) REFERENCES `cid_airport` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CID_OPRT_CID_AIRP_DEST` FOREIGN KEY (`ID_CID_AIRPORT_DESTINATION`) REFERENCES `cid_airport` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CID_OPRT_CID_RUNW_DEPR` FOREIGN KEY (`ID_CID_RUNWAY_DEPARTURE`) REFERENCES `cid_runway` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CID_OPRT_CID_RUNW_DEST` FOREIGN KEY (`ID_CID_RUNWAY_DESTINATION`) REFERENCES `cid_runway` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=15010153 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT COMMENT='This table respresents the data of an aircraft trajectory be'
1 row in set (0.00 sec)
mysql> ANALYZE TABLE cid_operation;
+-----------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+---------+----------+----------+
| ERM_ACC.cid_operation | analyze | status | OK |
+-----------------------+---------+----------+----------+
1 row in set (0.02 sec)
mysql> SHOW TABLE STATUS LIKE 'cid_operation'\G
*************************** 1. row ***************************
Name: cid_operation
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2655734
Avg_row_length: 125
Data_length: 333283328
Max_data_length: 0
Index_length: 290357248
Data_free: 3145728
Auto_increment: 15010153
Create_time: 2019-07-18 10:01:50
Update_time: NULL
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options: row_format=COMPACT
Comment: This table respresents the data of an aircraft trajectory be
1 row in set (0.00 sec)
and the same for cid_flight_point
mysql> SHOW CREATE TABLE cid_flight_point\G
*************************** 1. row ***************************
Table: cid_flight_point
Create Table: CREATE TABLE `cid_flight_point` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
`ID_CID_OPERATION` int(10) unsigned NOT NULL COMMENT 'Link on the operation that contains this flight point.',
`ID_REF_FLIGHT_PHASE` int(10) unsigned DEFAULT NULL,
`ID_REF_FLIGHT_ATTITUDE` int(10) unsigned DEFAULT NULL,
`ORIGIN` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT 'The point origin (track2d, profile, ...)',
`POINT_INDEX` int(11) NOT NULL,
`LATITUDE` double DEFAULT NULL COMMENT 'Latitude of a flight point (in decimal degrees).',
`LONGITUDE` double DEFAULT NULL COMMENT 'Longitude of a flight point (in decimal degrees).',
`DATE` date DEFAULT NULL COMMENT 'Date of the flight at this point. (dd_mm_yyyy)',
`TIME` time DEFAULT NULL COMMENT 'Time of the flight at this point. (hh:mm:ss)',
`TRUE_AIR_SPEED` double DEFAULT NULL COMMENT 'The speed of the aircraft by combination with the calibrated air speed and the speed of the air mass in which is located the aircraft. Expressed in Knots. Used only for noise (mandatory).',
`CALIBRATED_AIR_SPEED` double DEFAULT NULL COMMENT 'The speed of the aircraft with a correction done due to physical elements. Used only for noise (optional). (in knots)',
`CORRECTED_NET_THRUST` double DEFAULT NULL COMMENT 'The instant corrected thust in lbs. Used only for noise (mandatory).',
`TOTAL_NET_THRUST` double DEFAULT NULL,
`WEIGHT` int(11) DEFAULT NULL COMMENT 'The instant aircraft weight in tons. Used for noise, emissions, and Local Air Quality metrics.',
`FUEL_FLOW` double DEFAULT NULL COMMENT 'The instant fuel flow of an aircraft in kg/min. Used only for emissions and Local Air Quality.',
`FUEL_BURNT` double DEFAULT NULL,
`BANK_ANGLE` double DEFAULT NULL COMMENT 'The instant bank angle of an aircraft in degrees. Used only for noise.',
`TOTAL_DISTANCE` double DEFAULT NULL COMMENT 'The distance from the starting point. (in Ft)',
`AFL` double DEFAULT NULL,
`CREATED_AT` datetime DEFAULT NULL COMMENT 'Creation date.',
`CREATED_BY` int(11) DEFAULT NULL COMMENT 'Creation author.',
`UPDATED_AT` datetime DEFAULT NULL COMMENT 'Update date.',
`UPDATED_BY` int(11) DEFAULT NULL COMMENT 'Date of the last update.',
`IS_ON_GROUND` tinyint(1) DEFAULT NULL,
`FLAP_ID` varchar(20) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `UNQ_CID_FLIGHT_POINT` (`ID_CID_OPERATION`,`POINT_INDEX`),
KEY `FK_CID_FLGH_PONT_REF_FLGH_ATTT` (`ID_REF_FLIGHT_ATTITUDE`),
KEY `FK_CID_FLGH_PONT_REF_FLGHT_PHS` (`ID_REF_FLIGHT_PHASE`),
CONSTRAINT `FK_CID_FLGH_PONT_CID_OPRT` FOREIGN KEY (`ID_CID_OPERATION`) REFERENCES `cid_operation` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CID_FLGH_PONT_REF_FLGHT_PHS` FOREIGN KEY (`ID_REF_FLIGHT_PHASE`) REFERENCES `ref_flight_phase` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CID_FLGH_PONT_REF_FLGH_ATTT` FOREIGN KEY (`ID_REF_FLIGHT_ATTITUDE`) REFERENCES `ref_flight_attitude` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=744595925 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT COMMENT='This table represents a point and flight informations.'
1 row in set (0.00 sec)
mysql> ANALYZE TABLE cid_flight_point;
+--------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+---------+----------+----------+
| ERM_ACC.cid_flight_point | analyze | status | OK |
+--------------------------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> SHOW TABLE STATUS LIKE 'cid_flight_point'\G
*************************** 1. row ***************************
Name: cid_flight_point
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 143444013
Avg_row_length: 130
Data_length: 18739101696
Max_data_length: 0
Index_length: 7500464128
Data_free: 6291456
Auto_increment: 744595925
Create_time: 2019-07-17 07:02:04
Update_time: 2019-07-17 08:44:03
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options: row_format=COMPACT
Comment: This table represents a point and flight informations.
1 row in set (0.00 sec)
Best Regards,