Avoid deadlocks when ordering explicitly

Question

I want to explicitly provide an order on how MySql InnoDB should get locks for rows. If possible, there should not be any dead locks that just stopped. (If we follow the agreement.)

First, the database should block all rows found in the "model" tables in ascending order. Then all rows in the second color table should be locked in ascending order. Is there a way to manage the database in order to first lock the “model” tables and then the “colors”?

Given for example:

start transaction; select * from models m join colors c on c.model_id = m.id where c.id IN (101, 105, 106) order by m.id asc, c.id asc for update; 

Answer 1

Although you can do this via straight_join, you can also explicitly get locks on the rows you want, duplicating the selection ... to update on the one you want to get first.

 CREATE TEMPORARY TABLE colorsToUpdate ( colorID BIGINT(20) NOT NULL, modelID BIGINT(20) NOT NULL ); insert into colorsToUpdate ( colorID, modelID) SELECT id, model_id FROM colors where id in (101, 105, 106); #This will try to acquire lock on models select m.* from models m join colorsToUpdate c on c.modelID = m.id for UPDATE; #this will try to get locks on models, and colors. select m.*, c.* from colorsToUpdate u left join models m on u.modelID = m.id join colors c on u.colorID = c.ID order by m.id asc, c.id asc for update; # do your data modification here. drop table colorsToUpdate; 

Since locking is performed in several stages, it would be possible to change the elements in the "colors" table between setting up a temporary table and completing locking of two tables.

This may be good for you (i.e. if you want to modify existing records when you start a transaction), but it can cause subtle errors if that is not what you want.