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?
I would also be very much interested if that's possible when performing UPDATE with INNER JOIN (or with multiple tables).
N.B. Deadlocks occur in my table due to concurrent updates and inserts (and even deletes).
Copyright Notice：Content Author:「artaxerxe」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/34174109/putting-an-order-in-the-way-how-locks-are-aquired-in-mysql