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: Olivier Dasini
Date: July 18, 2019 03:52AM


Regarding cid_operation table.
because ID is your primary key and you're using InnoDB you can replace index:
UNIQUE KEY `id_id_env_case` (`ID_ENV_CASE`,`ID`),
UNIQUE KEY `id_id_env_case` (`ID_ENV_CASE`)

in other words `id_id_env_case` (`ID_ENV_CASE`) == `id_id_env_case` (`ID_ENV_CASE`,`ID`)
`id_id_env_case` (`ID_ENV_CASE`,`ID`) == `id_id_env_case` (`ID_ENV_CASE`,`ID`, ID)

Further more this index could be benefit for the other delete that is probably the root cause of the deadlock.

Could you also send the explain for the second query ?
There is probably something to win there

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.