I am trying to resolve a race condition in my application.
I have a table that is used as a work queue and many threads and read/update it. The following query is executed from application. First it reads the ID's of available jobs, then it reserves them and gets the info.
SELECT ID FROM JOBS WHERE LOCKED_BY IS NULL LIMIT 5; --Check available jobs UPDATE JOBS LOCKED_BY = 'Thread#1' WHERE ID IN (?); --Lock jobs SELECT * FROM JOBS WHERE ID IN(?); --Get info about jobs to process
Issue arises when multiple threads go at this table at the same time. This if happens if
'Thread#2' reads the row before
'Thread#1' runs the
UPDATE. Causing same jobs to run twice.
I have found a solution by wrapping the queries in:
LOCK TABLE JOBS; --Queries from above COMIT;
This works fine and prevents race conditions but it is a bit extreme, because threads have to wait for each other to finish.
How can I make sure that I can place a lock only on records from the following query?
SELECT ID FROM JOBS WHERE LOCKED_BY IS NULL LIMIT 5;
This is on AS400 DB2 database, now LUW