Force index on a delete UPDATE backup.records FORCE INDEX(IDX_records_deviceId) SET readyToBeDeleted = 1 WHERE deviceId = 226219 AND timestamp &lt;= 1445255546
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76903 page no 7 n bits 872 index `IDX_records_deviceId` of table `backup`.`records` trx id 992346439 lock_mode X waiting

DELETE r.* FROM backup.records r  WHERE readyToBeDeleted = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 76903 page no 7 n bits 872 index `IDX_records_deviceId` of table `backup`.`records` trx id 992346377 lock_mode X locks rec but not gap

I have a persistent deadlock between these two queries. The second query, the delete one, is using the wrong index. I have an index that has only the readyToBeDeleted column.

Is there any way to force an index into a DELETE statement?
If not, could you propose a way to avoid this deadlock?

Table definition
CREATE TABLE `records` (
  `recordId` int(11) NOT NULL AUTO_INCREMENT,
  `deviceId` int(11) DEFAULT NULL,
  `timestamp` bigint(20) DEFAULT NULL,
  `priority` set('SOS','HIGH','NORMAL','GTWFIX') DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `speed` int(11) DEFAULT NULL,
  `hdop` double DEFAULT NULL,
  `sat` int(11) DEFAULT NULL,
  `di1` tinyint(1) DEFAULT NULL,
  `di2` tinyint(1) DEFAULT NULL,
  `di3` tinyint(1) DEFAULT NULL,
  `do1` tinyint(1) DEFAULT NULL,
  `do2` tinyint(1) DEFAULT NULL,
  `movement` tinyint(1) DEFAULT NULL,
  `immobilizer` tinyint(1) DEFAULT NULL,
  `analogInput` int(11) DEFAULT NULL,
  `iButtonId` varchar(20) DEFAULT NULL,
  `usb` tinyint(1) DEFAULT NULL,
  `battery` int(11) DEFAULT NULL,
  `ReportCommand` varchar(10) DEFAULT NULL,
  `reportType` tinyint(1) DEFAULT NULL,
  `sendTime` bigint(20) DEFAULT NULL,
  `state` varchar(4) DEFAULT NULL,
  `engineRPM` int(11) DEFAULT NULL,
  `fuelConsumption` double DEFAULT NULL,
  `temporaryRecordId` int(11) DEFAULT NULL,
  `temporaryRecordAlertId` int(11) DEFAULT NULL,
  `imei` varchar(50) DEFAULT NULL,
  `type` varchar(25) DEFAULT NULL,
  `activeStatus` tinyint(1) DEFAULT NULL,
  `doDebug` tinyint(1) DEFAULT NULL,
  `sim` varchar(50) DEFAULT NULL,
  `processingPriorityRecord` tinyint(1) DEFAULT NULL,
  `receivingIncompleteCommunication` tinyint(1) DEFAULT NULL,
  `lastLogTime` int(11) DEFAULT NULL,
  `lastLatitude` double DEFAULT NULL,
  `lastLongitude` double DEFAULT NULL,
  `SystemId` int(11) DEFAULT NULL,
  `viaGtw` tinyint(4) DEFAULT NULL,
  `readyToBeDeleted` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`recordId`),
  UNIQUE KEY `IDX_records_deviceId` (`deviceId`,`timestamp`),
  KEY `UK_records_Send_time` (`deviceId`,`sendTime`),
  KEY `IDX_records_readyToBeDeleted` (`readyToBeDeleted`,`deviceId`)
) ENGINE=InnoDB AUTO_INCREMENT=118492776 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT
The solution I have found for my problem with the help of @jkavalik was to:


Move the DELETE statement into a DB Event that runs every minute.

CREATE      
  DEFINER = 'admin'@'%' 
EVENT backup.DeleteProcessedRecords     
  ON SCHEDULE EVERY '1' MINUTE  
  STARTS '2015-10-20 11:10:18'  
  ON COMPLETION PRESERVE    
  DO  
BEGIN   
  DELETE FROM backup.records WHERE readyToBeDeleted = 1 ORDER BY deviceId;        
END

add the ORDER BY deviceId to the DELETE Statement.
change the Index IDX_records_readyToBeDeleted from (readyToBeDeleted) to (readyToBeDeleted,deviceId).


Now I am waiting to see if I still get as many deadlock as I had before. I will keep you posted @jkavalik. And once more thank you for your expertise.