Record Locking in Procedure Code

Procedures often modify the data in a database. When a procedure modifies data in a shared database record locking is in effect just as it is when data is modified manually.

### Implicit Record Locking

As a procedure runs, Panorama will automatically lock and unlock records as necessary. This is called implicit record locking. Implicit record locking follows two simple rules — 1) The record is locked whenever any field in the record is about to be modified, and 2) The record remains locked until the procedure moves to another record, moves to another database, saves, or explicitly unlocks the record (see next section). It will also automatically unlock if the procedure ends with a “secret” window active.

The most common method for a procedure to modify a record is with an equation. For example, consider the equation below which adjusts the quantity level in an inventory database. This example assumes that Qty is a numeric field in the current sharable database, and SaleQty is a field or variable that contains a numeric value.

    Qty=Qty-SaleQty

When Panorama is running a procedure and encounters an equation that modifies a field in a sharable database it first attempts to lock the current record (if it is not already locked). To do this, Panorama contacts the server to see if anyone else has locked this record. If not, the server responds that everything is ok and updates the record that is about to be locked (see Editing Data and Record Locking). It then calculates the formula and modifies the Qty field. Since the data in the local copy of the record is updated as part of the record locking mechanism, the formula result is guaranteed to be based on the most recent data. (For example suppose the quantity starts at 100. Then Bob subtracts 11 and Kelly subtracts 14. The final quantity will always be 75, not 89 or 86.)

But what if the record is already locked by another user on the network? In that case the procedure will stop with an error. If you don’t want it to stop, you can trap the error with if error or the try statement (see Error Handling), for example:

    Qty=Qty-SaleQty
    if error
        ... code to deal with the fact that record is locked
    endif

Here is the alternate method using the try statement.

    try
        Qty=Qty-SaleQty
    catch
        ... code to deal with the fact that record is locked
    endcatch

Before this equation begins running, Panorama automatically locks the current record. That way another user cannot change the Qty value while the equation is being calculated, possibly causing a calculation error. When the calculation is complete, the record remains locked until Panorama moves to a different record (or you can explicitly unlock the record as described in the next section).

### Explicitly Locking/Unlocking Records in a Procedure

In the last section, you saw how a Panorama will implicitly lock and unlock a record during a procedure. However, sometimes the programmer may want to control exactly when and how records are locked and unlocked. The programmer may also want to control what happens when a record is already locked by another user.

A good method for locking in code is the lockcurrentrecord( function. This function attempts to lock the current record, and returns true if it succeeds and false if it fails (because someone else has already locked the record).

    if lockcurrentrecord()
        Quantity = Quantity-1
        unlockrecord
    else
        message "Please try again later, someone else is editing this record."
    endif

As shown in the example above, the UnlockRecord statement unlocks the current record. If the record wasn’t locked, the UnlockRecord statement does nothing. Remember, a locked record is also unlocked automatically whenever Panorama moves to another record in the current database or any other database.

#### Waiting for the Record to Unlock

If you want Panorama to wait until the record can be locked, use the LockRecord) statement. If the current record is not already locked by someone else on the network, the record is locked and the procedure continues immediately. If the current record is already locked, the LockRecord statement will wait until the record is available. While it is waiting the statement displays a dialog telling the person running the procedure to wait. If the person doesn’t want to wait, he or she can press the Cancel button. If the user presses the Cancel button the procedure stops and an error dialog appears. However, the programmer can intercept this error by putting an if error statement after the LockRecord statement. In that case when the user presses the Cancel button, the procedure will continue running with the first statement after the if error statement. Here is an example showing how this statement can be used.

    let ReceivedQuantity = ""
    gettext "How many "+Item+" were received?",ReceivedQuantity
    ReceivedQuantity = val(ReceivedQuantity)
    lockrecord
    if error
        message “These items could not be added to the database quantity. “+
            “Be sure to process them again later.“
        rtn
    endif
    Qty = Qty+ReceivedQuantity

#### LockOrStop Statement

The lockcurrentrecord( function was not available in Panorama 6 or earlier. In those older versions a similar capability was provided by the LockOrStop) statement, which is still available in today. Like the lockcurrentrecord( function, the LockOrStop statement attempts to lock the current record. If the current record is not being edited by someone else, the record is locked and the procedure continues. If the current record is already locked, the procedure stops immediately and an error dialog appears. However, the programmer can intercept this error by putting an if error statement after the LockOrStop statement.

Here is an example of a procedure that uses LockOrStop to explicitly lock and unlock the current record.

    let ReceivedQuantity = ""
    gettext "How many "+Item+" were received?",ReceivedQuantity
    ReceivedQuantity = val(ReceivedQuantity)
    LockOrStop
    if error
        message "Could not update inventory, try again later."
        stop
    endif
    Qty = Qty+ReceivedQuantity
    UnlockRecord

For new code, we recommend using the lockcurrentrecord( function instead, like this:

    let ReceivedQuantity = ""
    gettext "How many "+Item+" were received?",ReceivedQuantity
    ReceivedQuantity = val(ReceivedQuantity)
    if lockcurrentrecord()
        Quantity = Quantity+ReceivedQuantity
        unlockrecord
    else
        message "Could not update inventory, try again later."
    endif

### What Records are Locked?

The lockedrecordlist statement returns the number of records locked in the current database, and also returns a list of those records (listed by record ID number). This statement has one parameter, the name of a field or variable to receive the list. The result will be formatted like this:

    Ok: 2 records locked
    378
    864

This procedure displays the number of records locked in the current database.

    local locks
    lockedrecordlist locks
    message firstline(locks)[4,-1]

### Forcing the Server to Unlock All Records

The forceunlockallrecords statement will tell the server to unlock ALL of the records in the current database. You should only use this statement if a client has disconnected from the network or crashed, and no other clients have any locked records at the moment. If another client had locked any of these records, they will be unlocked behind their back. They will get an error message when they try to unlock the record, and their changes will not be saved. This makes most users very unhappy, so be very careful before using this command.

### Forcing the Server to Unlock a Specific Record

The forceunlockrecord statement will tell the server to unlock the current record in the current database. You should only use this statement if a client has locked this record and disconnected from the network or crashed. Make sure that you know that it is the disconnected client that locked this record. If another client that is still running is the one who locked this record, they will be unlocked behind their back. They will get an error message when they try to unlock the record, and their changes will not be saved. This makes most users very unhappy, so be very careful before using this command.

### Forcing the Server to Lock a Specific Record

The forcelockrecord statement tells the server to lock this record (the current record on the client) on the server, as if another client had locked the record. Once this is done, the only way to unlock the record is with the forceunlockallrecords or forceunlockrecord statement. This statement can be used for debugging if you don’t have two client computers available for testing.

### Temporarily Disabling Record Locking (and Server Updates)

When using a shared database, Panorama normally updates the server with full record locking every time any change is made to the database. Sometimes, however, you may want to make temporary changes to a database simply for the purposes of data analysis. For example, perhaps you need to use the formulafill statement to prepare data for printing, but will no longer need the calculated data after the report is printed. If you don’t want to keep these changes you can temporarily turn off record locking, essentially turning a shared database back into single user mode for a short while.

To temporarily disable record locking use the serverupdate statement.

    serverupdate truefalse

This statement has one parameter: truefalse. This parameter indicates whether you want to disable or enable record locking and server updates. To disable updates and record locking, use “off”, “no”, or “false”. To enable updates and record locking, use “on”, “yes”, or “true”.

The example below turns off server updates, then uses the formulafill statement to calculate P/E ratios. Since server updates (and record locking) are turned off, the formulafill will be very fast. In this case we don’t need to keep the calculated P/E ratios after we are finished printing, so it is acceptable to turn off the server update.

    serverupdate "off"
    field Ratio
    formulafill Price/Earnings
    print dialog
    serverupdate "on"
    forcesynchronize /* restore original data from server */

The last line in this example may not be necessary. If you don’t ever use this procedure for permanent changes to the database for permanent data, you can leave this line out.

You can only turn server updates off for a short time. We recommend that you explicitly turn them back on as soon as possible, but if you don’t, they will automatically turn back on when the procedure finishes, or if the procedure switches to another database (via the window or openfile statements).

#### The info(“serverupdate”) Function

The info(“serverupdate”) function can be used to check if server updates are currently enabled. The function returns the true if the server update option is currently turned on (this is the default). The example below shows how this function can be used. The procedure turns off server updates and performs a formula fill. It then turns server updates back on, but only if they were already on before this procedure began.

    let sup = info("serverupdate")
    serverupdate "off"
    field Z
    formulafill A+B
    if sup
        serverupdate "on"
    endif

If serverupdate was off before this procedure was called, it will remain off.

See Also

- Bulk Data Transformations on Shared Databases -- using formulafill, propagate, etc. in a shared environment.
- Controlling and Monitoring the Server Connection -- overriding automatic server connection behavior.
- Looking up Data Directly from the Server -- local in-memory vs. server lookups.
- Server Variables (Shared Variables) -- sharing variable values across multiple users.
- Synchronizing in Procedure Code -- customizing the data synchronization process.

History

Version | Status | Notes
10.2 | New | New in this version.