Avoid dead lock by ordering explicitly

Question

I want explicitly provide an order on how MySql InnoDB should acquire locks for rows. If this is possible there shouldn't be any dead locks just stalling. (If we follow the convention.)

First, the databse should lock all rows found in table "models" in ascending order. Then all rows in the second table "colors" should get locked in ascending order. Is there a way to control the database to lock table "models" first and then "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 it through straight_join, you can also explicitly get the locks on the rows you want by duplicating the select ...for 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;

As the locking is done in multiple steps it would be possible for entries in the table 'colors' to be modified between when you set up the temporary table and when you finish getting the locks on the two tables.

That may be ok for you (i.e. if you only want to modify existing entries, when the transaction start) but could cause subtle bugs if it's not what you want.