Как избежать тупика в 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')

Как я могу избежать тупика? Есть ли способ изменить запрос, чтобы заставить его работать, или мне нужно сделать что-то еще?

Ошибка не всегда, конечно, только после выполнения этого запроса много раз и в нескольких потоках.

Ответ 1

Вероятно, вы получите лучшую производительность, если вы замените "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);

Ответ 2

Насколько я понимаю, выбор не вызывает блокировки и не должен быть причиной тупика.

Каждый раз, когда вы вставляете/обновляете/или удаляете строку, происходит блокировка. Чтобы избежать тупиковой ситуации, вы должны убедиться, что параллельные транзакции не обновляют строку в порядке, который может привести к тупиковой ситуации. Вообще говоря, чтобы избежать тупика , вы должны получать блокировку всегда в том же порядке даже в разных транзакциях (например, всегда сначала таблица A, а затем таблица B).

Но если внутри одной транзакции вы вставляете только одну таблицу, это условие выполняется, и это обычно не должно приводить к тупиковой ситуации. Вы делаете что-то еще в транзакции?

Однако возможен тупик, если есть отсутствующие индексы. Когда строка вставлена /обновлена /удалена, базе данных необходимо проверить реляционные ограничения, то есть убедиться, что отношения согласованы. Для этого база данных должна проверять внешние ключи в связанных таблицах. Это может привести к приобретению другой блокировки, чем измененная строка. Убедитесь, что всегда есть индекс для внешних ключей (и, конечно, первичных ключей), иначе это может привести к блокировке таблицы вместо блокировки строк . Если происходит блокировка таблицы, конфликт блокировки выше и вероятность блокировки увеличивается.

Не уверен, что происходит именно в вашем случае, но, возможно, это помогает.