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

New Topic

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

Thanks!

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`),
by
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`)
and
`id_id_env_case` (`ID_ENV_CASE`,`ID`) == `id_id_env_case` (`ID_ENV_CASE`,`ID`, ID)
https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

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

Navigate: Previous Message• Next Message

Options: Reply• Quote

Subject

Views

Written By

Posted

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

2968

Christine Antoine

July 17, 2019 08:30AM

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

766

Olivier Dasini

July 18, 2019 02:55AM

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

835

Christine Antoine

July 18, 2019 03:11AM

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

753

Olivier Dasini

July 18, 2019 03:52AM

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

779

Christine Antoine

July 18, 2019 04:01AM

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

793

Olivier Dasini

July 18, 2019 04:09AM

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

799

Christine Antoine

July 18, 2019 04:20AM

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

743

Olivier Dasini

July 18, 2019 04:28AM

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

708

Christine Antoine

July 18, 2019 04:29AM

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

791

james wang

August 19, 2019 02:44AM

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.