Note: This month’s tip is an update to a blog post originally made a few years ago, when integrated record locking functionality was first incorporated into the Valence RPG toolkit (in version 3.2.20130912.0).
When developing apps in which users will be editing the contents of an order or master table record, you have to consider the possibility that multiple users may occasionally attempt to edit the same record or entity at the same time. This is where the domain of “record locking” comes into play. Conventionally, the two most common approaches to handling record locks are referred to as optimistic and pessimistic locking.
With optimistic locking, changes are committed to the database whenever a change is saved, without regard to the possibility that someone else may be maintaining the same record at the same time. Conversely, pessimistic locking prohibits more than one user from being in “edit mode” on a given record or entity at any time. As a developer, you need to decide which approach is most appropriate for the applications you’re developing.
Most elements in the Valence Portal Admin app utilize the optimistic model, meaning whichever user saves their changes last on a given record gets their changes to “stick” (the lone exception being Category Maintenance – only one user is allowed to edit a category at a time). Generally this is an acceptable approach for rarely-edited master tables such as these, as it’d be unlikely for more than one user to be maintaining the same record at the same time, and it removes the hassle of having separate “view” and “edit” functionality in the maintenance screens. In the rare case where two users are editing the same record at the same time, in all likelihood the changes being made are minor and often would be the same for both users anyway (i.e., updating a user’s email address). It’s also relatively easy to add logic to warn the user when the record they’re about to save has been changed elsewhere while they were editing it.
However, when it comes to maintaining elements such as customer orders, purchase orders, work orders, etc., having two people editing the same record or entity at the same time is generally an undesirable condition. In such cases, a pessimistic record locking scheme is generally the better approach, so that any attempt to go into edit mode on an order being maintained elsewhere would be trapped, generating an “Order in use” error for the user. In the IBM i world, many order header physical files include a special field to identify records that are in use for maintenance. Typically the field consists of a 10-character user ID and/or 5250 workstation ID. But what happens when you move some or all of your order entry functions from the green screen realm to the web world? Particularly during a transition period, where both green screen programs and web apps may be used to maintain the same database, how can you accommodate the possibility of a user ID or session ID that exceeds the size constraints of the 5250-centric fields you’ve been using in the past? And how do you handle situations where a user closes their browser or navigates to another page while they’re in the middle of editing an order? These are issues nearly all RPG developers on IBM i face as they work to retrofit or reengineer their green screen order entry programs into modern Valence apps.
To assist with this process, the Valence service program includes several VVUTILITY procedures to accommodate “soft” record locks that are linked to the user’s session ID. The locking is accomplished by using a dedicated table, VVLOCKS, as a standalone locking matrix to hold record-in-use info for any file. The locking-related procedures are: • vvUtility_lock() – Attempts to lock a record • vvUtility_unlock() – Unlocks a record • vvUtility_isLocked() – Checks to see if a record is locked Here’s how it works: Using customer order entry as a hypothetical example here, let’s say a user wants to start editing an order, so they click on an “Edit” button on the front-end. Before granting them access to edit mode, your back-end RPG program would attempt to lock the customer order record by using vvUtility_lock. This procedure will return a boolean value of “1” (or *ON) if the lock is successful, or “0” (*OFF) if not, in which case the user should get an “order is in use” error message. The parameters for the vvUtility_lock procedure are as follows: Mandatory Parameters for vvUtility_lock():
- File Identifier (up to 20A) – a string identifying the entity being edited. This could be the actual name of your order header file (i.e., ORDHDR), or simply something like “CustomerOrder”. Just be sure you’re consistent with your naming scheme across all your programs.
- Record Identifier (up to 256A) – a string uniquely identifying the record to be maintained (i.e., “order_nbr_16529” or just “16529”). Again, consistency is key.
- Program Identifier (20A) – a string denoting the program that will be handling the editing (i.e, “Order Entry” or “RPG960” or what-have-you). Note that if the record to be locked is already in use, this field will return the name of the program that established the lock.
- Session ID (64a) – the Valence session ID . If the record to be locked is already in use, this field will return the session ID currently holding the lock.
Optional Additional Parameters for vvUtility_lock():
- Valence user ID (10i0) – if the record cannot be locked because it’s already in use, this parameter will contain the numeric Valence user ID that is holding the lock, corresponding to a record in VVUSERS. You can use this to get the full name of the user editing the record, which may be useful for including in an error message.
- Timestamp (z) – if the record cannot be locked because it’s already in use, the parameter will contain the date and time the existing lock was established. Used in conjunction with the user ID field, you can create an error message that says something like, “Record has been in use by John Doe since 11:40”.
- Note (100A varying) – if desired, you can pass a note with additional technical info about the lock. When a record cannot be locked because it’s already in use, any note originally associated with the lock will be returned in this field.
- Allow Relock (n) – This is a boolean control field for the locking process. If passed and set to “1” (or *ON) then an attempt to lock a record that’s already flagged as in use by the same program and session ID passed above will be allowed — essentially a “relock” of the same order. If not passed or set to “0” (or *OFF) then a relock is not permitted, and the procedure will return *OFF if the same program and session is found to be already maintaining the record.
In a typical RPG program, the code for locking a customer order record might look something like this:
d OrderNbr s 20a
d pgmName s 20a
d sessID s 64a
d user s 7s 0
d lockTstamp s z
/free
pgmName = ’Order Entry’;
orderNbr= %char(vvIn_num(‘orderNbr’));
sessID = vvIn_char(‘sid’);
If not vvUtility_lock(‘CustomerOrder’:
OrderNbr :
pgmName :
sessID :
user :
lockTstamp);
// record is locked – tell user who the culprit is...
chain user vvusers;
vvOut_toJSONpair(‘success:false,msg:Cannot edit – order in use ’+
‘in ‘+%trim(pgmName)+’ program ‘+
‘since ‘+%char(lockTstamp)+’ ‘+
‘by ‘+%trim(vvfname)+’ ‘+%trim(vvlname));
else;
// proceed with edit process...
//
endif;
If your order entry file has a special locking field in it as well, it’s generally a good idea to update that field with a value to retain your original locking logic for any other programs that look at it. One common convention is to put *VALENCE in the field while editing the order, then blank it back out once the order is updated. To unlock the record, simply call the vvUtility_unlock procedure with the same file identifier, record identifier, program and session ID used to lock it, something like this:
vvUtility_unlock(‘CustomerOrder’:
orderNbr :
pgmName :
sessID);
As an added bonus, since this process is built into the Valence Portal, whenever a Valence session logs out, or the session times out, Valence will automatically clean up any record locks associated with the terminated session ID. This also accommodates scenarios where the user closes their browser or navigates away from the page while in the middle of maintaining an order, since the Valence Portal automatically logs out the session when that occurs. This greatly reduces the incidence of “orphaned” record locks — one less thing for your IT staff to worry about! Keep in mind that if your order entry physical file includes a field for denoting when a record is locked (i.e., where you’re storing *VALENCE while the order is being maintained, as described above), you’ll need a special provision to reset that field in cases where the user has terminated the session abnormally. This can be accommodated through an exit program associated with the logout routine, which — as of the latest 4.2 maintenance release — is called prior to housekeeping being done on the locking matrix file, VVLOCKS. Using RPG program EXEXITPGM as a template, the following code would be an example of clearing out a locking field called INUSEBY in an order entry file called ORDHDR:
if vvExitMode='LOGOUT';
// reset order-in-use field for any records associated
// with the session being logged out or terminated...
exec SQL update ORDHDR
set INUSEBY=' '
where INUSEBY='*VALENCE' and
char(ORDNBR) in (select lock_rec
from VVLOCKS
where lock_file='ORDHDR' and
lock_sess=VVSID);
endif;
Be sure to include your exit program name in the “Logout” section of Portal Admin > Settings > Exit Programs so it will be called whenever a session ends.