Transaction/Buffer Scoping

[Progress_Learner]

Dear All,

I have created one scenario to increase Order quantity (in order-line) by 5 for all orders where customer number is 10. Below is code for the same:

FIND customer WHERE customer.cust-num = 10 NO-LOCK NO-ERROR.

FOR EACH order WHERE order.cust-num = customer.cust-num NO-LOCK:

    DO FOR order-line TRANSACTION:

        FOR EACH order-line WHERE order-line.order-num = order.order-num EXCLUSIVE-LOCK:

               order-line.qty = order-line.qty + 5.

        FIND ITEM WHERE ITEM.item-num = order-line.item-num EXCLUSIVE-LOCK NO-ERROR.

               ITEM.allocated = ITEM.allocated - 5.       

        END.

        FIND CURRENT ITEM NO-LOCK.        

    END.                 

END.

This code is working fine but I have few doubts like:

• Should i created buffer for order-line and item table because I am updating records in both tables, is there any benefit of doing that.
• Can I use two tables with DO FOR (strong scope) block because two tables are getting updated in a single transaction.
• Is it fine to take order-line table with DO FOR block and downgrading the lock with FIND current for item table?

Please suggest.

Regards,

Learner

 

[BobyIsProgress]

Hi,

From what I learned by the forum:
I would create a buffer for the two table I need to update.

With something like this:

DEFINE BUFFER updOrder-line FOR order-line.
DEFINE BUFFER updItem FOR ITEM.

FIND customer WHERE customer.cust-num = 10 NO-LOCK NO-ERROR.

FOR EACH order WHERE order.cust-num = customer.cust-num NO-LOCK:

    FOR EACH order-line WHERE order-line.order-num = order.order-num NO-LOCK:
        DO FOR updOrder-line TRANSACTION:
            FIND updOrder-line WHERE ROWID(updOrder-line) = ROWID(order-line) EXCLUSIVE-LOCK NO-ERROR.
            updOrder-line.qty = updOrder-line.qty + 5.
        END.
        DO FOR updItem TRANSACTION:
            FIND updITEM WHERE updITEM.item-num = order-line.item-num EXCLUSIVE-LOCK NO-ERROR.
            updITEM.allocated = updITEM.allocated - 5.       
        END.
    END.               

END.

 

[Progress_Learner]

Thanks for your reply Boby!

Here, the only problem that I can think of is, we are having two different transactions for order-line and item and it could be possible that order-line changes (Qty.) are committed to the database and item changes will remain uncommitted.

Please suggest.

Regards

 

[BobyIsProgress]

You will have to manage your error in the transaction that you can UNDO things. But I'm not an expert on that matter .

 

[LarryD]

Change this:

    DO FOR updOrder-line TRANSACTION:
            FIND updOrder-line WHERE ROWID(updOrder-line) = ROWID(order-line) EXCLUSIVE-LOCK NO-ERROR.
            updOrder-line.qty = updOrder-line.qty + 5.
        END.
        DO FOR updItem TRANSACTION:
            FIND updITEM WHERE updITEM.item-num = order-line.item-num EXCLUSIVE-LOCK NO-ERROR.
            updITEM.allocated = updITEM.allocated - 5.       
        END.

to this to make the rollback for both:

       DO FOR updOrder-line,updItem TRANSACTION:
            FIND updOrder-line WHERE ROWID(updOrder-line) = ROWID(order-line) EXCLUSIVE-LOCK NO-ERROR.
            updOrder-line.qty = updOrder-line.qty + 5.
        
            FIND updITEM WHERE updITEM.item-num = order-line.item-num EXCLUSIVE-LOCK NO-ERROR.
            updITEM.allocated = updITEM.allocated - 5.       
        END.

While a simple example piece of code, note that you are not handling if either of those records are already locked or if the record you are trying to update no longer exists.

 

[Rob Fitzpatrick]

Having the iterating for each...exclusive block inside a do...transaction block isn't a good pattern for concurrency. It will lead to a lot of limbo locks and higher lock table utilization than necessary. Depending on the number of records that satisfy the queries inside the do block, you could overflow the lock table.

 

[Progress_Learner]

Change this:

    DO FOR updOrder-line TRANSACTION:
            FIND updOrder-line WHERE ROWID(updOrder-line) = ROWID(order-line) EXCLUSIVE-LOCK NO-ERROR.
            updOrder-line.qty = updOrder-line.qty + 5.
        END.
        DO FOR updItem TRANSACTION:
            FIND updITEM WHERE updITEM.item-num = order-line.item-num EXCLUSIVE-LOCK NO-ERROR.
            updITEM.allocated = updITEM.allocated - 5.      
        END.

to this to make the rollback for both:

       DO FOR updOrder-line,updItem TRANSACTION:
            FIND updOrder-line WHERE ROWID(updOrder-line) = ROWID(order-line) EXCLUSIVE-LOCK NO-ERROR.
            updOrder-line.qty = updOrder-line.qty + 5.
       
            FIND updITEM WHERE updITEM.item-num = order-line.item-num EXCLUSIVE-LOCK NO-ERROR.
            updITEM.allocated = updITEM.allocated - 5.      
        END.

While a simple example piece of code, note that you are not handling if either of those records are already locked or if the record you are trying to update no longer exists.

Thanks for your valuable inputs LarryD.

 

[Progress_Learner]

Having the iterating for each...exclusive block inside a do...transaction block isn't a good pattern for concurrency. It will lead to a lot of limbo locks and higher lock table utilization than necessary. Depending on the number of records that satisfy the queries inside the do block, you could overflow the lock table.

Thanks for your valuable inputs Rob!