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

#mysql #sql #innodb #транзакции

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

Задача банальна - выбрать 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`е я не эксперт, так что если что-то в корне делаю неверно, буду рад услышать,
что именно.
    

Ответы

Ответ 1

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

Сначала все необходимые записи обновляются с установкой "ключа" выборки в виде хэша
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...


Ответ 2

Я так поняла, нужно запретить параллельное выполнение этих запросов над одними и
теми же записями?

Попробуйте изменить уровень изоляции. При этом используя SELECT FOR UPDATE.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

READ UNCOMMITTED - разрешает другим чтение данных, даже если транзакция не подтверждена.

Либо REPEATABLE READ - должна блокировать только изменяемые строки.