Under load, the following job-queue design does not work reliably, why? I have a job queue table which is meant to mark-and-return one job at a time to the caller. The stored procedure works like this:

update job_queue
set status = "WORKING", job_id = &lt;arg_unique_job_id&gt;
where status = "NEW"
limit 1
;

if row_count() &gt; 0 then

    select
        -- get job details for work
    from job_queue
    where job_id = &lt;arg_unique_job_id&gt;
    ;

else

    -- nothing to do
    select 1 from dual where false;

end if;


The client generates a unique number (arg_unique_job_id) and passes it to the stored procedure. If there is any work to be done, that work will be marked as "WORKING" and get assigned the caller's job ID.

On occasion, under heavy DB load, I would find a job marked as WORKING but the client did not appear to get any row back, as if row_count "lied". Above statements are executed in one transaction, against InnoDB table (MySQL 5.6.39). Am I wrong in my assumption how above should work, or the error is likely elsewhere?
In similar situation I use:

DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;

REPEAT

    UPDATE job_queue
    SET status = "WORKING", job_id = &lt;arg_unique_job_id&gt;
    WHERE status = "NEW"
    LIMIT 1;

    SELECT *
    FROM job_queue
    WHERE job_id = &lt;arg_unique_job_id&gt;;

UNTIL row_count() &lt; 1 END REPEAT;


It's never gone wrong before.

PS. This construction was used in the case when a record free for reservation exists with guarantee. If not use some local variable (some @loopcount) for to limit the max amount of cycles to avoid infinite loop.