Re: MySQL 5.7 : InnoDB. 2 Deletes query using index but... too many rows locked and cause deadlock

[Christine Antoine]

This was my future idea :) To force it. Many thanks again !

In the meantime, I've reproduce the issue and (quite) succeed to explain the connection that fails.
This is not the same id, but this is a very simlar one (you understood that the DELETE are done in chunk for performance/volume reasons).

When I do the same explain just before the deadlock, it uses the primary key :
# 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', 'PRIMARY', '4', 'const', '477', '100.00', 'Using where'

and not the index on ID_ENV_CASE.. so clearly it makes sense.
I'll try the force and keep you informed.

Thanks for your time and investigation.