Pessimistic locking in JPA/Hibernate

There are two ways to deal with concurrency conflicts, optimistic locking and pessimistic locking. Implicit optimistic locking has been already covered, if you didn’t have the chance to read my article on it, make sure you do it before continuing. Pessimistic locking is also a key tool to deal with conflicts. I’ll show you how to use it with Hibernate.

About pessimistic locking

Pessimistic locking is all about the concept of avoiding the conflicts from happening. This is done by employing different kind of database locks. There are two types of locks which can be differentiated, read locks for reading only the data and write locks for reading and writing the data. Multiple readers can access the same row at the same time. If someone wants to write a row and acquires a write lock, only that single writer is allowed to access it and noone else, even reading is prohibited.

With read/shared locks the database engine can guarantee that if a row is read, no other concurrent transaction can modify it thus preventing the non-repeatable read anomaly. If a write/exclusive lock is applied, then no other reader or writer can access the data.

The reality is a bit complicated though. Different database engines support different type of locks, some of them only support exclusive locks. Additionally, locks can be usually acquired with syntax differences between vendors. Hibernate can help here to wrap all these things. It can handle the syntax differences and the lock levels will be requested based on the underlying database, e.g. if the database only supports exclusive locks but you request only for a shared lock, Hibernate will automatically request an exclusive lock as that’s the only possibility for that database.

Although it sounds really great to use locks, it has one big downside. Locks will be held until the transaction ends which obtained the locks. This can be an issue especially for long-running transactions. Imagine a situation when a transaction takes 10 seconds with requesting an exclusive lock at the beginning and keeping it until the transaction ends. No other reader or writer can access the same data for 10 seconds which is unacceptable for a performance oriented application.

In the next couple of minutes, I’ll show you how to handle pessimistic locking on Hibernate level which will result in a database lock. Note that there are other explicit locks as well which I’ll cover in an upcoming article.

Using the explicit pessimistic locking mechanism in JPA/Hibernate

Unlike optimistic locking (as seen in my previous article), pessimistic locking can only be utilized by explicitly telling Hibernate which lock is needed for the operation. The idea is that when you are requesting data via Hibernate, you can tell the framework to apply a shared or exclusive lock on the data you are requesting. In the following quick example you can check how this is possible with a simple EntityManager#find

As you can see, there is a 3rd parameter for the find  method which is an instance of the LockModeType  enum.

Two values are interesting for now:

  • PESSIMISTIC_READ
  • PESSIMISTIC_WRITE

The first one will request a shared lock from the database, the second one will be an exclusive lock.

The test now requested an exclusive lock and this can be easily seen in the generated SQL as well:

Note the FOR UPDATE  clause at the end of the select statement. I’m using H2 to run this test and the syntax is depending on the database as I mentioned previously. For example SQL Server doesn’t have FOR UPDATE  instead it operates with query hints to handle locking.

Read/shared locks

For showcasing read locks, the tests need to be switched to PostgreSQL as H2 only offers exclusive locks. However, I’ll just show you what happens when a shared lock is requested from such database. Same test as before with just a tiny little change to request a shared lock.

The resulting SQL is the same as before:

As the underlying database doesn’t support shared locks, Hibernate requests an exclusive lock as it has all the guarantees as a shared lock and even more than that thus preventing any other writers from changing the data.

Switching to PostgreSQL (which supports both type of locks) and executing the same test case will result in the following SQL:

PostgreSQL supports shared locks via the FOR SHARE  clause as you can see in the generated SQL. After the basics, continuing with executing some real tests demonstrating that multiple readers can access the very same row at the same time.

This test simulates the situation when 3 readers try to read the same entity concurrently. As the test passes successfully and the generated SQL contains 3 select statements for the same row with the FOR SHARE  clause, it’s clear that multiple readers can access the same data at the same time.

Write/exclusive locks

Going forward with the exclusive locks. The basic idea for these type of locks is that no other transaction can modify or read the data which is locked. The idea behind is to prevent the non-repeatable read  and dirty read anomalies on the read side and the lost update anomaly on the write side.

Checking the previous example when multiple readers were present, now let’s have 1 reader who already locked a database row and 1 writer who wants to lock the same row exclusively.

In the test, the first transaction at the beginning locks the database row in shared mode then a second parallel transaction comes into the picture and tries to lock the same row exclusively which will fail as a shared lock was already applied on the data. Note that a third parameter is provided as well when locking the row exclusively which is necessary to stop the select statement from hanging. This is how PostgreSQL will know that it shouldn’t wait for acquiring the exclusive lock, but this will be covered in more details on the Timeouts section.

In the log, there’s even a message that the exclusive lock cannot be obtained.

The same will happen when multiple writers try to lock the same row, I don’t think it’s necessary to show it as the only difference in the test is to use exclusive locks in both places.

Timeouts

There was already a timeout setting in the previous example when the lock couldn’t be obtained because of an already existing lock. Timeouts are used for avoiding deadlocks in the system. If a lock cannot be acquired after a certain amount of time, the request must be canceled and retried. Currently, there is no API available in JPA to configure lock timeouts but it can be set via query hints. It’s just basically a map of String – Object elements where different optimizations, configurations can be set for the underlying provider and database.

Lock timeout is no different, it can be set with the  javax.persistence.lock.timeout query hint along with a timeout value in milliseconds.This is how it was set in the previous example:

Unfortunately, there is no standard handling lock timeouts and different vendors handle it differently. For example in PostgreSQL, setting the lock timeout to any positive value will have no effect at all, however with a value of 0, Hibernate will add the NOWAIT  modifier at the end of the query which will instruct the database not to wait for the lock to be available.

I think it’s essential to understand the capabilities of the underlying database and how lock timeouts are supported as it will have an impact on the system. For this purpose always check the database documentation. On top of that, make sure you use timeouts for your pessimistic locks, don’t get into a situation which allow deadlocks.

Other ways to lock

The basic use case was covered when locking the database row along with fetching it. There are other ways to do the locking as well.

EntityManager#lock  can be used when you already have a fetched entity.

First the product is fetched and then it is getting locked resulting in executing two statements.

It’s also possible to lock multiple rows at the same time, this can be done by executing a custom select query and setting the lock mode.

In this example, all the products are retrieved from the database and gets locked. If any other transaction wants to access the data with an exclusive lock, that operation will simply fail.

Summary

In this article, we’ve checked how pessimistic locks can be used with JPA and Hibernate and what will be the database level implication of them. We’ve also checked how timeouts can be applied to prevent deadlocks from happening in case of an unsuccessful lock request. As always, when it comes to deciding what type of locking pattern to apply, optimistic or pessimistic, it’s a matter of business requirement so give yourself the time to think it through which one to choose.

All the tests are available on my GitHub, check it out.

If you liked the article, make sure you share it so others can benefit from it as well and follow me on Twitter for more interesting articles.

Leave a Reply

Your email address will not be published. Required fields are marked *