Concurrency and locking on databases

Solving concurrency problems when working with JPA, Hibernate and Spring Data.

Photo by Tobias Fischer on Unsplash

As soon as different threads on an application — or different application instances in a microservice ecosystem — need to make changes to the same database table, eventually, race conditions will surface.

By operating on the same table, those different threads will eventually want to update the same row. Without further controls, some updates will be silently lost…

Losing changes silently

It is evident that this can happen when we’re talking about the same field being updated in both flows.

However, it also happens when different fields are updated! By default, Hibernate issues an UPDATE statement for all entity fields, so the entire row is updated. Doesn’t Hibernate’s @DynamicUpdate annotation solve the problem? It will only update the fields which were changed, right? Well, no. Hibernate will compare the state of the in-memory entity with the database. Then it will generate an UPDATE statement for all columns which are different, regardless of where they changed. So, saving the in-memory entity will still overwrite changes made in the database!

This is a silent problem because there is no error raised to flag the overwrite. The only solution is to reload the record and retry the update. But to do so, the application must know that there is a conflict.

There are two workable solutions to this problem. All require a transaction to be active while the affected entity is being processed.

Optimistic locking

Each JPA entity that may be processed concurrently is enhanced with a @Version column.

When an update is made on a row that has been modified since the last time it was read, the version number is no longer the same, and an exception is thrown.

Recovery is possible by reloading the affected entity, reapplying the changes, and saving it again.

Disadvantages of optimistic locking:

1. Conflict is known only at commit time, so after all processing was done. The transaction is rolled back at that time. It can be a problem when using other, non-transactional resources for processing the entity.
2. The complexity of the retry mechanism: only the results of the processing can be played back. If non-transactional resources are involved in the processing, care must be taken not to repeat non-idempotent operations.

Pessimistic locking

In order words, using long-running transactions with row locking at the database level. Each transaction that wants to work with a row will need to wait until the other is done or a timeout occurs. The lock is released when the transaction completes.

Lock timeouts result in exceptions but not necessarily in a transaction rollback. Only when using a Spring transaction inside a @Transactional method or a TransactionTemplate callback is a rollback guaranteed because Spring rolls back the transaction if those methods throw an exception.

The most frequent pessimistic locking pattern is to lock the row against reading and modifying when it is read at the start of the transaction. This is known as a pessimistic write lock. No other transaction can read or modify the same row while it is locked. It often results in a SELECT … FOR UPDATE or similar statement being issued.

It is also possible to lock the row against modifications only, which means that other transactions can read it at any time. This is a pessimistic read lock. Not all databases or JPA implementations support this mode and often upgrade it automatically to a pessimistic write lock.

The desired JPA lock mode can be configured as described in this Java EE tutorial about lock modes. When using Spring Data, we can use the @Lock annotation in Spring repository methods to specify the desired lock mode. The lock timeout value can be defined by setting the javax.persistence.lock.timeout hint in a @QueryHints annotation applied to a Spring repository method.

Disadvantages of pessimistic locking:

1. Relies on the database for synchronization.
2. Timeout must be configured as a hint, so it’s not a contract and thus not necessarily portable between databases.
3. Deadlocks may occur when multiple resources are being locked.
4. Uses more resources at the database level.

Which one should be used?

It depends. As it always does.

Ideally, application flows should be designed in such a way that concurrency is not a problem. Thus, no resources are shared. This general principle also applies to database resources such as rows.

However, when it is impossible to prevent different flows from updating the same row concurrently, two factors influence the choice between one of these mechanisms.

In the end, the choice should be made after balancing the consequences brought about by either of the following:

Traffic

Pessimistic locking increases the resources needed by the database server, whereas optimistic locking leaves the burden on the client.

For low traffic, pessimistic locking may be appropriate. If traffic raises, though, the database server may become resource-constrained, leading to another type of problem. Also, threads in the client will be blocked until row locks are released, diminishing the client’s capacity to process requests.

When traffic levels rise, optimistic locking is a better choice.

Concurrency

Optimistic locking requires the client application to refresh its data when a conflict arises. This refresh increases the number of database roundtrips and thus the load at the database server.

High concurrency scenarios are good candidates for pessimistic locking.

Conclusion

There are two methods available for managing concurrency at the database level: optimistic locking and pessimistic locking.

The choice for one — or a combination of both — should be made after carefully considering the impact that traffic and concurrency have on the application and the database server, under either locking mode.

It is understood that load testing should always be performed under the expected maximum traffic levels to boost confidence in the solution and uncover problems early.

Resources

Documentation about the @QueryHints annotation: QueryHints (Spring Data JPA 2.4.7 API)

Link to a Java EE tutorial about lock modes.

Documentation about the @Lock annotation: Lock (Spring Data JPA 2.4.7 API)

Documentation about the @Transactional annotation: Transactional (Spring Framework 5.3.5 API)

Documentation about the @Version annotation: Version (hibernate-jpa-2.1-api 1.0.0.Final API) (jboss.org)

Documentation about the @DynamicUpdate annotation: DynamicUpdate (Hibernate JavaDocs) (jboss.org)