Data Locking Strategies

This document provides techniques for preventing corruption of database data due to concurrent writes by multiple users.

• Optimistic Locking
• Pessimistic Locking

Overview

With users accessing and editing the same record at the same time, race conditions may occur when writing to the database. For example, users A and B access a customer record of Acme Corp. at the same time. They both then start a related action to edit the company’s address and they will both see the current address that is saved. If user B submits their edits after user A, user B’s input will overwrite user A’s input (last save wins).

There are concurrency control mechanisms (record locking) to prevent this overwrite but before starting off with your locking implementation consider these factors:

• What is the probability of a race condition occurring? If only two users can edit a record and they work in non-overlapping shifts then a race condition is unlikely to happen.
• If an override occurs, what is the impact on the business? In the example above, if user B is authorized to make the change, it should not matter whose change is saved. However, if you are updating the number of widgets remaining in stock, the impact would be large if you sell more widgets than you have.

In a situation where there is a significant chance of data integrity issues occurring and the impact of the issue is large, a race condition prevention mechanism is necessary.

There are two main control mechanisms, optimistic and pessimistic locking, and we will discuss each one of them below.

Optimistic Locking

This is generally the recommended mechanism for concurrency control, especially if the chance of contention is relatively low.

When Using CDTs

When leveraging CDTs to write data to a database, optimistic locking can be implemented utilizing the @Version annotation. This mechanism is very simple to implement and the designer does not need to worry about contention detection within the models - it is handled by the database.

When Using Record Types

If your implementation is leveraging record types to persist data, additional steps will need to be taken to implement Optimistic Locking. Add an integer record field called version to each record type that needs locking applied. Upon creating the record for the first time, application logic should set the value of this field to 1. Subsequently, when the record is updated, the application should check whether the version number returned at the start of the update action is the same upon the completion of the action. If the values are the same, increment the version by 1 and persist the changes to the database. If the values are different, this means the data is stale and it is not safe to persist. This failure scenario can be handled a few different ways including alerting an administrator to resolve the issue or informing the user that their changes could not be committed.

Here is a sample flow:

1. User A starts an action to edit a record
2. The associated record data is queried and version N is returned in the record field
3. User A is presented with a form to edit the record
4. User B starts an action to edit the same record as User A
5. The associated record data is queried and version N is returned in the record field
6. User A completes their changes. The record is queried again and it is determined that the version is still N. That application will then increment the version value and update the record.
7. User B completes their changes. The record is queried again and it is determined that the version is now N+1. The application will not persist these changes and instead will route the user to an interface stating the change could not be committed and they should retry the action.

Although this implementation will dramatically reduce the chance of stale data being persisted, there is still a small race condition window between querying for the current version and updating the record. It should be determined if this is an acceptable risk for the application to take on before proceeding with this approach.

Although the creation of common objects can reduce the development burden for this implementation, some development time will be required to apply this locking strategy to each record type that needs it.

Pessimistic Locking

If there is a high chance of a race condition, and collisions occur frequently, resolving optimistic locking can become a big burden on the process administrator and it provides a poor user experience. A pessimistic locking strategy, where conflicts are prevented by forcing all transactions to obtain an exclusive lock before attempting any modifications, may be more appropriate in these scenarios. However, pessimistic locking can introduce performance and maintenance overhead and this locking mechanism is only recommended where alternative solutions are not applicable.

Before adopting a pessimistic locking strategy, consider utilizing other design patterns to reduce the chance of a race condition. This can be done utilizing various designs:

• Security settings - reducing the number of people with access, therefore reducing concurrency
• Business organization - although a wide audience has access, only a select few are considered "owners" and responsible for data updates.

In the event where a pessimistic lock is necessary, a simple locking mechanism can be built using a database table to hold locks against entities, with table key constraints managing the locking. A stored procedure is used to attempt an insert of a key that is unique to a given entity of a given entity type and to handle duplicate key exceptions. Once the locking stored procedure has been executed, it returns a confirmation as to whether the lock was successfully obtained and if not it provides information on who already has the lock. This approach enables locking with a single transaction. It also supports the scenario where two users attempt to obtain a lock at exactly the same time, because locking relies on always attempting an insert and relying on the database to apply the duplicate key constraint, rather than performing a read to see if the record is not yet locked.

Here is a sample flow:

1. User A starts an action to edit a record
2. From the process model, prior to the UI Task, a “get lock” stored procedure is called. It returns a confirmation that the lock was successfully obtained.
3. User A is presented with a form to edit the record
4. User B starts an action to edit the same record as User A
5. From the process model, prior to the UI Task, a “get lock” stored procedure is called. It returns confirmation that the lock was not successfully obtained and that User A has the lock.
6. User B is presented with a form explaining that the record is currently locked and not available for edits.

A pessimistic lock mechanism has several drawbacks:

• There needs to be a mechanism to release the lock even if the user does not complete the action. This has to be done using a timer, which can lock the record for prolonged periods of time.
  • Always use an exception flow to automatically release a lock after a given period.
  • Always handle the scenario where releasing the lock fails and retry the release to ensure that the lock is eventually released.
• The mechanism needs to be implemented in all functionality that allows an update to the record, including external systems.

An implementation of the pessimistic locking approach described above is available here.