Mysql and execute stored procedure in atomic way or select update atomically

Question

In Mysql I have two concurrent processes that need to read some rows and update a flag based on a condition.

I have to write a stored procedure with transaction but the problem is that sometimes the two processes updates the same rows.

I have a table Status and I want read 15 rows where the flag Reserved is true, then update those rows setting the flag Reserved to False.

The updated rows must be returned to the client.

My stored procedure is:

CREATE DEFINER=`user`@`%` PROCEDURE `get_reserved`()
BEGIN
DECLARE tmpProfilePageId bigint;
DECLARE finished INTEGER DEFAULT 0;

DECLARE curProfilePage CURSOR FOR 
    SELECT ProfilePageId 
    FROM Status
    WHERE Reserved is false and ((timestampdiff(HOUR, UpdatedTime, NOW()) >= 23) or UpdatedTime is NULL)
    ORDER BY UpdatedTime ASC
    LIMIT 15;
DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET finished = 1;
    
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;

START TRANSACTION;

DROP TEMPORARY TABLE IF EXISTS TmpAdsProfile;
CREATE TEMPORARY TABLE TmpAdsProfile(Id INT PRIMARY KEY AUTO_INCREMENT, ProfilePageId BIGINT);

OPEN curProfilePage;

getProfilePage: LOOP
    FETCH curProfilePage INTO tmpProfilePageId;
    IF finished = 1 THEN LEAVE getProfilePage;
    END IF;
    UPDATE StatusSET Reserved = true WHERE ProfilePageId = tmpProfilePageId;
    INSERT INTO TmpAdsProfile (ProfilePageId) VALUES (tmpProfilePageId);
END LOOP getProfilePage;

CLOSE curProfilePage;

SELECT ProfilePageId FROM TmpAdsProfile;

COMMIT;

END

Anyway, if I execute two concurrent processes that call this stored procedure, sometimes they update the same rows.

How can I execute the stored procedure in an atomic way?

Answer 1

Simplify this a bit and use FOR UPDATE. That will lock the rows you want to change until you commit the transaction. You can get rid of the cursor entirely. Something like this, not debugged!

START TRANSACTION;

CREATE OR REPLACE TEMPORARY TABLE TmpAdsProfile AS
SELECT ProfilePageId 
  FROM Status
 WHERE Reserved IS false 
   AND ((timestampdiff(HOUR, UpdatedTime, NOW()) >= 23) OR UpdatedTime IS NULL)
 ORDER BY UpdatedTime ASC
 LIMIT 15 
   FOR UPDATE; 

 UPDATE Status SET Reserved = true 
  WHERE ProfilePageId IN (SELECT ProfilePageId FROM TmpAdsProfile);
 
COMMIT;

SELECT ProfilePageId FROM TmpAdsProfile;

That temporary table will only ever have fifteen rows in it. So indexes and PKs and all that are not necessary. Therefore you can use CREATE ... AS SELECT ... to create and populate the table in one go.

And, consider recasting your UpdatedTime filter so it can use an index.

AND (UpdatedTime <= NOW() - INTERVAL 23 HOUR OR UpdatedTime IS NULL)

The appropriate index for the SELECT query is

CREATE INDEX status_update ON Status (Reserved, UpdatedTime, ProfilePageId);

The faster your SELECT operation can be, the less time your transaction will take, so the better your overall performance will be.