Locking Scope

[KMoody]

I'm having some trouble with a record's locking scope.

Our table SYSMST2 keeps track of the next available order number. Using OE-01A.P, we need to increment SYSMST2.nextord in each time we enter a new order in MAIN.P below. We want to unlock SYSMST2 immediately after incrementing SYSMST2.nextord so that other users can access the table. However, SYSMST2 doesn't unlock until the user leaves the main repeat lock in MAIN.P.

How can we scope OE-01A.p so that SYSMST2 unlocks properly? I've tried using RELEASE SYSMST2 and FIND CURRENT SYSMST2 NO-LOCK NO-ERROR at the end of OE-01A.P, but neither works.

EDIT: I can't add my code to my post for some reason. I keep getting an "Oops! We ran into some problems" message.

 

[Rob Fitzpatrick]

I suggest you do a COMPILE LISTING of your code to determine the scope of your transaction(s) and record buffers. A RELEASE won't help if the transaction in which sysmst2.nextord was updated is still active. The exclusive lock will be maintained on the record until the end of the transaction.

Longer term, if the code can be refactored, you may want to consider using a sequence instead of a field to maintain the next order number. Then you wouldn't have the record contention you have now, as you would be able to get the next number without needing a record lock.

 

[KMoody]

/* OE-01A.p */
DEFINE NEW SHARED VARIABLE flename AS CHARACTER FORMAT "X(15)".  /*  Locked File Name  */
DEFINE NEW SHARED VARIABLE prgname AS CHARACTER FORMAT "X(15)".  /*  Program Name  */
DEFINE SHARED     VARIABLE ssno    LIKE passfile.soc-sec-num.
DEFINE SHARED     VARIABLE ordno   AS INT       FORMAT ">>>>>>>".
DEFINE SHARED     VARIABLE invper  LIKE sysmstr.prod-inv-per.

prgname = "OE-01A".
DO TRANSACTION:
    REPEAT:
        FIND sysmst2 WHERE SYSMST2.coname = "Company Name"
            EXCLUSIVE-LOCK NO-WAIT NO-ERROR.
        IF LOCKED sysmst2 THEN
        DO:
            flename = "SYSMST2".
            FIND sysmst2 WHERE SYSMST2.coname = "Company Name"
                NO-LOCK NO-ERROR.
            IF AVAILABLE sysmst2 THEN
            DO:
                MESSAGE "SYSMST2 LOCKED!".
                PAUSE 15.
                NEXT.
            END.
        END.
        LEAVE.
    END.
    IF NOT AVAILABLE sysmst2 THEN
        LEAVE.

    ordno = sysmst2.nextord.
    sysmst2.nextord = sysmst2.nextord + 1.
    IF sysmst2.nextord GT 9999990 THEN
        sysmst2.nextord = 101.
    REPEAT:
        FIND FIRST custitm WHERE custitm.order-num = ordno
            NO-LOCK NO-ERROR.
        IF AVAILABLE custitm THEN
        DO:
            ordno = sysmst2.nextord.
            sysmst2.nextord = sysmst2.nextord + 1.
            IF sysmst2.nextord GT 9999990 THEN
                sysmst2.nextord = 101.
            NEXT.
        END.
        LEAVE.
    END.
    find sysmstr where sysmstr.coname = "Company Name" NO-LOCK NO-ERROR.
    invper = sysmstr.prod-inv-per.

END.

 

[KMoody]

/*MAIN.P */
DEFINE NEW SHARED VARIABLE cust   LIKE customer.cust-num.
DEFINE NEW SHARED VARIABLE ordno  AS INT       FORMAT ">>>>>>>".
DEFINE NEW SHARED VARIABLE repno  AS CHARACTER FORMAT "X(2)".
DEFINE NEW SHARED VARIABLE invper LIKE sysmstr.prod-inv-per.

/********************************** FORMS *****************************************/
FORM "Order No:" AT 1 hedord.order-num AT 11
    "Date" AT 25 hedord.order-date AT 30
    "Due Date" AT 45 hedord.due-date AT 55 SKIP(1)
    "Customer:" AT 1 cust AT 11 "Rep" AT 22
    repno AT 28 SKIP
    WITH NO-LABELS NO-BOX NO-UNDERLINE DOWN FRAME ordform.

/********************************** MAIN *****************************************/

HIDE ALL.
CLEAR FRAME ordform.

REPEAT:
    CLEAR FRAME ordform.


    MESSAGE "'F1' for assistance in finding number    'F4' To Exit".
    UPDATE cust GO-ON(F1 F4 ESC) WITH FRAME ordform.
    IF LASTKEY=KEYCODE("F4") OR
        LASTKEY=KEYCODE("ESC") THEN LEAVE.

    IF cust = ' ' THEN NEXT.   
    FIND customer WHERE customer.cust-num = cust
        NO-LOCK.
    IF NOT AVAILABLE customer THEN 
    DO:
        MESSAGE "Customer Not Found".
        UNDO, RETRY.
    END.
    FIND SALESREP WHERE SALESREP.REP-NUM = CUSTOMER.REP-NUM NO-LOCK NO-ERROR.

    /* Create the new order */
    /* Start of EXCLUSIVE-LOCK for HEDORD */       
    for each SYSMST2 no-lock:
        RUN OE-01A. /* Retrieve next order number */
        LEAVE.
    END.
        FIND CURRENT SYSMST2 NO-LOCK no-wait.
    IF ordno = 0 THEN LEAVE.
     
    CREATE hedord.
    ASSIGN 
        hedord.order-num  = ordno
        hedord.order-date = TODAY
        hedord.due-date   = hedord.order-date + 3.
    DISPLAY hedord.order-num hedord.order-date hedord.due-date
        WITH FRAME ordform.
    UPDATE repno WITH FRAME ordform.
    


END.    /*   END OF MAIN REPEAT BLOCK   */

Apologies for the double post. This was the only way I could get the CODE tags to work.

 

[Cringer]

My suggestion is you split this into 2 transactions. One would be small, to get the next value. You store that in a variable. Then you do your other transaction and use the value.
That being said, using a table for this is a bad idea. You will always have locking issues. A sequence is easy to use and removes these issues, as Rob has already mentioned.

 

[KMoody]

Thanks, everyone. I agree that managing order numbers with a sequence is better than using a table.