Managing record locking

Introduction

Record locking controls the access and maintains the integrity of the data within the record. A record-locking conflict occurs when multiple users try to access the same records in a file for update, or when a user or users try to read a record, and another user has the record locked for update.

This topic discusses record-locking conflicts and how to prevent and manage them.

Locking conflict example

The example below shows two requests that cause a record-locking conflict; user #2 cannot update the record until user #1 releases it.

Request #1:

BEGIN %NAME = 'HADRIAN WALL' FD.NAME: IN EMPLOYEES FIND ALL RECORDS FOR WHICH EMPLOYEE = %NAME END FIND FOR EACH RECORD IN FD.NAME PRINT JOB.TITLE AT 10 AND DIVISION AT 20 END FOR END

Request #2:

BEGIN %NAME = 'HADRIAN WALL' %JOB = 'HRD REP' FD.NAME: IN EMPLOYEES FIND ALL RECORDS FOR WHICH EMPLOYEE = %NAME AND JOB.TITLE = %JOB END FIND FOR EACH RECORD IN FD.NAME CHANGE JOB.TITLE TO 'SR. HRD REP' END FOR END

In the example shown above, user #2 receives the following message:

RECORD ENQUEUING CONFLICT DO YOU WANT TO CONTINUE?

Types of record locks

Locking, or enqueuing, can occur in a variety of circumstances depending on the type of file activity:

• A share lock (SHR) occurs for users with read access. All users can read a record, but no user has update access to it.
• An exclusive lock (EXC) occurs for users who are updating records. When records are exclusively locked, no other user has any access.

User Language statements create record locks as described in the following table.

User Language locks

UL Statement	Non-TBO Files	TBO Files
FIND	SHR LOCK on found set	Same
FIND AND PRINT COUNT	SHR LOCK on found set	Same
FIND AND RESERVE	EXC LOCK on found set	Same
FIND WITHOUT LOCKS	No lock	Same
CHANGE fieldname ADD fieldname INSERT fieldname DELETE fieldname	EXC LOCK on current record in FOR loop until end of current loop	Additional EXC LOCK until COMMIT
DELETE RECORD	EXC LOCK on current record in FOR loop until end of current loop	Additional EXC LOCK until COMMIT
DELETE RECORDS IN	EXC LOCK on found set of records from the FIND	Same
STORE RECORD	EXC LOCK on single record	Additional EXC LOCK until COMMIT
PLACE RECORDS ON LIST	No lock	Same
SORT RECORDS	No lock	Same

Using ON units

ON units, which allow you to perform an action on certain conditions, can be used for a number of different purposes within Model 204 code.

Two ON unit statements can be used in situations where record-locking conflicts might occur:

ON FIND CONFLICT	Handles conflicts that occur during FIND or FOR EACH RECORD statements when used to retrieve records.
ON RECORD LOCKING CONFLICT	Handles all types of conflicts that might arise during an attempt to lock records.

To help manage record-locking conflicts, include either an ON RECORD LOCKING CONFLICT or an ON FIND CONFLICT unit in every request in which you issue a FIND. If a procedure contains both ON RECORD LOCKING CONFLICT and ON FIND CONFLICT units, the ON FIND CONFLICT statement takes precedence for FIND conflicts.

If a FIND statement fails, that is, triggers an ON unit, no locks are held for that statement. Therefore, if an ON FIND CONFLICT is triggered during a group find, record locks are released for all files in the group.

ON RECORD LOCKING CONFLICT units can be reset throughout a program to handle conflicts in different ways. Remember that the last executed ON unit remains active until either the next ON unit is executed or the end of the request is reached.

Use ON FIND CONFLICT or ON RECORD LOCKING CONFLICT to ensure that any attempt by a FIND statement to get share access triggers the ON unit, if the records desired are already held in exclusive status by some other user. The records are held in share status during FOR EACH RECORD loop processing unless they are updated, in which case the lock is changed to exclusive.

If the records are to be updated, it is recommended that you use an ON RECORD LOCKING CONFLICT unit, because ON FIND CONFLICT is not triggered when the share lock is changed to exclusive.

In the following example, Model 204 places the found set of records in share status but allows other users shared access as well. As a result, no one user can gain the exclusive access needed for updating. This method guarantees that data in these records cannot be changed while this procedure examines and displays them. When using this method, be sure to release the records as soon as possible to allow access by other users.

BEGIN SET HEADER 1 'RECORD STATUS REPORT' SET HEADER 2 NEW PAGE . . . ON FIND CONFLICT AUDIT 'LOCK CONFLICT ' WITH $RLCFILE AND $RLCUSR AND $RLCREC READ OFC ********************************** ** WHERE OFC IS A SCREEN DEF ** ** INCLUDED IN PROCESSING ABOVE ** ********************************** IF %OFC:ANSWER EQ 'Y' THEN RETRY ELSE JUMP TO FINISH END IF END ON FD.ACCT: FIND ALL RECORDS END FIND CT.ACCT: COUNT RECORDS IN FD.ACCT IF COUNT IN CT.ACCT EQ 0 THEN JUMP TO FINISH END IF FOR EACH RECORD IN FD.ACCT PRINT ALL INFORMATION END FOR RELEASE RECORDS IN FD.ACCT SKIP 2 LINES PRINT 'END OF REPORT' FINISH: END

The next example still provides read-only access, but the records can be deleted or field values changed between the time the FIND statement is issued and the current time of processing.

Note: The IF statement is used within the FOR EACH RECORD loop to validate that the record has not been deleted and that the original FIND criteria have not changed. If changes have occurred, then the body of the IF statement is not processed.

BEGIN . . . ON FIND CONFLICT AUDIT 'RECORD LOCKING CONFLICT CANCELLED ' WITH - $RLCFILE AND $RLCUSR AND $RLCREC JUMP TO PRT.ERROR END ON FD.ACCT: FIND ALL RECORDS FOR WHICH ACCT = '23643' END FIND CT.ACCT: COUNT RECORDS IN FD.ACCT IF COUNT IN CT.ACCT EQ 0 THEN PRINT 'NO RECORDS FOUND' JUMP TO FINISH END IF *************************** ** PUT RECORDS ON LIST ** *************************** PLACE RECORDS IN FD.ACCT ON LIST ACCTS ******************** ** RELEASE THEM ** ******************** RELEASE RECORDS IN FD.ACCT FOR EACH RECORD ON LIST ACCTS IF TYPE EQ 'BILLABLE' THEN PRINT NAME %CALC = %CALC + AMOUNT + 2 PRINT %CALC TO 20 END IF END FOR JUMP TO FINISH PRT.ERR: PRINT 'YOUR REQUEST IS CANCELED' WITH 'DUE TO A RECORD LOCKING CONFLICT' FINISH: END

Using FIND AND RESERVE

The FlND AND RESERVE statement locks the current set of records in exclusive mode and therefore is recommended when executing update requests.

However, it is recommended that you observe the following prohibitions:

• Do not reserve records across terminal I/O (READ SCREEN, for example).
• Do not reserve records during single-threaded (batch) updating.

To update a large set of records without locking the entire set, place the records to be updated on a list, and then issue a FIND AND RESERVE on each record as you update it. Whenever you use the FIND AND RESERVE statement, be sure to release the records with one of the following statements:

• RELEASE
• COMMIT RELEASE

Coding RETRY counters

If a FIND AND RESERVE statement encounters a locking conflict, the ON unit can either retry or end the request. If you use RETRY, use it with a counter. Increment the counter each time you retry, and cancel the request after a certain number of times. This prevents an infinite retry loop.

If you use a RETRY counter more than once in a program, remember to reset the counter to zero before each FIND AND RESERVE statement. If the FIND is within an outer FIND loop, you must create a new counter to monitor these iterations as well.

The following example shows the code for a screen that appears when Model 204 encounters a record-locking conflict. This example sets a RETRY counter and cancels the RETRY after a specified number of attempts.

BEGIN ******************************************************* ** IN CASE OF RECORD CONFLICT ASK USERS IF THEY WANT ** ** TO TRY AGAIN. CANCEL AFTER 25 USER ATTEMPTS. ** ** AUDIT EACH ATTEMPT TO THE JOURNAL. ** ** ON RECORD LOCKING CONFLICT SCREEN ** ******************************************************* SCREEN ORLC TITLE 'UNABLE TO ACCESS REQUESTED RECORDS' AT 24 BRIGHT SKIP 3 LINES PROMPT 'CONFLICTING USER:' PROMPT USER PROMPT ' FILE:' PROMPT FILE PROMPT ' RECORD:' PROMPT RECORD SKIP 2 LINES PROMPT 'DO YOU WANT TO TRY AGAIN?' INPUT TRY ONEOF Y,N DEFAULT 'N' END SCREEN ORLC: ON RECORD LOCKING CONFLICT ************************** ** WRITE LOG TO AUDIT TRAIL ** ******************************** %RECORD = $RLCREC %FILE = $RLCFILE %USER = $RLCUSR AUDIT 'RECORD LOCKING CONFLICT DURING DEPT PROCESSING ' WITH %RECORD AND %FILE AND %USER ******************* ** SET COUNTER ** ******************* %COUNTER = %COUNTER + 1 IF %COUNTER EQ 25 THEN BACKOUT PRINT '**** REQUEST CANCELLED ****' %COUNTER = 0 JUMP TO FINISH END IF ************************************** ** ASK IF USER WANTS TO TRY AGAIN ** ************************************** %ORLC:RECORD = %RECORD %ORLC:FILE = %FILE %ORLC:USER = %USER READ SCREEN ORLC IF ORLC:TRY EQ 'Y' THEN RETRY ELSE PRINT '**** YOUR REQUEST HAS BEEN CANCELLED****' JUMP TO FINISH END IF END ON *************************************** ** FIND ACCT RECORDS FOR EACH DEPT ** *************************************** FD.ACCT: FIND AND RESERVE ALL RECORDS FOR WHICH - DEPT = ACCOUNT END FIND PROCESS: FOR EACH RECORD IN FD.ACCT CHANGE CORRECTED TO 'YES' END FOR FINISH: END

Ensuring file integrity

If you are updating from records placed on a list, you can help ensure file integrity, and maintain the logical consistency of your data, by keeping the user ID of the person who performed the last update on record. Check to see that no other user has updated the record while you are updating.

In the following example, one record at a time is displayed for update. After the record is changed, the update ID is checked. If it is not the number expected, an error message is issued.

BEGIN . . . FD.VIN: IN VEHICLES FD VIN = 123456789 END FIND IN VEHICLES CLEAR LIST VIN PLACE RECORDS IN FD.VIN ON LIST VIN RELEASE RECORDS IN FD.VIN UPD.SCR: FOR 1 RECORD ON LIST VIN %UPDATEID = UPDATE ID ************************ ** FILL SCREEN HERE ** ************************ END FOR IF $CHKTAG ('SCR.UPD.VIN') THEN REREAD SCREEN SCR.UPD.VIN ELSE READ SCREEN SCR.UPD.VIN NO REREAD END IF . . . ************************ ** EDIT SCREEN HERE ** ************************ FDR.VIN: FDR VIN = 123456789 END FIND FR FDR.VIN IF UPDATE ID = %UPDATEID THEN CHANGE UPDATE ID TO UPDATE ID + 1 CHANGE ...(other record fields) . . . ELSE %SCR.EMSG = 'RECORD CONCURRENTLY UPDATED ' - WITH 'REDO UPDATES ON REFRESHED RECORD' IN VEHICLES CLEAR LIST VIN PLACE RECORDS IN FDR.VIN ON LIST VIN RELEASE RECORDS IN FDR.VIN JUMP TO UPD.SCR END IF END FOR COMMIT RELEASE END

Controlling record locking problems

To control record-locking problems, keep in mind the following guidelines:

• You can avoid many conflicts by releasing records as soon as you no longer need them.
• Use a commit statement at the end of every update unit.
• If possible, keep update units within the same terminal I/O point.
• In a multi-request application, segregate updating functions from read-only functions.
• To free pages in CCATEMP, use the CLEAR LIST statement when you no longer need the records in a list.
• Perform REDEFINE, RENAME, and DELETE FIELD functions during off-peak hours.
• When not using the application subsystem facility, set the ENQRETRY parameter to control the number of retries before a message is generated.
• Place the records you need on a list, release the records, and process from the list in situations where no updates are taking place or where updates are known not to affect the data in question.
• Defer index updates whenever possible.

For more information on file management, see the Model 204 file management wiki pages.