How can I lock row for reading/updating in DB2?

Question

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

Answer 1

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.

Answer 2

As an alternative to locking (running with RS isolation will still have threads wait for each other and possibly deadlock, unless you SELECT .. FOR UPDATE), if you can tolerate processing less than 5 jobs at a time, you can use an optimistic approach:

UPDATE JOBS LOCKED_BY = 'Thread#1' WHERE ID IN (?) AND LOCKED_BY IS NULL; --Lock jobs
SELECT * FROM JOBS WHERE ID IN(?) AND LOCKED_BY = 'Thread#1'; --Get info