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
Best Answer
Why are you using a table? The IBM i provides a native data queue object...
If your application is running off platform in a language that doesn't offer direct access to the native objects, you could always build a stored procedure or user defined function on the I to provide access to the data queue.
Having said that, the answer to your question is the same for any RDBMS, use isolation levels.
In your case, you want to use Read Stability
Simply add
WITH RS
to your statements...The first statement will lock (up to) 5 rows..till the
COMMIT
is done.Note that the table will need to be journaled if it is not already.