Почему один запрос к MySQL отражается на выполнении следующего

#mysql

UPDATE `table` SET `link`=0 where `link`=X AND `type` IN (LIST);
DELETE FROM `table` where `link`=X;

В целевой таблице table удаляются (update) ссылки у строк определенного типа.
Затем происходит удаление оставшихся. При этом второй запрос происходит очень долго,
драйвер падает с ошибкой таймаута.
Если первый запрос не выполнять, то второй, на удаление, пройдет быстро.
Индекс на поле link имеется.
Во время работы второго запроса processlist показывает state = updating

| Query   |   27 | updating | DELETE FROM `table` WHERE `table`.`link` = ?

Как с этим бороться?
v5.6.16
p.s. Обе операции происходят внутри транзакции, если операцию на обновление вынести
до старта транзакции, то проблем не наблюдается.
    

Ответы

Ответ 1

В InnoDB осуществляется блокировка на уровне строк, поэтому на время поиска или сканирования
индекса таблицы устанавливается совместно используемая или эксклюзивная блокировка
записей обрабатываемых индексов

Существуют следующие блокировки в InnoDB:


record lock — блокировка записи индекса
gap lock — блокировка промежутка между, до или после индексной записи
next-key lock — блокировка записи индекса и промежутка перед ней


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

Представьте индекс, как оглавление в книге, и вот сейчас его кто-то меняет, а еще
меняет какие-то данные в книге, а Вы хотите его забрать себе (прочитать или изменить),
но вы это не сможете сделать пока текущий обладатель этой книги не завершит работу.
И правильно, что вы не сможете этого сделать, ведь если в один момент обладатель поменял
данные, но не успел поменять оглавление, а вы его взяли читать, то оглавление будет
неправильное.

У вас происходит record lock и следовательно блокируются все сущности связанные с
X. Например, SELECT c1 FOR UPDATE FROM T WHERE c1 = 10; предотвращает любую другую
транзакцию от вставки, обновления или удаления строк, где значение t.c1 равно 10.

Блокировки всегда используют индексные записи, даже если таблица определена без индексов.
Для таких случаев InnoDB создает скрытый кластерный индекс и использует этот индекс
для блокировки записей.

InnoDB использует блокировки так, чтобы полностью покрыть условие поиска блокирующего
запроса и при этом заблокировать наименьшее возможное количество индексного пространства.


Ответ 2

Могу предположить что при выполнении UPDATE поля по которому есть ключ идет перестройка
индекса. Если записей в таблице много - это может занять достаточно много времени.
Рекомендую попробовать сперва удалять DELETE а потом уже делать UPDATE:

DELETE FROM table where link=X AND type NOT IN (LIST);
UPDATE table SET link=0 where link=X;


Ответ 3

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

show full processlist;

Посмотреть на текущие блокировки можно так

select * from information_schema.innodb_locks;

Информацию о транзакциях так

SHOW ENGINE INNODB STATUS

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