Избегайте мертвой блокировки, заказывая явным образом

Я хочу явно указать, как MySql InnoDB должен получать блокировки для строк. По возможности не должно быть мертвых замков, просто заглохших. (Если мы будем следовать соглашению.)

Во-первых, база данных должна заблокировать все строки таблицы «модели» в порядке возрастания. Затем все строки во второй таблице «цвета» должны быть заблокированы в порядке возрастания. Есть ли способ управлять базой данных, чтобы сначала заблокировать таблицы «модели», а затем «цвета»?

Например:

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;

mysql sql innodb locking pessimistic-locking
person mazatwork    schedule 31.05.2012    source источник
comment
Никакой блокировки выбора не происходит. Не могли бы вы предоставить дополнительную информацию?   -  person Sebas    schedule 31.05.2012
comment
@Sebas: пример в вопросе OP использует SELECT ... FOR UPDATE, который блокирует.   -  person eggyal    schedule 31.05.2012
comment
Вижу, ну, во-первых, что обновлять? модели, цвета или и то, и другое?   -  person Sebas    schedule 31.05.2012
comment
@Sebas: я хочу обновить обе таблицы; на самом деле между моделями и цветами существуют отношения родитель-потомок   -  person mazatwork    schedule 31.05.2012
comment
хорошо, извините за беспокойство :-), вы хотите обновить внешний ключ + модели или модели + поля цветов?   -  person Sebas    schedule 31.05.2012
comment
@Sebas: хочу обновить произвольные поля моделей и цветов   -  person mazatwork    schedule 31.05.2012
comment
Я был бы удивлен, если бы mysql поддерживал что-то подобное. Ваш вопрос предполагает определенный метод сортировки - вложенный цикл. Я бы рекомендовал вам обновлять две таблицы независимо в рамках одной транзакции.   -  person Gordon Linoff    schedule 31.05.2012
comment
@ Гордон Линофф: Я использую ORM, и все операции выполняются через сущности. Всякий раз, когда изменяется цвет определенного поля, родительский объект уведомляется, поэтому он также может обновить свои поля.   -  person mazatwork    schedule 31.05.2012
comment
не могли бы вы подробно рассказать об этом последнем пункте?   -  person Sebas    schedule 31.05.2012
comment
@Sebas: например, color.setStockCount () может вызвать this.model.colorStockCountChanged () для уведомления родительской сущности модели. Позже entityManager сохранит все изменения. Прежде чем сущности изменят свое состояние, я хочу получить блокировки.   -  person mazatwork    schedule 31.05.2012
comment
Вы можете получить тот же эффект, используя триггеры.   -  person Gordon Linoff    schedule 31.05.2012
comment
@MarcusAdams: STRAIGHT_JOIN потрясающий; я думаю, вы только что раскрыли решение! (теперь мне нужно найти решение о том, как использовать индикаторы вместо полного сканирования таблицы, но это не проблема)   -  person mazatwork    schedule 01.06.2012
comment
С помощью STRAIGHT_JOIN вам нужно объединить цвета с моделями, вы ищите по color.id, а не по модели. Также уровень изоляции может иметь значение, например REPEATABLE READ гарантирует, что никто не изменит прочитанную вами строку, пока вы не зафиксируете ее. И да, убедитесь, что colors.id и models.id проиндексированы. В противном случае вы блокируете всю таблицу при каждом выборе (при использовании REPEATABLE READ).   -  person Toni    schedule 08.08.2012
comment
Весь SELECT сам по себе является транзакцией / атомаром, разве это не меняет положение вещей? Я имею в виду, что для остальной части приложения все задействованные записи блокируются одновременно.   -  person Patrick Savalle    schedule 15.08.2012

Ответы (1)


arrow_upward
1
arrow_downward

Хотя вы можете сделать это через direct_join, вы также можете явно получить блокировки для строк, которые вам нужны, дублируя select ... for update на той, которую вы хотите получить в первую очередь.

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;

Поскольку блокировка выполняется в несколько этапов, записи в таблице «цвета» могут быть изменены между тем, когда вы настраиваете временную таблицу, и когда вы завершаете получение блокировок для двух таблиц.

Это может быть для вас нормально (т.е. если вы хотите изменить только существующие записи при запуске транзакции), но может вызвать небольшие ошибки, если это не то, что вы хотите.

person Danack    schedule 16.08.2012