Взаимные блокировки на MySQL, удаление строк

У нас есть (в настоящее время InnoDB) таблица, которая содержит примерно 500000 строк. Это представляет очередь задач для запуска. Он хранится в базе данных MySQL.

На постоянной основе, по крайней мере, один раз в секунду, но иногда чаще, мы выбираем данные из них и впоследствии обновляем некоторые строки. Раз в день мы убираем старые строки из таблицы.

Мы начали зацикливаться на столе, и это привело к остановке обработки наших задач. Эти тупики были вызваны во время ночной пробежки. Комбинация DELETE, SELECT и UPDATE означала, что по сути ничего продуктивного не могло произойти. У меня, к сожалению, нет выхода из SHOW ENGINE INNODB STATUS.

Я хотел бы знать лучший вариант для решения этой проблемы. Обратите внимание, что наш код обнаруживает взаимоблокировки и перезапускает запрос. Кроме того, мы давно обнаружили, что одновременное удаление всех совпадающих строк слишком обременительно для таблицы базы данных, в которой наблюдается большая активность, поэтому мы ОГРАНИЧИВАЕМ наши удаления до 10 000 строк за раз и продолжаем повторный запрос до тех пор, пока все необходимые строки не будут обрезка.

Я вижу следующие варианты и хотел бы узнать, какие из них являются лучшими, или предложения по другим вариантам:

  1. УДАЛИТЬ меньше строк одновременно
  2. Используйте экспоненциальный откат в наших DELETE, хотя я обеспокоен тем, что это не поможет, учитывая нашу конкретную рабочую нагрузку
  3. ЗАБЛОКИРОВАТЬ СТОЛЫ согласно документации MySQL. Возможно, мы могли бы принять блокировку операторов SELECT и UPDATE на время удаления.
  4. Переключитесь на тип таблицы MyISAM. Мы пошли с InnoDB, потому что мы первоначально использовали транзакции для этой таблицы. Это больше не так. Я недостаточно знаком со спецификой, чтобы знать, является ли это жизнеспособным решением.
  5. Возможно, используйте UPDATE LOW_PRIORITY. Возможно, что DELETE не влияют на SELECT, а только на UPDATE, и этого может быть достаточно.
Источник

2 ответа

Решение

При выполнении DML операции, InnoDB блокирует все отсканированные строки, но не совпадает.

Рассмотрим эту схему таблицы:

DROP TABLE t_tran;

CREATE TABLE t_tran (id INT NOT NULL PRIMARY KEY, data INT NOT NULL, KEY ix_tran_data (data)) Engine=InnoDB;

DROP TABLE t_tran;

CREATE TABLE t_tran (id INT NOT NULL PRIMARY KEY, data INT NOT NULL, KEY ix_tran_data (data)) Engine=InnoDB;

INSERT
INTO    t_tran
VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5),
(6, 6),
(7, 7),
(8, 8);

START TRANSACTION;

DELETE
FROM    t_tran
WHERE   data = 2
        AND id <= 5;

В этом случае, MySQL выбирает RANGE путь доступа на id, который он считает дешевле, чем REF на data,

В параллельной транзакции вы сможете удалять или обновлять строки 6, 7, 8 но не ряды 1 в 5 так как они заблокированы (несмотря на то, что только строка 2 был затронут).

Если вы удалите id <= 5 из условия выше, вы сможете удалить любую строку, кроме строки 3,

К сожалению, вы не можете контролировать MySQL пути доступа в DML операции.

Лучшее, что вы можете сделать, это правильно проиндексировать ваши условия и надеяться, что MySQL выберет эти индексы.

Источник

Убедитесь, что ваша изоляция транзакции помечена как прочитанная и не повторяемая. Чтение зафиксировано должно быть по умолчанию, но мы увидели, что на нашем сервере по умолчанию innodb повторяется чтение.

Вы можете проверить, запустив следующее:

SHOW VARIABLES LIKE 'tx%';

Чтобы установить это, в своем файле my.cnf введите следующую строку:

tx_isolation=READ-COMMITTED
Источник
Другие вопросы по тегам