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;

There's no lock involved on selects, could you please provide more informations? — Sebas, May 31 '12 at 17:46
@Sebas: The example in the OP's question uses [`SELECT ... FOR UPDATE`](http://dev.mysql.com/doc/en/innodb-locking-reads.html), which does lock. — eggyal, May 31 '12 at 17:48
I see, well, first of all, what is to be updated? models, colors or both? — Sebas, May 31 '12 at 17:50
@Sebas: I want to update both tables; in fact there is a parent-child relation between models and colors — mazatwork, May 31 '12 at 17:58
ok, so, sorry to bother :-), do you want to update the foreign key + models, or models + fields of colors? — Sebas, May 31 '12 at 18:05
@Sebas: i want to update arbitrary fields of models and colors — mazatwork, May 31 '12 at 18:11
I would be surprised if mysql supported something like this. Your question is presupposing a particular method for doing the sort -- a nested loop. I would recommend that you update the two tables independently within a single transaction. — Gordon Linoff, May 31 '12 at 18:15
@Gordon Linoff: I use an ORM and all manipulation is done via entities. Whenever a certain field of color changes the parent entity is notified so it may also update its fields. — mazatwork, May 31 '12 at 18:30
@Sebas: e.g. color.setStockCount() may call this.model.colorStockCountChanged() to notify the parent model entity. Later the entityManager will save all changes. Before the entities mutate their state I want to acquire the locks. — mazatwork, May 31 '12 at 19:01
@MarcusAdams: STRAIGHT_JOIN is awesome; i think you have just revealed the solution! (now i have to find a solution on how to get indicies applied instead of full table scan but that's no problem) — mazatwork, May 31 '12 at 20:00
With STRAIGHT_JOIN you need to join colors to models, you are searching by color.id not by model. Also isolation level might make a difference, e.g. REPEATABLE READ would ensure that no-one modifies the row that you have read until you commit. And yes, make sure that colors.id and models.id are indexed. Otherwise you lock the whole table with every select (if using REPEATABLE READ). — Toni, Aug 07 '12 at 21:06
The whole SELECT is a transaction / atomic on its own, doesn't that change things? I mean, for the rest of the application all record involved are locked at the same time. — Patrick Savalle, Aug 15 '12 at 12:37

Danack · Answer 1 · 2012-08-16T07:39:28.610

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.

Avoid dead lock by ordering explicitly

1 Answers1

Linked