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?
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.
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 …