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).

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 simply wait for the other use to finish and unlock the record. The computer running the procedure will be frozen until the other user finishes (actually only Panorama is frozen, other applications work fine). When the record is available, Panorama will lock the record on this machine and continue as described in the previous paragraph.

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. (If another user (or another procedure equation) has already locked this record, the procedure will stop and wait for this user to finish and unlock the record. The user running the procedure will be frozen until the other user finishes.) When the calculation is complete, the procedure stores the new Qty value and unlocks the record. (If you don’t like the part about Panorama becoming temporarily “frozen”, continue on to the next section for a solution.)

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. To accomplish these goals, the programmer can use the LockRecord, LockOrStop and UnlockRecord statements.

The LockRecord statement attempts to lock the current record. If the current record is not already locked by someone else on the network, the record is locked and the procedure continues. If the current record is already locked, the procedure 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.

local ReceivedQuantity
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

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.

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.

Here is an example of a procedure that explicitly locks and unlocks the current record to get around the frozen user problem mentioned in the last section. In this example, the user is not frozen if another user has locked the record, they simply see an error message.

local ReceivedQuantity
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

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


History

VersionStatusNotes
10.2NewNew in this version.