#mysql #sql #sql-update #sql-order-by #where-clause
#mysql #sql #sql-обновление #sql-order-by #where-предложение
Вопрос:
У меня есть таблица с именем queue_items , которая имеет 3 столбца.
'item' varchar(500) the item that has been queued
'processed_at' a datetime to mark when it has started processing, and
'completed_at' a datetime to mark when it has completed in the queue worker
Вот как я выбираю элементы,
SELECT `id`,`item` FROM `queue_items` WHERE `processed_at` IS NULL AND `completed_at` IS NULL ORDER BY `id` ASC LIMIT 1
После проверки того, что она существует и допустима для обработки, я запускаю это:
UPDATE `queue_items` SET `processed_at` = @processedAt WHERE `id` = @id
Проблема в том, что другой работник очереди может забрать элемент, который я только что выбрал, до того, как я обновлю processed_at . Как я могу остановить это?
Ответ №1:
Поместите транзакцию вокруг двух запросов и используйте FOR UPDATE опцию в SELECT запросе, чтобы заблокировать строки, которые он проверил. Любое другое соединение, которое пытается прочитать эту строку, будет приостановлено до тех пор, пока транзакция не будет зафиксирована.
Убедитесь, что у вас есть индекс для столбцов, которые вы тестируете в WHERE предложении, поэтому ему не придется выполнять сканирование и блокировать все проверенные строки, прежде чем найти нужную.
START TRANSACTION;
SELECT @id := `id`,`item`
FROM `queue_items`
WHERE `processed_at` IS NULL AND `completed_at` IS NULL
ORDER BY `id` ASC
LIMIT 1
FOR UPDATE;
UPDATE `queue_items` SET `processed_at` = @processedAt WHERE `id` = @id
COMMIT;
Комментарии:
1. Я не вижу
FOR UPDATEопции в вашем ответе, где я должен ее разместить? Кроме того, что произойдет, если второй экземпляр попытается выполнить тот же запрос, получит ли он просто следующую строку? Нужно ли мне добавлять какую-либо условную проверку в мое приложение, чтобы учесть это?2. Вы не видите это в строке после
WHERE?3. На самом деле, это было не то место. Это должно быть после
LIMIT 1.4. Извините, я опубликовал это до того, как вы обновили вопрос (или я, возможно, пропустил его). Не могли бы вы ответить на мой последний вопрос?
5. Второй экземпляр будет заблокирован до завершения транзакции. В это время эта строка будет обновлена, и она получит следующую строку.
Ответ №2:
Вы можете сделать это в одном запросе. Использовать update с предложением, ограничивающим строку:
UPDATE `queue_items`
SET `processed_at` = @processedAt
WHERE `processed_at` IS NULL AND `completed_at` IS NULL
ORDER BY id LIMIT 1
Если вы хотите сохранить id строку, которая была обновлена, это немного сложно. MySQL не поддерживает RETURNING предложение, но мы можем обойти это с помощью пользовательской переменной:
SET @updated_id := 0;
UPDATE `queue_items`
SET `processed_at` = @processedAt, id = (@x := id)
WHERE `processed_at` IS NULL AND `completed_at` IS NULL
ORDER BY id LIMIT 1;
@SELECT @updated_id;
Комментарии:
1. Конечно, но как мне получить элемент записи, которую я обновил одновременно? В противном случае я не буду знать, какой элемент обрабатывать на рабочем. Если я использую запрос для последнего обновленного идентификатора и извлекаю его таким образом, другой работник мог бы обновить запись, с которой я попадаю в то же место.
2. Спасибо! Так что, это запрещает двум работникам получать один и тот же элемент очереди, даже с многоядерным сервером MySQL?
3. @asynclife: это безопасно с точки зрения параллелизма, поскольку операция выполняется одним оператором (вместо двух в вашей первоначальной попытке) - и, конечно, пользовательские переменные являются частными для сеанса.
4. Обновлено: исправлено путем изменения
@xна правильное имя переменной5. Для всех, кто использует C # и натыкается на этот ответ, убедитесь, что вы установили
AllowUserVariablesзначение true в строке подключения, иначе возникнет исключение из-за того, что параметр не определен.