Как я могу избежать взаимоблокировок с моей таблицей очередей на MariaDB/Galera?

У меня есть таблица базы данных, которая в основном представляет собой очередь в порядке поступления. Строки просто вставляются в таблицу другими частями системы и забываются. Каждые 5 минут запускается задание для обработки элементов из очереди. Поле состояния каждой строки, подлежащей обработке, изменено с ожидающего значения на обрабатываемое. Последующие дубликаты в очереди сопоставляются и помечаются как дубликат ранее поставленного в очередь элемента, который обрабатывается. Задание процессора очереди — единственное, что делает с таблицей что-либо, за исключением тех частей системы, которые просто вслепую вставляют строки.

Это именно то, что процессор делает с очередью:

START TRANSACTION;

SELECT id
FROM api_queue
WHERE status=:status_processing

-- Application checks this result set is empty, then...

UPDATE api_queue qs
INNER JOIN api_queue qdupes ON qdupes.products_id=qs.products_id AND qdupes.action=qs.action
SET qdupes.status = IF(qs.id=qdupes.id, :status_processing, :status_processing_duplicate)
WHERE qs.id IN (:queue_ids) ;

COMMIT;

-- Each queue item is processed

-- Once processing is complete, we purge the queue

START TRANSACTION;

SELECT COUNT(*) AS total FROM api_queue WHERE status = :status_processing ;

-- Application sanity checks the number of processing items it's about to delete against how many it's processed, and then...

DELETE FROM api_queue WHERE status IN (:status_processing, :status_processing_duplicate) ;

COMMIT;

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

Первая транзакция, как правило, выполняется довольно быстро, если не заходит в тупик (0,1–0,2 секунды на завершение), но кажется, что тупики возникают примерно в 10 % случаев.

Почему он так часто попадает в тупики? Даже если транзакция блокирует все строки, находящиеся в настоящее время в таблице, следует ли ожидать, что это вызовет конкуренцию при добавлении новых строк в таблицу? Если да, то почему?

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

Моя таблица очереди выглядит так:

CREATE TABLE IF NOT EXISTS `api_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `products_id` int(11) NOT NULL,
  `action` tinyint(3) NOT NULL,
  `triggered_by` tinyint(3) NOT NULL,
  `status` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

mysql mariadb deadlock galera
person Alex    schedule 01.07.2015    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Моя мантра: «Не стой в очереди, просто делай». Я говорю это, потому что я видел слишком много очередей, реализованных в MySQL, которые провалились по той или иной причине. Распространенной причиной является то, что накладные расходы на вставку/проверку/удаление элементов могут быть такими же дорогостоящими, как «просто выполнение задачи». Так зачем удваивать стоимость? И, по-видимому, очередь вызывает дополнительные взаимоблокировки.

Согласно информации, которую вы предоставили, система должна обрабатывать 1500-3000 каждые 5 минут. Это должно обрабатывать от «100» до «тысяч».

Ваш механизм очередей кажется слишком сложным, поскольку он включает в себя JOIN и другие вещи, которые не просто 1-in, 1-out.

Предполагая, что вы пока отвергаете мои комментарии, я перейду к критике кода...

SELECT ... FOR UPDATE

возможно, требуется для обоих SELECTs.

SELECT рядом с DELETE можно было бы объединить с DELETE в виде мультитаблицы DELETE. Или можно было бы извлечь его вместе с соответствующим кодом из транзакции. (Более быстрые транзакции с меньшей вероятностью зайдут в тупик.)

Вы проверяете ошибки (взаимная блокировка и т. д.) после COMMITs, да? Именно тогда Галера получает хит.

При использовании IN(...) отсортируйте элементы. Базовый код, вероятно, блокирует строки в порядке IN элементов. Это может превратить взаимоблокировку в задержку до innodb_lock_wait_timeout секунд. (Такая задержка не так «плоха», как взаимоблокировка.)

Вы повторяете транзакцию, когда она заходит в тупик, правильно? (Это простой способ справиться с взаимоблокировками.)

Изменить (IN)

Если один поток выполняет UPDATE ... WHERE id IN (11,22), а другой — UPDATE ... WHERE id IN (22,11), и каждый из них блокирует одну строку, то попытка заблокировать другую строку приведет к тупиковой ситуации — и придется ROLLBACK. Если бы вместо этого оба сказали (11,22), то (в худшем случае) пришлось бы ждать (но не зайти в тупик). Я предполагаю без доказательств, что код InnoDB недостаточно проницателен, чтобы как-то избежать этого IN тупика - путем сортировки числа, атомарной блокировки или чего-то еще. (И я бы сказал, что тесак = медленнее, поэтому не стоит делать это в таком редком случае.)

person Rick James    schedule 02.07.2015
comment
Задание, которое он ставит в очередь, обычно занимает 3-5 минут, будь то 1 элемент или партия из 1000. Выполняемая им задача API также регулируется на 1 запрос в минуту. Я не отвергаю ваши комментарии, но в этом случае кажется, что очередь и группировка элементов - единственный вариант. Спасибо за ваши предложения. Да, я проверяю взаимоблокировки по всем запросам (SELECT, INSERT, UPDATE и COMMIT и т. д.). Да, я повторяю попытку в случае тупика. Я до сих пор не понимаю, почему мой код может их вызвать. Не могли бы вы подробнее пояснить предложение по заказу IN? - person Alex; 03.07.2015
comment
Ах, конечно. Я попробую, но я все еще не уверен, что это будет причиной взаимоблокировок в этом случае, поскольку только один процесс когда-либо будет изменять строки. Я последовал вашему совету и упростил очередь, используя INSERT INTO ... SELECT ... FROM ... WHERE NOT EXISTS ... описанный здесь, чтобы избежать дублирования. Я не хотел использовать ON DUPLICATE KEY UPDATE, потому что это могло привести к дальнейшим взаимоблокировкам (поэтому я решил сделать это так, как делал раньше). Этот новый способ кажется лучше (и менее сложным). - person Alex; 03.07.2015