I am not sure whether this is the right forum, how ever thought of putting this in somewhere related to web access.Anyways let me explain
I have a web application which creates employee.There is an employ table which store the details of employees of different departments. The table have an empNo attribute which should be unique for a department.This should be auto generated.
The requirement given was , when the user starts creating the employ the employ number should be displayed on the top of the employ creation page.The employ number should be the next available employ number for that department.
As employ number can be repeated for different departments , i can not use a data base sequence to generate the employ number.So I decided to generate this programmatic , by checking the max employ number for that department from database, when user entering this page.
I would be storing this information at the session level, basically inside the Employ object (a VO).
Now the issue is , when there are concurrent users creating employees for the same department, the concurrent users will have employ object with same employ number in their respective sessions. This will fail when they submit the form to insert into database , as the employ number is unique for same department.
I hope I have explained the scenario clearly. I am pretty much sure that this should not be the way to handle this. Can anybody guide me here for a better logic?
One option which quickly comes to my mind can be as follows. Have a synchronized method like getNextEmpNo retain the instance of max emp no read from DB whenever client call it. On next client call get emp no from DB compare it with one stored in instance variable. If they are same or greater get next valid no and return that to the client and don't forget to update the instance variable with the emp no you are returning to client.