MVSFORUMS.com

[pendant]

Query

How to avoid concurrency Problems(may it be wrong updation of database or an abend) in a project that has DB2 as database and EJB as front end when both online as well as batch try to update the database.

More Details

CA7 is the scheduler that triggers the batch process which runs in the Java server. CA7 has an option called Virtual Resource Connection that will take care of the concurrency part. But In this case, Once the CA7 triggers the Java part of it, there is no relation between ca7 and the original batch, apparently coz of which Virtual Resource Connection cannot be used.

[Ravi]

We used to have a Logical Lock for tables. i.e., when ever some batch process is running it will insert a row in a Table (logical lock) saying that the batch process is running with the job name as one of the field.

We created a small process (say checkLock)which the online uses. From the online Whenever a Save/Update/Delete is clicked, it will execute checkLock and then process based on the result with cache 'ing the data.

As we tried and trying to tune the queries for better performance (in the batch side) at the max, the client is locked for around Max. 10 secs for few queries. We are still in the process of cutting this down.


Later on, for few processes we were using Hibernate which also improved the process. Its mainly used to insert/update huge amount of data and the performance was increased. For select queries it doesn't improve the performace where as for database updations it definetly is fast.

[pendant]

Online being vital for the clients, Don't you think more importance should be given to the online click rather than the batch. Just a thought.

If not, Atleast in my case I would ask Batch to wait for the online click to go ahead.

Please pour in with more ideas

Pendant.

[Ravi]

If you want to the Batch process to wait then I don't think you can ever run a Batch process as most of the time the users will be online and atleast min one user will be trying to update the fields. So its difficult for the Batch process to wait till all the users are done with their processing.

Find a good time when you can run the batch process. Might be nights or when you think based on Online users logging, the approiate time to run the batch job.

Locking the clients for < 10 sec with an approprite message should be a good idea. But I don't know your requirements and how critical the clients are to lock them out. Might be trying out a particular time for the job to run and notify the users that daily at so and so time a batch job will run and might lock the clients out. . Think am going away from the topic.


OK .. Another idea could be using row/column level locking of the tables. Making the max use of DB2 as far as locking is considered.

But the problem is (which we faced and still facing @ some time) is that the Max. no of locks goes out of limit allowed and the process might fail. If we have huge client base and a batch job is trying to run at the same time, this problem might come.