Optimistic lock

This document explains the difference between how the management of version 6 handles concurrency conflicts when updating an entity instance, and what has been done in version 7.

Pessimistic lock in object management

In version 6, the object management uses a pessimistic locking method. The principle is the following:

• When a user opens a record, a lock record is added in the 'APLLCK' table. By default, the record is always considered as being potentially modified. The record is released only if another record is selected or if the function is ended.
• If another user wants to open the same record during this operation, a padlock icon appears on the page. Any data input attempt on the page will display an error message (record locked by another user).

This type of locking has the following issues:

• It provides unreal conflicts if a user has opened a record without any intention to modify it: another user willing to modify it will not be able to.
• The 'APLLCK' table might become a contention point and thus bring up performance issues when many modifications are made to movement tables.
• It might be relevant if the users perform immediate modifications, but it creates contention if a user retains a record for a long period of time. This might be the case with mobile devices because the disconnection might take time to be detected.

Optimistic lock in Version 7 native CRUD operations

In version 7, the CRUD operations on a class, which replaced the object management, implement another optimistic method. The main principles are the following:

• On every record, a technical column called Updtick has been added. This column is automatically updated by a trigger during every update (the cost of this operation is negligible). When the record is created, its value is set to 1. Then, every update will increment it by 1.
• When an update page is used, a working copy is created on the server. This is an instance of the current record and it includes the current value of Updtick. No lock record is created.
• When the user saves its modification and triggers the update, the supervisor uses a new instruction called RewriteByKey. This instruction updates the record with a condition that includes the current value of Updtick. At this point, two things can happen:
  • The record can be updated successfully. This means that the Updtick value did not change: nobody tried to modify the record. The update is done in a single transaction, (and Updtick is incremented).
  • The record cannot be updated successfully. The supervisor tries to read the record by its key without a condition on Updtick. If the record cannot be read, it means that it has been deleted. Otherwise, it means that another user modified the record and changed the Updtick value.

When a conflict occurs in this system, the performed input is lost and can be seen as a disadvantage. However, it is very rare that two users would make substantial modifications on the same record. The probability of such a conflict is smaller than the probability of unreal conflicts between a user that reads a record and a user that updates it.

Advantages:

• If a user remains on a record for a long period of time, no locking record will be present.
• A unique and simple database transaction is done (while in version 6, you have a transaction on 'APLLCK' and another transaction on the record), which decreases the contention in the system and makes it easier to scale.