Страницы

Поиск по вопросам

суббота, 6 октября 2018 г.

Гранулированый select c последующим update

Есть ли способы выполнения селекта с последующим апдейтом на выбранном наборе таким образом, чтобы между двумя запросами не выполнились запросы из других соединений, при этом не блокируя всю таблицу целиком?
Задача банальна - выбрать N записей, после чего все их обновить(несколько полей) так, чтобы между выборкой и обновлением те же самые записи не были выбраны параллельно в других соединениях.
Такие блокировки, как select for update не подходят, т.к они блокируют записи с индексом только если задано условие на "пространство" индексированного поля.
Есть ли какие-либо адекаватные методы решения этой задачи?
UPDATE:
Запросы выглядят следующим образом(все они - в триггерах):
START TRANSACTION ;
DROP TABLE IF EXISTS TempTable; CREATE TEMPORARY TABLE TempTable AS( SELECT units.id AS id, units.authkey AS authkey FROM availablePublicUnitsView units LEFT JOIN media_info ON units.id=media_info.unit_id AND media_info.media_id=mediaId WHERE media_info.media_id IS NULL LIMIT unitsCount #FOR UPDATE );
UPDATE units SET reserved=true, last_usage_time=NOW(), reservation_hash=reservationHash WHERE id IN (SELECT id from TempTable) AND reserved=false;
SELECT * FROM TempTable;
COMMIT ;
В SQL`е я не эксперт, так что если что-то в корне делаю неверно, буду рад услышать, что именно.


Ответ

В итоге после проведения нескольких нагрузочных тестов оптимальным оказался вариант, не использующий временной таблицы(каждый раз пересоздается, занимая основное время), но использующий блокировку для обновления.
Сначала все необходимые записи обновляются с установкой "ключа" выборки в виде хэша SHA-256(алгоритм не принципиален), затем все записи с установленным хэшом выбираются, но уже после завершения транзакции. Работает решение довольно быстро и с минимальными блокировками.
Важно также отметить, что данные из вью availablePublicUnitsView выбираются, предварительно сортируясь в случайном порядке(ORDER BY RAND), сводя блокировки почти к нулю.
CREATE PROCEDURE select_and_reserve_units( IN unitsCount INT, IN mediaId VARCHAR(40), IN reservationHash BINARY(64)) BEGIN
START TRANSACTION ;
UPDATE units SET reserved=true, last_usage_time=NOW(), reservation_hash=reservationHash WHERE id IN (
SELECT units.id FROM availablePublicUnitsView units LEFT JOIN media_info ON units.id=media_info.unit_id AND media_info.media_id=mediaId WHERE media_info.media_id IS NULL FOR UPDATE
) LIMIT unitsCount;
COMMIT ;
SELECT unit.id AS id, unit.authkey AS authkey FROM units WHERE reservation_hash=reservationHash AND reserved=true;
END;//
P.S видимо, пора переходить на PostgreSQL...

Комментариев нет:

Отправить комментарий