2

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

Improve this question
1
  • If I am understanding correctly, you're looking for a SELECT ... FOR UPDATE pattern with Isolation RR? Commented Mar 21, 2019 at 16:49

2 Answers 2

Reset to default
3

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.

Improve this answer
1
  • I wrapped the calls in transaction with rs locks. Seems to fix the issue Commented Mar 22, 2019 at 15:39
0

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
Improve this answer
1
  • I tried doing a similar thing, using SELECT FROM FINAL TABLE dba.stackexchange.com/q/232659 but update is not supported in my version of DB Commented Mar 21, 2019 at 20:04

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.