How can I lock row for reading/updating in DB2? 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
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


  Like level RR, level Read Stability (RS) ensures that:
  
  Any row read during a unit of work is not changed by other activation
  groups that use different commitment definitions until the unit of
  work is complete. 1 Any row changed (or a row that is currently locked
  with an UPDATE row lock) by another activation group using a different
  commitment definition cannot be read until it is committed.


Simply add WITH RS to your statements...

SELECT ID FROM JOBS WHERE LOCKED_BY IS NULL WITH RS LIMIT 5
UPDATE JOBS LOCKED_BY = 'Thread#1' WHERE ID IN (?) WITH RS
COMMIT


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.