Record level locking and concurrency control

Overview

This article discusses aspects of request design related to concurrency control in a multi-user environment and presents statements and Model 204 options that can be used to ensure logical consistency.

Illustrating a concurrent update

A user executing a User Language request expects that the records retrieved will not be modified by another user's concurrent request until the user's own request has completed. For example, consider these two requests being run concurrently:

User 1

BEGIN BLUE.CARS: FIND ALL RECORDS FOR WHICH COLOR = BLUE END FIND FOR EACH RECORD IN BLUE.CARS PRINT MAKE AND COLOR END FOR END

User 2

BEGIN BLUE.BUICKS: FIND ALL RECORDS FOR WHICH MAKE = BUICK AND COLOR = BLUE END FIND FOR EACH RECORD IN BLUE.BUICKS CHANGE COLOR TO RED END FOR END

User 1 expects only the make and the color BLUE to be printed. User 2's request must be prevented from changing User 1's records before they are printed.

Record locking in Model 204

Conflicts arise when one or more users are reading a file and another user attempts to update the file or when two or more users attempt to perform file maintenance on the same records retrieved from that file. The technique used by Model 204 to prevent overlapping updates is called record level locking.

Record locking modes

Model 204 can lock records as a set of records or as a single record number. Model 204 performs record level locking in two modes:

Lock mode	Allows...
Share	One or more users to read a file. Any number of users can have shared control of a record or record set concurrently.
Exclusive	Single user to update the file. An exclusive lock is not compatible with other exclusive locks nor with any shared locks.

All records in a record set have the same locking strength: they are all locked share, or exclusive, or they are all unlocked.

FIND statement records

Records located by a FIND statement, called a record set or found set, are locked based on the FIND statement specification:

• FIND

  Locks the record set in share mode

• FIND AND RESERVE

  Locks the record set in exclusive mode

• FIND WITHOUT LOCK

  Does not lock record set

Once obtained record set locks are kept till a RELEASE or COMMIT RELEASE statement is executed or the request ends.

Record level locking evaluation rules

Request compilation and evaluation

Record-level locking is performed in the following manner. User Language requests are processed in two phases: compilation and evaluation. All requests can be compiled regardless of the operations to be performed or of other requests being compiled or evaluated at the same time. All requests are allowed to begin the evaluation stage.

When the evaluation involves records or sets of records, Model 204 automatically ensures that operations achieves the expected results by adhering to the following set of rules. The following User Language statements generate found sets that are processed as described.

FIND statement

The Find statement immediately locks the set of records it has retrieved in share mode. If the locking is successful, none of the records in that set can be updated by another user until the entire request, including request continuation(s), has been completed or the records have been released by some form of the Release statement, such as Release Records In..., Release All Records, Release Commit.

A Find statement executed in a loop releases the old found set as soon as the statement is re-executed. The final set selected by the Find remains locked until the end of the request. An unlabeled Find statement, such as a Find And Print Count statement, does not lock records at all.

FIND WITHOUT LOCKS statement

The FIND WITHOUT LOCKS statement executes a FIND statement without locking any records. The found set of records is indistinguishable from a list, except that it is referenced with an IN label clause. You must use the FIND WITHOUT LOCKS statement with caution, otherwise logical inconsistencies might occur. See FIND WITHOUT LOCKS statement for more information.

FOR RECORD NUMBER statement

The FOR RECORD NUMBER statement locks the specified record in share mode, if it was not locked by the FIND statement. The lock is kept until the end of the loop or a COMMIT statement is processed.

DELETE ALL RECORDS IN statement

The DELETE ALL RECORDS IN statement temporarily locks the set of records to be deleted in exclusive mode before deletion occurs. The locking does not succeed if another user has access to any of the records through a FIND or file maintenance statement. Once a record has been deleted, the exclusive lock on the record is released, because the record no longer exists in the file.

ADD, CHANGE, or DELETE, DELETE RECORD, and INSERT statements

The ADD, CHANGE, DELETE fieldname, DELETE RECORD, and INSERT statements all lock the current record in exclusive mode before updating it. The exclusive single record lock remains on the current record until it passes through the loop, unless the record has been deleted.

Upon completion of a DELETE RECORD statement the deleted record cannot cause a record locking conflict. However, it the deleting user attempts to reference the deleted record via a previously established found set or list, the following message is issued:

M204.1266: NONEXISTENT RECORD REFERENCED

STORE RECORD statement

The STORE RECORD statement gets a LPU record lock for the TBO files and does not get a record lock for non-TBO files.

• For TBO files, an LPU record lock, which is always exclusive, is obtained, meaning unconditionally gotten, at the beginning of the STORE RECORD processing. However, other users cannot get a record locking conflict, because the record's existence bit is not turned on until STORE RECORD processing finishes. If you store a record, you automatically obtain an LPU lock and hold it until the next COMMIT statement is processed.
• For non-TBO files, no record lock of any kind is obtained. However, the record existence bit is not turned on until STORE RECORD processing is finished, so there are no problems with protecting the record from other users during STORE RECORD processing.

END MORE statement

If a request ends with END MORE, records found by that request remain locked and cannot be modified by other users. If the request ends with END, all records are released as soon as execution is completed.

Locking conflicts

If Model 204 cannot lock a record, a locking conflict occurs, which are discussed in detail in Handling locking conflicts.

FIND WITHOUT LOCKS statement

The FIND WITHOUT LOCKS statement executes a FIND statement without locking any records. The resulting found set is indistinguishable from a list, except that it is referenced with an IN label clause.

Note: The FIND WITHOUT LOCKS feature should be used only to solve specific performance problems. Before using the FIND WITHOUT LOCKS statement, please take into account the FIND WITHOUT LOCKS statement following Responding to a record locking conflict.

Syntax

The format of the FIND WITHOUT LOCKS statement is:

{FIND WITHOUT LOCKS | FDWOL} [ALL] RECORDS [IN label | ON [LIST] listname] [FOR WHICH | WITH] retrieval-conditions

Usage notes

Issues involved with using FIND WITHOUT LOCKS include:

• Logical integrity of data is at risk when another user:
  • Is in the midst of changing values which are related
  • Changes or deletes the field which caused the record to be found
• Physical integrity error messages or snaps are generated, including:
  • SICK RECORD messages are sent when extension records are deleted. The record is not really sick; temporarily it appears that way to Model 204.
  • SICK RECORD messages are sent from FOR EACH OCCURRENCE (FEO) statements when the record is modified by another user. Again, the record is not really sick.
  • NONEXISTENT RECORD messages are sent when entire records are deleted.
• Examples of appropriate use of the FIND WITHOUT LOCKS statement include:
  • When there is one user at a time per record (for example, scratch records or bank teller applications where an account is usually modified by one teller at a time)
  • Report programs in a heavy update environment
• Examples of inappropriate uses of the FIND WITHOUT LOCKS, which can result in snaps, include:
  • Report program in a heavy delete environment (results in many NONEXISTENT RECORD messages)
  • Retrievals in which the selection criteria can be changed by other users
  • Reuse Record Number files, except possibly scratch files keyed on the user ID

Handling locking conflicts

A typical locking situation is as follows. The first user issues a FIND statement for a large set of records and begins printing a long report. The second user issues a FIND statement for some of the same records and tries to update them with a CHANGE statement.

ENQRETRY parameter

The number of times a request automatically attempts to lock a record or set of records before notifying the user of a conflict is determined by the ENQRETRY parameter. Between attempts, Model 204 waits until the record or records that were held by another user are released or until three seconds pass.

Under these circumstances, the second user must decide whether to cancel the request or try again. The second user receives a message noting that the locking failed and is queried: DO YOU REALLY WANT TO TRY AGAIN? The second user can respond in one of three ways:

• Reply N, thereby cancelling the request.
• Reply Y and try to lock again immediately.
• Wait a minute or two and reply Y.

Responding to a record locking conflict

In Model 204 you can specify the action to take if, an effort to lock a set of records is unsuccessful. The following statements specify the action to take in detail.

ON clause	Can be used for...
RECORD LOCKING CONFLICT	Any type of conflict, including a retrieval statement conflict, that arises during a record locking attempt.
FIND CONFLICT	Only a conflict that arises during the evaluation of a FIND statement or a FOR EACH RECORD statement used for retrieval.

Syntax

The format for these ON units is:

[label] ON {RECORD LOCKING CONFLICT | FIND CONFLICTS}

If both types of ON units are active

ON RECORD LOCKING CONFLICT and ON FIND CONFLICT follow the same rules as other ON units (see ON units). If both ON RECORD LOCKING CONFLICT and ON FIND CONFLICT are active within a request when a conflict occurs, the conflict is handled in the following manner:

If the conflict results from...	Clause invoked...
FIND statement or FOR EACH RECORD statement used for retrieval	ON FIND CONFLICT
A condition other than the FIND or FOR EACH RECORD statement used for retrieval	ON RECORD LOCKING CONFLICT

CLEAR ON statement

The definition of an ON RECORD LOCKING CONFLICT or ON FIND CONFLICT unit is cleared by the following statement:

CLEAR ON {RECORD LOCKING CONFLICT | FIND CONFLICTS}

After a CLEAR ON RECORD LOCKING CONFLICT or CLEAR ON FIND CONFLICT statement, a record locking conflict does not invoke the corresponding ON unit.

Pause statement

Purpose

You can use the Pause statement to cause the request to wait a specified number of seconds and then to retry the statement that caused the evaluation of the ON unit.

Syntax

The format of the Pause statement is:

Pause [n | %variable]

Where:

• n must be in the range 0-600, allowing a maximum pause of 10 minutes.
• %variable is interpreted as a numeric value representing the number of seconds to wait. It must be in the range 0-86400.

Usage

The Pause statement is a bumpable wait.

If n is not specified or is specified as zero, processing stops and does not continue until the user enters a carriage return. If n is specified, processing continues automatically after n seconds.

If %variable takes any non-null, non-numeric value or a numeric value in the range 0-86400, the SOUL request is canceled with the message:

M204.2650: PAUSE TIME-VARIABLE NOT IN RANGE

A variable with a null or 0 value does a terminal read. For example, %a='xxx' is treated as if zero was specified, and a read is issued.

Note: You should generally specify small values for n and issue a Pause only when no records are held by the request. If n is large, the request can seem to be hung.

See also

The $WakeUp function is an alternative approach; it offers millisecond resolution pausing.

Handling Parallel Query Option/204 record locking conflicts

If a client request cannot complete because of a record locking conflict on the server system, the server automatically tries again to lock the record or set of records. The server tries again until it succeeds or until it has tried as many times as the value of the client thread ENQRETRY parameter. The value of the ENQRETRY parameter that is specified on the server thread has no effect on the number of retries.

If ENQRETRY attempts to lock a record or set of records do not succeed, the server notifies the client about the conflict. If an ON RECORD LOCKING or ON FIND CONFLICT unit is active, the unit is invoked. Otherwise, the client receives a message that the locking failed, followed by a prompt asking if the client wants to try again.

If the client enters N, the request is canceled. If the client enters Y, the server repeats the locking attempt cycle, making as many as ENQRETRY attempts before prompting again.

Record locking and release statements

The following statements can place a lock on a set of records or remove the lock placed on records.

Note: To remove the lock placed on record sets, you can also use the COMMIT RELEASE for of the COMMIT statement.

FIND AND RESERVE statement

A User Language request can lock records in exclusive mode by using the FIND AND RESERVE statement. However, because records are exclusively locked, concurrency is reduced.

Syntax

The basic format of the FIND AND RESERVE statement is:

FIND AND RESERVE [ALL] RECORDS FOR WHICH

Example

BEGIN ON RECORD LOCKING CONFLICT PRINT 'RECORD LOCKING CONFLICT OCCURRED WITH ' - WITH $RLCUSR PRINT 'FILE ' WITH $RLCFILE PRINT 'RECORD ' WITH $RLCREC END ON POL.HLDR: IN CLIENTS FIND AND RESERVE ALL RECORDS - FOR WHICH POLICY NO = 100015 RECTYPE = POLICYHOLDER END FIND OWNER.POL: IN VEHICLES FIND AND RESERVE ALL RECORDS - FOR WHICH OWNER POLICY = 100015 END FIND FOR EACH RECORD IN OWNER.POL %NEW.PREMIUM = VEHICLE PREMIUM + 100 CHANGE VEHICLE PREMIUM TO %NEW.PREMIUM %TOTAL.PREMIUM = %TOTAL.PREMIUM + %NEW.PREMIUM END FOR FOR EACH RECORD IN POL.HLDR CHANGE TOTAL PREMIUM TO %TOTAL.PREMIUM END FOR END

In the preceding example, the first FIND AND RESERVE statement prevents access to TOTAL PREMIUM while its corresponding VEHICLE PREMIUMs are being changed.

RELEASE RECORDS statement

Records found using the FIND AND RESERVE statement are held in exclusive status until the end of the request or until they are released explicitly by the user with the RELEASE RECORDS statement. The RELEASE RECORDS statement also can be used to release records from SORT statements or records obtained in share status by FIND statements issued in the regular form. Records released in this manner are no longer available to the request and might have to be found again. The original found set or list from which the sorted set was built is not affected.

Note: To avoid producing confusing results, issue a RELEASE RECORDS statement at the end of a loop and not in the middle of one, since the statement relinquishes control of a found set.

Syntax

The format of the RELEASE RECORDS statement is:

RELEASE RECORDS {IN label | ON [LIST] listname}

where label is the statement label of the FIND statement that locked the records.

The RELEASE RECORDS statement is supported in remote file and scattered group contexts.

Processing

The RELEASE RECORDS statement removes all:

• Entries from the specified found set(s), thereby removing the record set lock
• List entries, when issued against list(s)
• The user's single record locks.

When the RELEASE statement refers to a SORT statement, the space occupied by the temporary sorted record copies is released.

RELEASE RECORDS ON is equivalent to the CLEAR LIST statement.

Release All Records statement

Description

A Release All Records statement terminates the lock in share mode placed on records by the Find statement. Release All Records also clears all lists and the results of all Sort statements, and sets the current record number to -1.

After a Release All Records statement is processed in a For Each Record loop, subsequent references to the current record in the loop cannot find a current record. If processing returns to the top of the loop, no new record is available, and the first statement after the end of the loop is executed.

Syntax

The format of this release statement is:

Release All Records

The Release All Records statement is supported in remote file and scattered group contexts.

RELEASE and COMMIT RELEASE statements with global foundsets and lists

The RELEASE statements and the COMMIT RELEASE statements empty the contents of a global found set, global sort set, or global list. The label and positions associated with a found set, sort set, or the list is still considered global, but it is empty. Global positions are not cleared by RELEASE or COMMIT RELEASE statements, however, without records there is nothing to process.

Lock pending updates

Model 204 provides a special facility, lock pending updates, that prevents updated records in one transaction (a sequence of file updating operations) from being used by other applications until the transaction ends. Lock pending updates ensures logical consistency without requiring the use of the FIND AND RESERVE statement. LPU locks are distinct from single record locks.

Processing

If the lock pending updates option is specified, records are locked in share mode by a FIND statement. The first update to a record locks the record in exclusive mode and adds it to a set of updated locked records called the pending update pool. The record is not released from this exclusive lock at the end of the FOR EACH RECORD loop. Instead, the record is locked until the end of the transaction when the entire pending update pool is released.

Set with the FOPT parameter

Lock pending updates is an option of the FOPT parameter that is enabled or disabled on a file-by-file basis.

Commit statement

A Commit statement ends the current transaction, dequeues checkpoints, and drops all LPU locks and exclusive record locks obtained by update statements (Add, Change, Delete) for all files participating in the transaction. The Commit statement does not release record locks obtained by the Find statement. The share record lock obtained by the For Record Number statement is not released by a Commit statement executed inside a loop.

The Commit statement is supported in remote context. If records on any remote nodes are updated, the Commit statement saves all remote updates on all remote nodes. Also, remote updates are committed automatically by the system when appropriate.

Syntax

Commit [Release]

Example

begin get.rec: find all records for which lname = 'NELSON' end find for each record in get.rec add month = 'nov' commit print fname and lname and each month end for name.ct: count records in get.rec print count in name.ct and 'records updated' end

Release option

The Release option of the Commit statement performs all the operations of the Commit and Release_all_records statements. :

After processing a Commit Release statement ...	Any subsequent reference to the current...
For Each Record loop	Record in the loop cannot find a current record. If processing returns to the top of the loop, no new record is available; the first statement after the end of each loop is executed.
For Each Value loop	Value still obtains the last value processed.

Usage

To avoid confusing results, it is recommended that Commit Release be issued at the end of a loop and not in the middle of one.

Release and Commit Release statements with global foundsets and lists

The Release statement and the Commit Release statement both empty the contents of a global found set, global sort set, or global list. The label and positions associated with a found set, sort set, or the list is still considered global, but it is empty. Global positions are not cleared by Release or Commit Release statements, however, without records there is nothing to process.