Record Locking

4D and 4D Server automatically manage databases by preventing multi-user or multi-process conflicts. Two users or two processes cannot modify the same record or object at the same time. However, the second user or process can have read-only access to the record or object at the same time.

There are several reasons for using the multi-user commands:

•  Modifying records by using the language.
•  Using a custom user interface for multi-user operations.
•  Saving related modifications inside a transaction.

There are three important concepts to be aware of when using commands in a multi-processing database:

1. In a process, each table is in either a read-only or a read/write state.
2. Records become locked when they are loaded and unlocked when they are unloaded.
3. A locked record cannot be modified.

As a convention in the following sections, the person performing an operation on the multi-user database is referred to as the local user. Other people using the database are referred to as the other users. The discussion is from the perspective of the local user. Also, from a multi-process perspective, the process executing an operation on the database is the current process. Any other executing process is referred to as other processes. The discussion is from the point of view of the current process.

A locked record cannot be modified by the local user or the current process. A locked record can be loaded, but cannot be modified. A record is locked when one of the other users or processes has successfully loaded the record for modification, or when the record is stacked. Only the user who is modifying the record sees that record as unlocked. All other users and processes see the record as locked, and therefore unavailable for modification. A table must be in a read/write state for a record to be loaded unlocked.

Each table in a database is in either a read/write or a read-only state for each user and process of the database. Read-only means that records for the table can be loaded but not modified. Read/write means that records for the table can be loaded and modified if no other user has locked the record first.

Note that if you change the status of a table, the change takes effect for the next record loaded. If there is a record currently loaded when you change the table’s status, that record is not affected by the status change.

When a table is read-only and a record is loaded, this record is always locked. In other words, locked records can be displayed, printed, and otherwise used, but they cannot be modified.

Note that the read-only state applies only to editing existing records. A read-only state does not affect the creation of new records. You can still add records to a read-only table using CREATE RECORD and ADD RECORD, or the menu commands of the Design environment (in this case, the records being created are locked for all other users/processes). Note that the ARRAY TO SELECTION command is not affected by the read-only state since it can both create and modify records.

4D automatically sets a table to read-only for commands that do not require write access to records. These commands are: DISPLAY SELECTION, DISTINCT VALUES, EXPORT DIF, EXPORT SYLK, EXPORT TEXT, _o_GRAPH TABLE, PRINT SELECTION, PRINT LABEL, QR REPORT, SELECTION TO ARRAY, SELECTION RANGE TO ARRAY.

You can find out the state of a table at any time using the Read only state function.

Before executing any of these commands, 4D saves the current state of the table (read-only or read/write) for the current process. After the command has executed, this state is restored.

When a table is read/write and a record is loaded, the record will become unlocked if no other user has locked the record first. If the record is locked by another user, the record is loaded as a locked record that cannot be modified by the local user.

A table must be set to read/write and the record loaded for it to become unlocked and thus modifiable.

If a user loads a record from a table in read/write mode, no other users can load that record for modification. However, other users can add records to the table, either through the CREATE RECORD and ADD RECORD commands or manually in the Design environment.

Read/write is the default state for all tables when a database is opened and a new process is started.

You can use the READ ONLY and READ WRITE commands to change the state of a table. If you want to change the state of a table in order to make a record read-only or read/write, you must execute the command before this record is loaded. Any record that is already loaded is not affected by the READ ONLY and READ WRITE commands.

Each process has its own state (read-only or read/write) for each table in the database.

By default, if you do not use the READ ONLY command, all tables are in read/write mode.

Before the local user can modify a record, the table must be in the read/write state and the record must be loaded and unlocked.

Any of the commands that loads a current record (if there is one) — such as NEXT RECORD, QUERY, ORDER BY, RELATE ONE, etc. — sets the record state as locked or unlocked. The record is loaded according to the current state of its table (read-only or read/write) and its availability. A record may also be loaded for a related table by any of the commands that cause an automatic relation to be established.

If a table is in the read-only state for a process or a user, then this table's records are loaded in read-only mode, which means they cannot be modified or deleted by this process or user. This is recommended for viewing or retrieving data because it does not prevent other users or processes from accessing the records of this table in read/write mode if necessary.

If a table is in the read/write state for a process or a user, then any record from this table is also loaded in read/write mode, but only if no other user or process has already locked this record. If a record is successfully loaded in read/write mode, it is unlocked for the current process or user (it can be modified and saved) and is locked for all other users or processes. A table must be put into the read/write state before loading a record for modification and then saving it.

If the record is to be modified, you use the Locked function to test whether or not a record is locked by another user. If a record is locked (Locked returns True), load the record with the LOAD RECORD command and again test whether or not the record is locked. This sequence must be continued until the record becomes unlocked (Locked returns False).

When modifications to be made to a record are finished, the record must be released (and therefore unlocked for the other users) with UNLOAD RECORD. If a record is not unloaded, it will remain locked for all other users until a different current record is selected. Changing the current record of a table automatically unlocks the previous current record. You need to explicitly call UNLOAD RECORD if you do not change the current record. This discussion applies to existing records. When a new record is created, it can be saved regardless of the state of the table to which it belongs.

Note: When it is used in a transaction, the UNLOAD RECORD command unloads the current record only for the process that manages the transaction. For other processes, the record stays locked as long as the transaction has not been validated (or cancelled).

Use the LOCKED BY command to see which user and/or process have locked a record.

Note: A good practice is to place all tables in read-only mode when each process is started (using the syntax READ ONLY(*)) then put each table in read/write mode only when necessary. Access to tables in read-only mode is faster and more memory-efficient. Moreover, changing the state of a table is optimized in client/server mode because it does not cause any additional network traffic: information is only sent to the server when executing a command that requires adequate access to the table.

The following example shows the simplest loop with which to load an unlocked record:

The loop continues until the record is unlocked.

A loop like this is used only if the record is unlikely to be locked by anyone else, since the user would have to wait for the loop to terminate. Thus, it is unlikely that the loop would be used as is unless the record could only be modified by means of a method.

The following example uses the previous loop to load an unlocked record and modify the record:

The MODIFY RECORD command automatically notifies the user if a record is locked, and prevents the record from being modified. The following example avoids this automatic notification by first testing the record with the Locked function. If the record is locked, the user can cancel.

This example efficiently checks to see if the current record is locked for the table [Commands]. If it is locked, the process is delayed by the procedure for one second. This technique can be used both in a multi-user or multi-process situation:

A number of commands in the language perform specific actions when they encounter a locked record. They behave normally if they do not encounter a locked record.

Here is a list of these commands and their actions when a locked record is encountered.

• MODIFY RECORD: Displays a dialog box stating that the record is in use. The record is not displayed, therefore the user cannot modify the record. In the Design environment, the record is shown in read-only state.
• MODIFY SELECTION: Behaves normally except when the user double-clicks a record to modify it. MODIFY SELECTION displays dialog box stating that the record is in use and then allows read-only access to the record.
• APPLY TO SELECTION: Loads a locked record, but does not modify it. APPLY TO SELECTION can be used to read information from the table without special care. If the command encounters a locked record, the record is put into the LockedSet system set.
• DELETE SELECTION: Does not delete any locked records; it skips them. If the command encounters a locked record, the record is put into the LockedSet system set.
• DELETE RECORD: This command is ignored if the record is locked. No error is returned. You must test that the record is unlocked before executing this command.
• SAVE RECORD: This command is ignored if the record is locked. No error is returned. You must test that the record is unlocked before executing this command.
• ARRAY TO SELECTION: Does not save any locked records. If the command encounters a locked record, the record is put into the LockedSet system set.
• GOTO RECORD: Records in a multi-user/multi-process database may be deleted and added by other users, therefore the record numbers may change. Use caution when directly referencing a record by number in a multi-user database.
• Sets: Take special care with sets, as the information that the set was based on may be changed by another user or process.