MySQL Forums
Forum List  »  Performance

Re: Delete query not using index and cause deadlock
Posted by: Rick James
Date: August 24, 2015 12:27PM

> INDEX(grid_id = 10, grid_role_id)
That was a typo; I meant
> INDEX(grid_id, grid_role_id)

And, as Øystein points out, INDEX(grid_id) is essentially that, since secondary keys have the PK appended. (An oversight on my part.)

> A very good idea! As you say, using this statement, the index scan will be separated from the base table look-up. Very smart!

Thanks for the compliment. I have spent years discovering subtle things like this.

> I do not think sorting the IDs would make any difference.

Yeah, I wasn't sure if it would apply here. When there are two DELETEs (or UPDATEs) in parallel, they can deadlock if they have some (or all) the same rows are involved but in different orders. By sorting, the deadlock and ROLLBACK turns into a delay as one transaction waits for the other to let go of row locks.

Øystein, I do not know if the query engine sorts the ids before acquiring row locks. But that _might_ be a deadlock-prevention feature.

Options: ReplyQuote

Written By
Re: Delete query not using index and cause deadlock
August 24, 2015 12:27PM

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.