Batch Processing

[wjmartin]

This is a 'big' question so I am not expecting full answers - but I would appreciate some pointers to where I can get more info;

I have a shared DB consisting of Job Records. Each Job has a status (ie: 'in progress', 'completed', 'invoiced'). The status of each record is updated individually, that is, I open the individual record and stamp it with it's new status. However, for the final status ('invoiced') I want to be able to open a list of all 'completed' jobs and stamp them all as 'invoiced' simultaneously. The problem I anticipate is that in the time between when I create this list and the moment that I stamp it's records as 'invoiced' another user on the system may stamp another record as 'completed'. That record will then not be included in the batch stamping.

What strategies can be used to deal with this - ie: batch processing records when other users may be simultaneously modifying the very field that the batch is based on?

[comment]

other users may be simultaneously modifying the very field that the batch is based on?

Actually, it doesn't matter which field another user is modifying - if the record is locked, you won't be able to stamp it.

I am not sure what type of solution you are looking for: suppose you generate an invoice of 3 "completed" jobs 1, 2 and 3. At the same time, another user marks Job 4 as "completed". Job 4 is not in your found set, so it's not included. No matter what measures you take, you will get the same result if the other user marks Job 4 as "completed" 10 seconds later.

[RodSierra]

If you take a step back and look first at record access by user and lets say client this may make your life easier. There are many strategies for batch processing transactions, but for me all start with how you run your business. Who can access what type of record and when. These strategies play an important role with what your trying to do. Batch processing on a fully open shared data structure is very problematic if not impossible to control, depending on the amount of users. Conversely batch processing has its own set of problems that must be dealt with in a very very structured manner not only with the data, but how you run your business, for instance do you have a frozen schedule for a certain time period for planning, or how often is your inventory updated and does this sync with your ability to schedule deliveries? Many things to consider.