Prevent the most common cause of record lock errors so that you can sleep through the night.

As long as we've written programs that update data files, there has been record lock, and as long as we continue to update data files, there will be record lock. But that doesn't mean that you have to get that 3:00 a.m. wake-up call when your nightly backups or day-end process failed because a user left a maintenance screen up that left a record locked, which is now causing you to get up in the middle of the night and figure out what happened and how to fix it.

Don't you wish you could keep that error from happening? Well, you can. I've spoken at many conferences about techniques you can use to avoid record lock errors. In this article, I'll discuss the most significant part of that.

The presentation that I give on this subject is titled "Get Rid of Record Lock." But in reality, we aren't getting rid of it completely, just managing it better. Some of the techniques that I discuss have probably been in use for 20 years or more and were given to me by veteran RPG programmers. Despite the fact that very easily implemented solutions have been around for such a long time, far too many RPG shops have never implemented any solution to this problem.

To understand how to avoid record lock, you need to first understand why record lock exists. IBM didn't incorporate record lock into RPG just to give us something to do at 3:00 a.m. on Saturday morning; it's there for a serious reason. It prevents data loss/corruption. Without it, any number of programs could be writing their data to the database, oblivious of the changes other programs made. So, if we choose to avoid record lock, we must take on the responsibility to prevent data loss/corruption.

There is more than one way to avoid record lock errors, but all of them involve the same basic principles:

1.      Records are only locked immediately before they are updated.

2.      Before performing an update, verify that the record remains unchanged.

To accomplish step 1, we'll stop locking the record when we load the data into output displays. The long wait while the user interacts with the display is one of the most likely causes of record-lock problems.

Step 2 can be accomplished in a number of ways, but my preferred method for doing this is to use a data structure that contains a list of relevant data fields. These fields can be some or all of the columns in the file being updated and might also include fields in other files.

Now, let's look at the code. Here is the complete code to a test program that illustrates this technique. The sample is a bit simplified, but it provides a working model for you to use.

FMyScreen  CF   E             WORKSTN  PREFIX(S_)      

FmyFile    UF   E           K DISK                     

D I_Ptr           S               *    INZ(%ADDR(*IN)) 

D I_Indicators    DS                   BASED(I_Ptr)    

D   F3_Exit               3      3N                     

D   F12_Cancel           12     12N                    

D   Err_Flags            97     98                     

D   Err_Update           97     97N                    

D   Err_Key              98     98N                    

D I_Data        E DS                   ExtName(MyFile) 

D H_Data          DS                   LikeDS(I_Data)  

 /FREE                                                  

  DOU F3_Exit;                                         

    EXFMT SCRN01;                    

    Err_Flags = *OFF;                

    CHAIN(N) S_KeyField MyFile;     

    IF F3_Exit;                      

       LEAVE;                        

    ENDIF;                           

    IF %FOUND;                     

       EXSR SCREEN02;                

    ELSE;                            

       Err_Key = *ON;           

    ENDIF;                      

  ENDDO;                        

  *INLR = *ON;                  

  RETURN;                       

  BEGSR SCREEN02;               

    S_DataFld1 = DataFld1;      

    S_DataFld2 = DataFld2;      

    S_DataFld3 = DataFld3;      

    H_Data = I_Data;            

  DOU Err_Update = *OFF;        

    EXFMT SCRN02;               

    Err_Flags = *OFF;           

    IF F3_Exit OR F12_Cancel;   

       LEAVE;                   

    ENDIF;                      

    CHAIN(E) KeyField MyFile;

    IF NOT %ERROR AND %FOUND 

       AND I_Data = H_Data;  

       EXSR UPDATE;          

    ELSE;                    

       Err_Update = *ON;     

       IF %FOUND;            

          UNLOCK MyFile;    

       ENDIF;                 

    ENDIF;                   

  ENDDO;                     

  ENDSR;                     

  BEGSR UPDATE;              

    DataFld1 = S_DataFld1;   

    DataFld2 = S_DataFld2;   

    DataFld3 = S_DataFld3;   

    UPDATE(E) MyRecord %FIELDS(DataFld1:DataFld2:DataFld3);  

    IF %ERROR;                              

       Err_Update = *ON;                    

    ENDIF;                                  

  ENDSR;                                    

 /END-FREE                                  

Figure 1: Complete Program Code

Next, we'll take a closer look at key portions of this code.

Figure 2 contains the first section of the code that we need to look at: the CHAIN to MyFile after screen 1 is processed. In many legacy programs, this CHAIN might be allowed to lock the record while it's reviewed on screen 2. But in this program, the "(N)" operation extender prevents the CHAIN from locking the record.

    CHAIN(N) S_KeyField MyFile;     

Figure 2: Chain with No Lock (N) Operation Extender

The next significant section of code appears in subroutine Screen02. This code, shown in Figure 3, loads the data from the file fields into a holding data structure shown in Figure 4. This data structure retains an image of what the data in the file looked like when the screen was loaded.

    H_Data = I_Data;            

Figure 3: Load Holding Data Structure

D I_Data        E DS                   ExtName(MyFile) 

D H_Data          DS                   LikeDS(I_Data)  

Figure 4: Data Structures to Capture File Data and Hold It

After the user presses Enter on Screen 02, the program continues. One of the first things the program must do is CHAIN to the record again, and this time it must lock the record so that it's able to perform an update. Figure 5 illustrates how, after that CHAIN, the program verifies that the CHAIN succeeded and that the data was not changed by another program while it was being reviewed by this program's user.

    CHAIN(E) KeyField MyFile;

    IF NOT %ERROR AND %FOUND 

       AND I_Data = H_Data;  

       EXSR UPDATE;          

    ELSE;                    

       Err_Update = *ON;     

       IF %FOUND;            

          UNLOCK MyFile;    

       ENDIF;                

Figure 5: CHAIN to Lock Record and Error-Checking

If no errors occurred, the UPDATE proceeds. Otherwise, an error flag is turned on and the record is unlocked if necessary. The program must then send a message to the user, indicating that the update failed because the data changed while the user was looking at it.

This technique will prevent the most common cause of record lock errors. If you use this technique, interactive programs will no longer create record locks while waiting for user input.

For simple programs, the I_Data externally described data structure will work fine. But for more-sophisticated programs, you may need to manually define the subfields of that data structure and include fields from multiple files and possibly even fields that you're not updating. Any field that is either displayed to users and might influence their decisions or that is involved in calculations made by your program should be a candidate for inclusion the in I_Data data structure.

While this technique alone will not completely get rid of record lock errors, it is the first and most important step in that process. Once you've implemented this technique in your interactive programs, you can pursue solutions to similar issues with batch programs and even embedded SQL statements.