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.