Record locking

When multiple users attempt to simultaneously update a record, the database locking model ensures that the update from each user succeeds as a unit, but each update overwrites the previous update.

An explicit (pessimistic) locking model prevents unexpected loss of data updates and controls the workflow when simultaneous updates from more than one user occur.

There are two locking models that can be used:
  • Optimistic locking - A locking technique that allows simultaneous updates and detects conflicts in updates when the record is committed. By default HCL Compass uses optimistic locking.
  • Pessimistic locking - A locking technique that guarantees exclusive access to the record while it is being edited.

Optimistic locking

The optimistic locking model allows multiple users to view and attempt to modify a record at the same time but prevents all but the first user from committing their changes. Users are not informed that others are also attempting to update the record.

The record changes are validated when the record is committed. If another user has already successfully updated the record, other simultaneous updates are informed that a conflict exists when these other users attempt to submit their concurrent updates.

By default, records are not locked while they are displayed in a HCL Compass client application. As a consequence, any schema design that uses a single site or a replicated environment should take that into account.

Data integrity is ensured by checking, at the time a user clicks the Apply button, whether another user has updated the record and committed their change while still in the process of making their changes. If so, the user's updates cannot be committed to the database because this might result in losing some of the other user's changes. The user trying to commit their changes after the record has been updated by another user receives an error message that their changes were not committed to the database.

In complicated scenarios involving coordinated updates to multiple related records, care must be taken to ensure that this behavior does not cause a problem. Since optimistic locking is effective on each record individually, your application must ensure that the records are updated in the right order and handle failures to update a subordinate record if another user updated it between the beginning of your action and committing your changes. Your schema design can retry the operation, or acknowledge the failure and revert the update of the parent record, or it may commit the parent record update even though the update of the subordinate record failed.

Your schema design should handle cases where a record is modifiable,
  • but the modification fails because the record was modified by another user during the modification.
  • and a subordinate record is modifiable but the modification fails because the subordinate record was modified by another user during the modification.

Pessimistic locking

Pessimistic locking provides a mechanism to prevent situations where multiple users concurrently edit a record. The pessimistic locking model enforces sequential modification of records and prevents the simultaneous updates of records. As soon as one user starts to update the record, this model places a lock on the record. Any other users that attempt to start to update this record are informed that another user has an update already in process and are locked out from modifying it. Users desiring to simultaneously update the record must wait until the first user has finished committing the record, and then the other user is granted the lock and can make changes after the prior user's changes. This model avoids the issue of conflict resolution by preventing conflicts. Updates are serialized and each subsequent update starts with the already updated record with the previous users changes.

However, a lock must be acquired to prevent other users from attempting to update the record. This model requires a lock management strategy which addresses:
  • Getting the lock
  • Informing any users that desire to simultaneously update the record that they must wait for the lock to be released
  • Informing users that the lock has been released
  • Freeing locks that have been abandoned (such as for system crashes or forgetting to complete an update)

To use pessimistic record locking, hook code must be added to the record types that want to use it. The hook code must be added as a new BASE type action for each record type. Manually removing locks can be accomplished with hook code that is implemented as a record script alias. You can use a HCL Compass query to find locked records by searching for records with the locked_by field equal to non-null values. The locked_by user database column is an integer column that records the user's login id when a record is locked.

Pessimistic locking is a useful model when true simultaneous updates are not required, and subsequent updates can be delayed until a previous update has been completed. This usually implies that updates occur in a very short time interval. Updates which hold the lock for long periods of time prevent other users from updating the record.