MySQL Forums
Forum List  »  Performance

Re: MySQL 5.7 : InnoDB. 2 Deletes query using index but... too many rows locked and cause deadlock
Posted by: Christine Antoine
Date: July 18, 2019 04:20AM

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.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.