通过显式订购避免死锁 [英] Avoid dead lock by ordering explicitly

问题描述

我想显式地提供有关MySql InnoDB如何获取行锁的命令.如果可能的话，就不应有任何死锁. (如果我们遵循惯例.)

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"?

例如:

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;

推荐答案

尽管您可以通过Straight_join来做到这一点，但是您也可以通过复制select ...来显式地获取所需行的锁，以便在您要更新的行上进行更新.想要得到第一.

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;

由于锁定是分多个步骤进行的，因此在设置临时表和完成对两个表的锁定之间可能会修改表"colors"中的条目.

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.

这篇关于通过显式订购避免死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！