To answer a question that arises from the comments, temporary tables in MySQL are owned by and only available to the client connection (session, thread) that created them, so there's not an isolation issue, but there is the potential issue of your connection having left something there with that name before, either with stale data or a similarly-named table with a different structure, either of which would be less than ideal.
CREATE TEMPORARY TABLE IF NOT EXISTS statement creates the temporary table if there isn't one of the same name, but is a non-blocking no-op if the table is already there, which, as noted, is still bad. The better alternative is probably to drop the temporary table at the top of the proc, if it's there, before creating it, so you always begin with a clean environment. This is similarly a no-op if the temporary table isn't there:
DROP TEMPORARY TABLE IF EXISTS BATCH_ITEMS_UOW;
Important: if you do not have an index on the STATUS column of the BATCH_ITEMS table, this will be one of the two major reasons this procedure is slower than it needs to be. That's going to be essential for performance, either with this code or the existing code.
It would probably also be best to index the temporary table, to make life as easy as possible for the optimizer. Note that the column declared must be the same as the name or alias of the column in the
SELECT, otherwise MySQL assumes the
SELECT is supposed to generate additional columns for the temporary table using the column names in the
CREATE TEMPORARY TABLE BATCH_ITEMS_UOW (
ID INT NOT NULL PRIMARY KEY
) ENGINE=MEMORY AS (
SELECT ID FROM BATCH_ITEMS
WHERE STATUS = 'UNPROCESSED'
AND BATCH_JOB_ID = batchId
Then instead of an update that use a subquery, this should be a join, since those are generally optimized better in any version of MySQL. This is the other likely reason this procedure may be slower than you expect.
UPDATE BATCH_ITEMS BI
JOIN BATCH_ITEMS_UOW UOW ON UOW.ID = BI.ID
SET BI.STATUS = 'PROCESSING';
Then return the result-set...
SELECT * FROM BATCH_ITEMS_UOW;
Optionally, you can drop the temporary table... it will be dropped automatically when your session disconnects, or the next time you run this procedure, since we're now explicitly dropping it at the start if you run this again in the same session.
DROP TEMPORARY TABLE BATCH_ITEMS_UOW;