This is the first in a series of TechTips about record lock issues. Most iSeries shops deal with record lock problems occasionally, and too many of us view those as unavoidable problems whose only solution is to occasionally beat users upside the head with a stick to get them to log out of their maintenance programs. As appealing as that may be, it's neither the best solution nor the only solution.

I've seen examples of how to detect record lock and respond to it. But for some reason, we don't seem to realize that we have the ability to prevent record lock errors. That's right--not just an after-the-fact error-trapping scheme, but a proactive programming technique that actually prevents record lock errors from happening in the first place!

The root of all record lock errors is the long pause between the read of the file (for update) and the write. This pause frequently takes the form of an interaction with the user, most likely an EXFMT, as shown in Figure 1.

Figure 1: This typical program logic leads to record lock errors.

If the user is active and using this program as the developer expects, there's no problem. If a second user needs update access to the same master record, our user has about a minute to complete his work on this screen before an error is issued. The second user can simply wait politely until the record frees up and the program continues.

If only things actually worked this way! Unfortunately, the program is designed for the programmer's utopian, unrealistic idea of how the user will work. In reality, before completing his work, the user may go to lunch, a meeting, or even home. The record can remain locked for hours, and batch-intensive processes that run at night are often the victim of these record locks. We can easily remove the record lock issue by modifying the code (Figure 2).

Figure 2: This program logic has no record lock (bad idea).

This solution is easy enough; simply use the "N" operation extender on the first chain to avoid locking the record. Now, the user can leave the screen up for a week and never create a record lock. Then, before we update the record, we read it again, this time without the "N" operation extender so that we can lock the record for update a nanosecond before issuing the update operation.

But this is a bad idea because the purpose of a record lock is to play "traffic cop" and prevent other users' changes from being overwritten, thereby corrupting our database. With this design, there is no traffic cop. To turn this bad design into a good one, we need to add logic that performs the traffic cop role.

There are several techniques for doing this; the easiest is to use an externally defined data structure to hold the fields from the database along with a work field to hold a snapshot of the data (Figure 3).

Figure 3: Here's your program logic without a record lock and with the traffic cop logic.

When the screen fields are loaded, the contents of each field in the file are copied from the RlMast data structure to the SsMast work field. Then, immediately before the record is updated, the current contents of the file fields (in RlMast) are compared to the snapshot held in SsMast. If they aren't the same or if the record can't be found, then someone has changed the data while our user was looking at the screen, and an error must be returned to the user. Otherwise, the program can continue as normal.

Although unsophisticated, this solution will work and can be implemented one program at a time.

Other record lock issues will be discussed in later TechTips.

Kevin Forsythe, author of SQL for eServer i5 and iSeries, has over 18 years of experience working with the iSeries platform and its predecessors. He has been a member of the DMC team for the past nine years. Kevin's primary responsibility is providing iSeries education, but he also provides customers with project management, system design, analysis, and technical construction. In addition to his technical skills (RPG IV, CL, OS/400, SQL, FTP, Query, VB, Net.Data), Kevin possesses the ability to communicate new and complex concepts to his students. He has been the primary instructor for DMC's iSeries-based AS/Credentials training courses since 1997 and has authored courses such as Advanced ILE, SQL, Embedded SQL, Operations Navigator, and Intro to WebSphere Studio. An award-winning speaker, he has spoken at every COMMON Conference since the spring of 2000.