I have a sample Spring Rest application. I have several clients accessing an API in the Spring Rest application. The API checks whether a job has started or not against the MSSQL DB that we use. If the Job status in DB is in started status, it will pick the record and update its status to inprogress and return the details of the Job as the response to the API and based on that the client will do some processing.
We have observed that, more than one client is picking up the same Job which is in started status and updates it to inprogress and pass the response to the client. So, it ends up like the same job is being processed by multiple clients.
We tried to resolve this by adding a synchronized block and enclosed the DB call that picks the record in started state and update it to inprogress. The DB call resides in the service layer. But still the duplicate issue is there.
If the controller and the subsequent layers like service and DAO layers are singleton then, when multiple API calls hit the web app, the synchronized block of code should be executed by one request at a time. But that is not what we see practically.
Could someone please help to resolve this issue?