как избежать мертвой блокировки в mysql
У меня есть следующий запрос (все таблицы являются innoDB),
INSERT INTO busy_machines(machine)
SELECT machine FROM all_machines
WHERE machine NOT IN (SELECT machine FROM busy_machines)
and machine_name!='Main'
LIMIT 1
Который вызывает мертвую блокировку, когда я выполняю ее в потоках, очевидно, из-за внутреннего выбора, правильно?
Ошибка, которую я получаю:
(1213, 'Deadlock found when trying to get lock; try restarting transaction')
Как я могу избежать мертвой блокировки? Существует ли способ измениться на запрос, чтобы заставить его работать, или я должен сделать что-то еще?
Ошибки всегда не происходит, конечно, только после выполнения этого запроса много времен и в нескольких потоках.
2 ответа
Вероятно, вы получите лучшую производительность, если замените «NOT IN» на внешнее соединение.
Вы также можете разделить это на два запроса, чтобы избежать вставки и выбора одной и той же таблицы в одном запросе.
Примерно так:
SELECT a.machine
into @machine
FROM all_machines a
LEFT OUTER JOIN busy_machines b on b.machine = a.machine
WHERE a.machine_name!='Main'
and b.machine IS NULL
LIMIT 1;
INSERT INTO busy_machines(machine)
VALUES (@machine);
Насколько я понимаю, select не получает блокировку и не должен быть причиной взаимоблокировки.
Каждый раз, когда вы вставляете / обновляете / или удаляете строку, устанавливается блокировка. Чтобы избежать взаимоблокировки, вы должны убедиться, что параллельные транзакции не обновляют строку в порядке, который может привести к взаимоблокировке. Вообще говоря, чтобы избежать взаимоблокировки , вы должны получать блокировку всегда в одном и том же порядке даже в разных транзакциях (например, всегда сначала таблица A, затем таблица B).
Но если в рамках одной транзакции вы вставляете только одну таблицу, это условие выполняется, и это обычно не должно приводить к тупиковой ситуации. Вы делаете что-то еще в транзакции?
Однако может возникнуть взаимоблокировка, если отсутствуют индексы . Когда строка вставлена / обновлена / удалена, базе данных необходимо проверить реляционные ограничения, то есть убедиться, что отношения согласованы. Для этого базе данных необходимо проверить внешние ключи в связанных таблицах. Это может привести к получению другой блокировки, кроме измененной строки. Убедитесь, что у внешних ключей всегда есть индекс (и, конечно, первичных ключей), иначе это может привести к блокировке таблицы вместо блокировки строки . Если происходит блокировка таблицы, конкуренция за блокировку выше и вероятность тупиковой ситуации увеличивается.
Не знаю, что именно происходит в вашем случае, но, возможно, это поможет.