4GL/ABL: How to Implement the Optimistic Locking Strategy to manage record locking in a Multi-User Environment?

4GL/ABL: How to Implement the Optimistic Locking Strategy to manage record locking in a Multi-User Environment?
How is the FIND CURRENT statement used in the implementation of the Optimistic Locking Strategy?
How are the AVAILABLE, LOCKED and CURRENT-CHANGED functions used in the implementation of the Optimistic Locking Strategy?
Sample code implementing the 'Optimistic Locking Strategy' to improve concurrency and eliminate record contention errors in a Multi-User Environment.

When developing a 4GL application for deployment in a multi-user environment, you will want to design a record locking strategy that avoids keeping records locked unnecessarily for long periods of time.  The Optimistic Locking Strategy is recommended to accomplish this objective.  Optimistic Locking is a strategy whereby records are initially accessed using NO-LOCK, with an EXCLUSIVE-LOCK used afterwards for a very short period of time.

This example runs 2 sessions against the Sports2000 Data Base.

In the first session, procedure 1 accesses a record with EXCLUSIVE-LOCK and procedure 2 accesses the same record using Optimistic Locking Strategy.  In the second procedure, notice the use of the NO-LOCK NO-ERROR NO-WAIT options of the first FIND and the use of the FIND CURRENT EXCLUSIVE-LOCK NO-ERROR NO-WAIT of the first FIND.  Also, notice the roles played by the AVAILABLE, LOCKED and CURRENT-CHANGED functions in the implementation of this Optimistic Locking Strategy:

// procedure 1, sit in a lock

FIND customer WHERE customer.custnum = 1 EXCLUSIVE-LOCK NO-WAIT NO-ERROR.

// if available, sit in the update statement (transaction active)

IF AVAILABLE customer THEN 
    DO:
        DISPLAY customer.custnum customer.name WITH 1 COLUMN.
        UPDATE customer.name.
    END.
    
PAUSE.

 

// procedure 2, Optimistic Locking Example

FIND customer WHERE customer.custnum = 1 NO-LOCK NO-ERROR.

IF AVAILABLE customer THEN
    DISPLAY Customer.custnum customer.NAME WITH 1 COLUMN.

PAUSE.

MESSAGE "Do you wish to change this record?" VIEW-AS ALERT-BOX QUESTION BUTTONS YES-NO UPDATE upd AS LOGICAL.

IF upd THEN 
    DO:
        // try getting a lock on the record
        FIND CURRENT customer EXCLUSIVE-LOCK NO-ERROR NO-WAIT.
        IF AVAILABLE customer THEN 
            IF CURRENT-CHANGED customer THEN 
                DO:
                    DISPLAY Customer.custnum customer.NAME WITH 1 COLUMN.
                    UPDATE customer.NAME.
                END.
            ELSE 
                UPDATE customer.NAME.
        ELSE 
            IF NOT AVAILABLE customer THEN 
                IF LOCKED customer THEN 
                    MESSAGE "This Record is Locked by another user" VIEW-AS ALERT-BOX.
                ELSE 
                    MESSAGE "This record has been deleted" VIEW-AS ALERT-BOX.
    END.