Currently we have a sysOperation job in which we are deleting the records in a table and inserting new calculated fields in a service class. We are not using any ttsbegin ttscommit or putting it inside a try catch. I am just trying to understand how will the system work if two or more users run the service class simultaneously at the same time. Are there any preventive measures that we could take to avoid such situation?
Thank you Martin for your time, will do the same.
If you always want to use the table(s) in this way, disabling OCC directly on the table is the easiest way.
Hi Martin, Sorry that I didn't mentioned in my previous comment. In our service class we are performing multiple insert_Recordset operation and update_recordset operation on different groupings of records. I have seen code to set pessimistic locking in select statements but not sure how to activate the pessimistic locking for inser_recordset or Update_recordset. Please can you advice on that?
Also, will changing the table property OCC enabled to no will do the trick on pessimistic locking?
That's a simple scenario. Do the work in a transaction and use pessimistic locking.
When the second session tries to delete data from the table, it'll be blocked until the exclusive lock is released. When the original transaction completes, the second sessions continues - it deletes all the records created a moment ago and runs the same logic to generates them once more.
Hi Martin, thanks for your response.
For the first question, we are not generating any user specific data. All users have the same set of data.
For second question, Yes when two users launches the batch job I want the second user's batch to wait and then run once the first user's job get's over. Can you please advice on how can we achieve this?
It depends on what you want to achieve. If you want each users to have a separate set of data, then your design is wrong and you'll need to come with a different one. For example, you may want to use a temporary table, partition data by user ID or a session ID or so.
If all users should use the same same set of data and you just want to prevent multiple refreshes at the same time, decide that should happen. For example, if you run the job and another is already running, does it make sense to wait for completion and immediatelly running the same thing again? Or what should happen?
I suspect that you really need to redesign the thing. It doesn't sound like something designed for a multi-user system.
André Arnaud de Cal...
292,031
Super User 2025 Season 1
Martin Dráb
230,868
Most Valuable Professional
nmaenpaa
101,156