Multi-User Systems and Concurrency

In a single-user application information can be read, created, updated or deleted without the risk of interfering with other people's work. When more than one user has the ability to access shared information, concurrency control becomes essential.

Concurrency Issues

When developing multi-user systems it is important to consider the desired outcome when two or more users or services simultaneously try to access the same data. The most serious problems occur when two users try to modify an item at the same the same time or when one user is editing information whilst others read the original data.

As an example, consider a database that contains all of a company's customer information. If two users simultaneously open the same customer record, make changes to the information and then save their modifications, several data integrity issues are possible. We can see these by looking at the timeline of the two concurrent transactions:

Concurrency Control

The problems described above are some of those that must be prevented to maintain the integrity of a system's data in a multi-user system. These issues are generated because it is necessary to make temporary, local copies of information from the official data store; in this case the customer database. These local copies are essential to permit data to be modified but cause more up-to-date information to exist outside of the official data than within.

A concurrency control system provides safe access to shared information, removing the risk of collisions that cause data integrity problems or data loss such as that described above. This is usually achieved by either preventative locking, where access to data being edited by another user is limited or removed, or by the detection of collisions, where problematic changes are disallowed or require correction.

Locking Strategies

In this article we will discuss two types of concurrency control system. These are optimistic locking and pessimistic locking. This will be the first article in a series of three. The two following articles will provide working examples of the locking strategies using SQL Server tables and stored procedures.

Optimistic Locking

Optimistic locking is so named because it optimistically assumes that there will be few collisions when data is modified. This strategy does not prevent users from accessing information that is in the process of being updated by other users or processes. However, it does prevent destructive changes from being made without the user's knowledge by detecting changes made by another user that have occurred during the time of editing. The process for modifying data with an optimistic concurrency control system is as follows:

1. The user reads the original information from the data store.
2. The user makes changes to their local copy. The original information is remembered.
3. The user attempts to save their changes. Before the change is committed, the existing official data is compared with the originally loaded information. If the two match, the save is permitted. If the data has already been modified, the change is rejected.

In step 2, it is common to remember a signature for the originally retrieved information, rather than all of the data. This is often a version number that is updated with every revision. If the version number changes between the times of loading and saving, this indicates that a collision has occurred.

When a collision is detected, there are several possible courses of action. The simplest is to reject the new updates entirely and require the user to repeat the process. More complex solutions allow the new data to be saved but audit the collision and the data that existed before and after the change. The best solutions can merge the new data with the existing information, either automatically or with the user's assistance.

Optimistic locking is most suited to systems where collisions are unlikely because editing of data is limited, or where information is partitioned in such a way that each user usually edits a distinct set of items. It is the only viable solution that we will consider for disconnected systems, which rely on occasional synchronisation.

Advantages of Optimistic Locking

The key advantages of optimistic locking systems are:

• The risk of inadvertently overwriting other users' changes is removed.
• Optimistic locking can be used on rarely connected systems, as no locking information needs to be held centrally.
• There is little central overhead to the locking system. This leads to only a small performance impact whilst allowing higher concurrency and scalability.

Disadvantages of Optimistic Locking

The key disadvantages of optimistic locking systems are:

• The possibility exists that users will be disappointed when they attempt to save their changes. In the worst situations, a user may be informed that the official data changed whilst they were editing a record and that their changes were cancelled. This increases the user's workload and decreases satisfaction with the software.
• As the duration required to edit an item increases, the possibility that a collision will occur also increases. This can occur when individual items are complex and take a long time to update, or for disconnected systems where local changes are committed rarely.
• No details of data being edited by one user are available to the other users. This prevents users from delaying changes until the information they require becomes available for editing and increases the chance of rejections.

Pessimistic Locking

Pessimistic locking gets it name by being pessimistic about the number of collisions that may occur in a multi-user application. This strategy either prevents or limits access to information that is being edited. The process for editing an item in such an environment is as follows:

• The user reads the original information from the official data store. A lock is created in the data store that can be seen by other users. Any other user that attempts to access the data being edited is prevented from doing so or is limited to read-only access.
• The user makes changes to their local copy of the information.
• The user saves their changes. Once the new data is committed to the official data store, the previously created lock is removed. Other users may now access the updated information.

The lock that is created can be simple or sophisticated. In the simplest solutions, a unique identifier for the data is held in a table of locks that other users can query. This can be enhanced by storing information such as the creation time of the lock and details of the user that holds it.

Pessimistic locking is most suited to systems where the risk of collisions is high because data is modified often and by many different users and services. It only works for connected systems, as access to the central store must be available to correctly maintain the locks.

Advantages of Pessimistic Locking

The key advantages of pessimistic locking systems are:

• The risk of inadvertently overwriting other users' changes is removed.
• It is possible to prevent users from reading information from the official data store that is currently being edited and that may therefore be out of date.
• Users changes will not normally be rejected on saving.
• With interrogation of the current locks, it is possible to identify who is currently editing information and which records are being worked upon.

Disadvantages of Pessimistic Locking

The key disadvantages of pessimistic locking systems are:

• As the number of users grows, the risk of collisions increases. This increases the risk that users will be prevented from accessing information that they require and reduces the scalability of the software.
• The additional overhead of maintaining the locks can lower the performance of the solution.
• Misuse and failures for overly simplistic pessimistic locking solutions can lead to items being permanently locked. In some cases this may require manual intervention to remove the lock and restore access to the information.

• The requirement to maintain locks centrally requires that users remain permanently connected to the central data store.