MySQL Forums
Forum List  »  Performance

Re: Delete query not using index and cause deadlock
Posted by: Rick James
Date: August 22, 2015 03:48PM

I wonder if the _composite_ index
INDEX(grid_id = 10, grid_role_id) would help?

Perhaps it would also require reformulating the query to

DELETE t1
    FROM `customgrid_grid_role` t1
    JOIN `customgrid_grid_role` t2  USING (grid_role_id)  -- assuming that is the PK
       WHERE t2.grid_id = 10
         AND t2.grid_role_id NOT IN (...);
(I am not sure of the syntax.)

The hope in my reformulation is that it will do an _index_ scan on t2, then DELETE from t1. This _might_ further avoid the contention.
Øystein, how does that sound?

A different trick is to _sort_ the ids before launching into the DELETE (or other query). That tends to avoid _deadlocks_ by turning them into "waits". The problem with your query is that it's not easy to get the list of ids, much less, to sort them.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Delete query not using index and cause deadlock
2351
August 22, 2015 03:48PM


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.