У меня есть таблицы menus, categories и products. Я использую mysql 5.5. Все таблицы являются innoDB, и во всех случаях идентификатор является первичным ключом (int) с автоинкрементом.
menus table
id, name, status
categories table
id, menu_id, name
products table
id, menu_id, category_id, status, name, url, content
Несколько скриптов могут работать параллельно, выполняя один и тот же файл php, который содержит следующую логику.
START TRANSACTION;
SET autocommit = 0;
LOCK TABLES products WRITE, categories WRITE, menus WRITE;
SELECT
p.`id`,
p.`name`,
p.`url`,
p.`status`,
c.`id` cat_id,
c.`name` cat_name,
m.`id` `menu_id`,
m.`name` menu_name
FROM
products p
LEFT JOIN categories c
ON p.`subcategory_id` = c.`id`
LEFT JOIN menus m
ON p.`menu_id` = m.`id`
WHERE p.`status` = 0 LIMIT 1
если после выбора результат найден, его статус обновляется до 1 (в противном случае я откатываюсь и снимаю блокировки)
UPDATE products SET status = 1 WHERE id = [product_id];
если последний запрос выполнен успешно, я запускаю
COMMIT();
UNLOCK TABLES;
в противном случае
ROLLBACK();
UNLOCK TABLES;
После этого скрипт делает запрос curl к URL-адресу этого продукта, чтобы получить от него некоторый контент и соответствующим образом обновить строку продукта.
// making curl request (might take a few dozen seconds, because proxy is being used and sometimes because of proxy failure the request is attempted again)
trying to update the products table
UPDATE products SET content = [received content], status = 2 WHERE id = [product_id]
Итак, скрипт устанавливает X-блокировку на указанные таблицы, извлекает одну строку из таблицы products со статусом 0 (означает - TODO), пытается установить ее статус на 1 (означает PENDING) и разблокирует таблицы. После этого пытается выполнить некоторую логику в php и, наконец, пытается обновить таблицу продуктов - обновляет столбец содержимого, а также статус до 2 (означает ВЫПОЛНЕНО).
Если я запускаю 5 сценариев параллельно, после нескольких минут выполнения самого последнего шага (обновление продукта до DONE) я получаю эту ошибку.
Error: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when try to get lock; try restarting transaction
Я понимаю общую идею взаимоблокировок, когда две транзакции ждут друг друга для обновления одних и тех же строк в обратном порядке, однако я не могу понять причину взаимоблокировки в этом случае. Я имею в виду, что скрипты работают и блокируют таблицы в том же порядке, поэтому, если один скрипт заблокировал products (и другие) таблицы, получив эксклюзивную блокировку, другие скрипты должны ждать в очереди, пока эти блокировки будут сняты, поэтому это не должно вызывать deadlock.
С другой стороны каждый скрипт выбирает товары со статусом -> 0 и пытается обновиться до 1, а во время той же "сессии" с 1 на 2, поэтому я не вижу, как это может быть причиной тупик. Что мне здесь не хватает?
изменить:
Хотя я не упомянул, как я использую информацию из таблиц категорий и меню, но мне также нужно их получить, их использование не важно, поскольку я не выполняю с ними никакой обработки базы данных.
Раньше я использовал блокировку на уровне строк SELECT FOR UPDATE, однако у меня возникали взаимоблокировки, как в этом вопросе Видеоблокировка MySQL InnoDB при SELECT с монопольной блокировкой (FOR UPDATE) , поэтому мне пришлось изменить код на блокировку на уровне таблицы
Спасибо
