Putting an order in the way how locks are aquired in MySQL

Question

From MySQL documentation:

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.

When I'm performing an UPDATE (although I would be interested about the others also, now I'm concerned with UPDATE statement), is there a way to put lock in the same order, so that to avoid deadlocks as much as possible?

N.B. Deadlocks occur in my table due to concurrent updates and inserts (and even deletes).

Answer 1

Short answer: No.

Thing you can do:

• Tailor the indexes to the queries -- this may decrease the frequency of deadlocks. Would you like to provide some examples of what is giving you trouble?
• Sort the items in IN(...) -- this may prevent certain deadlocks.
• Check for errors and replay transactions that are rolled back due to a deadlock. Then live with the deadlocks.