How to release record locks in a repeat block inside a do transaction block

[Rajendran_]

Hello everyone,

I have an issue with a batch process program that takes a bunch of orders as input, allocates the required order line items, and creates a delivery flow and exports them as a report. When dealing with orders with huge order lines, I see a huge amount of records being locked. I've included my code structure below.

doTrans:
do transaction:
    ...
    repeat while not hQuery:query-off-end:
        /* updates made to customer table and required data's are loaded into temp-table */
        run internalCustProcedure.
   
        /* updates made to order and orderline table and required data's are loaded into temp-table */
        run internalOrdProcedure.
    end. /* repeat */
 
    /* Create picking records with above gathered data. */
    run generateFlowAndReport(temp-table).
    if error then undo doTrans, leave doTrans.
end. /* do transaction */

Consider the below:
internalCustProcedure - this procedure updates the order count on the customer table and loads user info in a temp-table
internalOrdProcedure - allocates the item to the order lines and loads the order and order line info in a temp-table
generateFlowAndReport - This procedure will determine on which flow the order has to be delivered and also generate the output report for reference

I tried adding the "transaction" keyword to the repeat statement, but it didn't fix the issue.

I'm experiencing an issue when I load approximately 100 orders, each with 500 order lines. This causes about 100 customer records and 100 * 500 = 500000 order line records to be locked in the repeat block until I exit the do block. I can't move generateFlowAndReport inside the repeat because I need all 500 orders to be processed before I can execute the roadmap flow for them. If something goes wrong with the delivery mapping flow, I'll have to undo the entire transaction.

What would be the better approach to undo the entire do block if something goes wrong in generateFlowAndReport and also release record locks in the repeat block?
What is the best way to minimize these record locks? I used the VST table below to determine the number of locks.

for first _DbStatus no-lock:
display _DbStatus._DbStatus-NumLocks .
end.

 

[tamhas]

The solution, of course, is to change the transaction scope. Making the entire do block one transaction is obviously going to lock a LOT of records with those kind of numbers. What is not obvious is what scope is appropriate for your business needs. Possibly you could process the orders for each customer as a transaction and build a temp-table with the info for your delivery flow. Unless, of course, you WANT to abort everything if there is an error anywhere, in which case you are pretty well stuck with a mega-transaction.

 

[Marco Mendoza]

Hello everyone,

I have an issue with a batch process program that takes a bunch of orders as input, allocates the required order line items, and creates a delivery flow and exports them as a report. When dealing with orders with huge order lines, I see a huge amount of records being locked. I've included my code structure below.

doTrans:
do transaction:
    ...
    repeat while not hQuery:query-off-end:
        /* updates made to customer table and required data's are loaded into temp-table */
        run internalCustProcedure.
  
        /* updates made to order and orderline table and required data's are loaded into temp-table */
        run internalOrdProcedure.
    end. /* repeat */
 
    /* Create picking records with above gathered data. */
    run generateFlowAndReport(temp-table).
    if error then undo doTrans, leave doTrans.
end. /* do transaction */

Consider the below:
internalCustProcedure - this procedure updates the order count on the customer table and loads user info in a temp-table
internalOrdProcedure - allocates the item to the order lines and loads the order and order line info in a temp-table
generateFlowAndReport - This procedure will determine on which flow the order has to be delivered and also generate the output report for reference

I tried adding the "transaction" keyword to the repeat statement, but it didn't fix the issue.

I'm experiencing an issue when I load approximately 100 orders, each with 500 order lines. This causes about 100 customer records and 100 * 500 = 500000 order line records to be locked in the repeat block until I exit the do block. I can't move generateFlowAndReport inside the repeat because I need all 500 orders to be processed before I can execute the roadmap flow for them. If something goes wrong with the delivery mapping flow, I'll have to undo the entire transaction.

What would be the better approach to undo the entire do block if something goes wrong in generateFlowAndReport and also release record locks in the repeat block?
What is the best way to minimize these record locks? I used the VST table below to determine the number of locks.

for first _DbStatus no-lock:
display _DbStatus._DbStatus-NumLocks .
end.

What kind of Errors do yo expect for UNDO the Transaction? Data errors or connection errors?
I would try to validate everything before the data update, and if not error, update data on small transactions; this is a batch program so the probability for session disconnection meanwhile is updating I think is very small.

 

[Rajendran_]

What kind of Errors do yo expect for UNDO the Transaction? Data errors or connection errors?
I would try to validate everything before the data update, and if not error, update data on small transactions; this is a batch program so the probability for session disconnection meanwhile is updating I think is very small.

The generateFlowAndReport procedure calls several other procedures, both locally and on an appserver. So the error can be of both data and connection.

 

[Rajendran_]

The solution, of course, is to change the transaction scope. Making the entire do block one transaction is obviously going to lock a LOT of records with those kind of numbers. What is not obvious is what scope is appropriate for your business needs. Possibly you could process the orders for each customer as a transaction and build a temp-table with the info for your delivery flow. Unless, of course, you WANT to abort everything if there is an error anywhere, in which case you are pretty well stuck with a mega-transaction.

Yes Tamhas, our objective is to reverse the entire procedure due to the modifications made to the Customer, Order, and Orderline tables within the repeat block. Another alternative I considered was incorporating generateFlowAndReport within the repeat block, but this disrupts the functionality and necessitates extensive code adjustments to ascertain the route mapping.

Thank you so much for your suggestions!

 

[tamhas]

I recommend that you think carefully about what you really need. For example, I can understand wanting to undo everything about a customer and their orders, while leaving everything about all other customers and their orders, plus treating the delivery flow as a separate transaction. What you need to think about is the consequence of getting an error and what your recovery from that looks like and how likely it is to happen.

 

[TomBascom]

It seems to me that this is (at least) a two step process. You seem to be gathering some information into temp-tables and then creating some stuff. But your transaction encompasses both phases. So, at the very least, it should look more like this:

repeat while not hQuery:query-off-end:

    /* updates made to customer table and required data's are loaded into temp-table */
    run internalCustProcedure.
   
    /* updates made to order and orderline table and required data's are loaded into temp-table */
    run internalOrdProcedure.

end. /* repeat */

doTrans:
do transaction:
    /* Create picking records with above gathered data. */
    run generateFlowAndReport(temp-table).
    if error then undo doTrans, leave doTrans.
end. /* do transaction */

Your comments say that the first part is all about building temp-tables so there is no reason for that to be inside a transaction block.

Beyond that I question the wisdom of "all or nothing" for this process. If it were me I would generateFlowAndReport() one customer at a time (or whatever unit makes sense) committing data as I go and keeping track of how far through the process I am. And I would then create the report at the end. With a little thought you should be able to devise a way to either restart after an error (and correcting the source of the error if need be) or, if necessary, run a set of reversals to undo the process.

Using database transactions to implement business logic like this doesn't always make sense. It sometimes works "ok" at a small scale but it becomes completely impractical at large scale.

 

[Rajendran_]

It seems to me that this is (at least) a two step process. You seem to be gathering some information into temp-tables and then creating some stuff. But your transaction encompasses both phases. So, at the very least, it should look more like this:

repeat while not hQuery:query-off-end:

    /* updates made to customer table and required data's are loaded into temp-table */
    run internalCustProcedure.
  
    /* updates made to order and orderline table and required data's are loaded into temp-table */
    run internalOrdProcedure.

end. /* repeat */

doTrans:
do transaction:
    /* Create picking records with above gathered data. */
    run generateFlowAndReport(temp-table).
    if error then undo doTrans, leave doTrans.
end. /* do transaction */

Your comments say that the first part is all about building temp-tables so there is no reason for that to be inside a transaction block.

Beyond that I question the wisdom of "all or nothing" for this process. If it were me I would generateFlowAndReport() one customer at a time (or whatever unit makes sense) committing data as I go and keeping track of how far through the process I am. And I would then create the report at the end. With a little thought you should be able to devise a way to either restart after an error (and correcting the source of the error if need be) or, if necessary, run a set of reversals to undo the process.

Using database transactions to implement business logic like this doesn't always make sense. It sometimes works "ok" at a small scale but it becomes completely impractical at large scale.

Hello Tom,

Thank you for your helpful suggestions. The reason the repeat was inside the "do transaction" is because "internalCustProcedure" and "internalOrdProcedure" also make updates to the database table. We first gather the data for the given order, then do the road mapping. Currently, we don't have a logic to update the route when processing every order. The "generateFlowAndReport" function will essentially create the route record for each order and then generate the report.

--
Rajendran

 

[TomBascom]

It sounds to me like you have a lot of serious architectural problems related to coupling and side effects. There is no magic wand to solve those, it takes hard work and dedication to undoing past mistakes.

 

[Rajendran_]

It sounds to me like you have a lot of serious architectural problems related to coupling and side effects. There is no magic wand to solve those, it takes hard work and dedication to undoing past mistakes.

Yes, when the functionality was built, the main focus was on making it work as expected, and details like this were overlooked. We are currently in the process of fine-tuning and optimizing the application. Thank you very much for your suggestions.

 

[Stefan]

Yes, when the functionality was built, the main focus was on making it work as expected, and details like this were overlooked. We are currently in the process of fine-tuning and optimizing the application. Thank you very much for your suggestions.

Architectural flaws are not a detail that can be fixed by fine-tuning.