mysql – Granular select followed by update

Question:

Are there ways to perform a selection with a subsequent update on the selected set in such a way that no queries from other connections are executed between two queries, while not locking the entire table?

The task is trivial – to select N records, and then update all of them (several fields) so that between selection and update the same records are not selected in parallel in other connections.

Locks such as select for update are unsuitable because they only lock indexed records if a condition is specified on the "space" of the indexed field.

Are there any adequate methods for solving this problem?

UPDATE:

Requests look like this (they are all in triggers):

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 ;

I'm not an expert in SQL, so if something is fundamentally wrong, I'll be glad to hear what it is.

Answer:

As a result, after several stress tests, the optimal option turned out to be that does not use a temporary table (it is recreated each time, taking up most of the time), but uses a lock for updating.

First, all necessary records are updated with the setting of the "key" of the selection in the form of a SHA-256 hash (the algorithm is not fundamental), then all records with the set hash are selected, but after the completion of the transaction. The solution works pretty quickly and with minimal blocking.

It is also important to note that the data from the availablePublicUnitsView selected after being ORDER BY RAND, reducing the locks to almost zero.

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;//

PS apparently it's time to switch to PostgreSQL …