How to handle race conditions in Web Service?

Question

I implemented a Web Service with Java Servlets.

I got the following setup: There is a database which handles 'job'-entries. Each job has a status like 'executing' or 'in queue' or 'finished'. If a user starts a new job, there is made an entry in the database with a job and the status 'in queue'.

The job should only be executed if less than five other jobs are already executed. If there are five others already executing the status needs to stay 'in queue' and a Cronjob will handle the execution of this job later.

Now I just wonder, that if there are less than five jobs executing at the moment, my Script will execute this job. But what if at the same time, between my script asking the database how many jobs are being executed and the script starting to execute the job, another request from another user creates a job and also gets 'four executing jobs' as a result from the database.

Then there would be a race condition and 6 jobs would be executed.

How can I prevent something like that? Any advice? Thank you very very much!

Answer 1

If I understand correctly and you have control over the application layer that makes the requests to the DB you could use Semaphores to control who is accessing the DB.

Semaphores, in a way, are like traffic lights. They give access to the critical code for only N threads. So, you could set N to 5, and allow only the threads in the critical code change their status to executing etc..

Here is a nice tutorial about using them.

Answer 2

You can use record locking to control concurrency. One way to do it is by executing "select for update" query.

Your application must have other table that store worker_count. And then your servlet must do as following:

1. Get the database connection

2. Turn off auto commit

3. Insert the job with 'IN QUEUE' status

4. Execute "select worker_cnt from ... for update" query.

(at this point other users that execute the same query will have to wait until we commit)

5. Read worker_cnt value

6. If worker_cnt >= 5 commit and quit.

(at this point you get the ticket to execute the job, but other users still waiting)

7. Update the job to 'EXECUTING'

8. Increment worker_cnt

9. commit.

(at this point other users can continue their query and will get updated worker_cnt)

10. do execute the job

11. Update the job to 'FINISHED'

12. Decrement worker_cnt

13. commit again

14. close the database connection