Optimistic and Pessimistic Locking in JPA

Locking is a mechanism that allows parallel work with the same data in the database. When more than one transaction tries to access the same data at the same time, locks come into play, which ensures that only one of these transactions will change the data. JPA supports two types of locking mechanisms: optimistic model and pessimistic model. \ Let's consider the airline database as an example. The `flights` table stores information about flights, and `tickets` stores information about booked tickets. Each flight has its own capacity, which is stored in the `flights.capacity` column. Our application should control the number of tickets sold and should not allow purchasing a ticket for a fully filled flight. To do this, when booking a ticket, we need to get the capacity of the flight and the number of tickets sold from the database, and if there are empty seats on the flight, sell the ticket, otherwise, inform the user that the seats have run out. If each user request is processed in a separate thread, data inconsistency may occur. Suppose there is one empty seat on the flight and two users book tickets at the same time. In this case, two threads simultaneously read the number of tickets sold from the database, check that there is still a seat left, and sell the ticket to the client. In order to avoid such collisions, locks are applied. ## Simultaneous changes without locking We will use Spring Data JPA and Spring Boot. Let’s create entities, repositories, and other classes: ```java @Entity @Table(name = "flights") public class Flight { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; private String number; private LocalDateTime departureTime; private Integer capacity; @OneToMany(mappedBy = "flight") private Set tickets; // ... // getters and setters // ... public void addTicket(Ticket ticket) { ticket.setFlight(this); getTickets().add(ticket); } } ``` \ ```java public interface FlightRepository extends CrudRepository { } ``` \ ```java @Entity @Table(name = "tickets") public class Ticket { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "flight_id") private Flight flight; private String firstName; private String lastName; // ... // getters and setters // ... } ``` \ ```java public interface TicketRepository extends CrudRepository { } ``` \ `DbService` performs transactional changes: ```java @Service public class DbService { private final FlightRepository flightRepository; private final TicketRepository ticketRepository; public DbService(FlightRepository flightRepository, TicketRepository ticketRepository) { this.flightRepository = flightRepository; this.ticketRepository = ticketRepository; } @Transactional public void changeFlight1() throws Exception { // the code of the first thread } @Transactional public void changeFlight2() throws Exception { // the code of the second thread } } ``` \ An application class: ```java import org.apache.commons.lang3.function.FailableRunnable; @SpringBootApplication public class JpaLockApplication implements CommandLineRunner { @Resource private DbService dbService; public static void main(String[] args) { SpringApplication.run(JpaLockApplication.class, args); } @Override public void run(String... args) { ExecutorService executor = Executors.newFixedThreadPool(2); executor.execute(safeRunnable(dbService::changeFlight1)); executor.execute(safeRunnable(dbService::changeFlight2)); executor.shutdown(); } private Runnable safeRunnable(FailableRunnable runnable) { return () -> { try { runnable.run(); } catch (Exception e) { e.printStackTrace(); } }; } } ``` \ We will use this database state in every following run of the application `flights` table: | id | number | departure_time | capacity | |----|----|----|----| | 1 | FLT123 | 2022-04-01 09:00:00+03 | 2 | | 2 | FLT234 | 2022-04-10 10:30:00+03 | 50 | `tickets` table: | id | flight_id | first_name | last_name | |----|----|----|----| | 1 | 1 | Paul | Lee | Let's write a code simulating the simultaneous purchase of tickets without locking. ```java @Service public class DbService { // ... // autowiring // ... private void saveNewTicket(String firstName, String lastName, Flight flight) throws Exception { if (flight.getCapacity() <= flight.getTickets().size()) { throw new ExceededCapacityException(); } var ticket = new Ticket(); ticket.setFirstName(firstName); ticket.setLastName(lastName); flight.addTicket(ticket); ticketRepository.save(ticket); } @Transactional public void changeFlight1() throws Exception { var flight = flightRepository.findById(1L).get(); saveNewTicket("Robert", "Smith", flight); Thread.sleep(1_000); } @Transactional public void changeFlight2() throws Exception { var flight = flightRepository.findById(1L).get(); saveNewTicket("Kate", "Brown", flight); Thread.sleep(1_000); } } ``` \ ```java public class ExceededCapacityException extends Exception { } ``` \ Calling `Thread.sleep(1_000);` makes sure that transactions started by both threads will overlap in time. The result of executing this example in the database: | id | flight_id | first_name | last_name | |----|----|----|----| | 1 | 1 | Paul | Lee | | 2 | 1 | Kate | Brown | | 3 | 1 | Robert | Smith | As you can see, three tickets were booked, although the capacity of the FLT123 flight is two passengers. ## Optimistic locking Now, look at how optimistic blocking works. Let's start with a more straightforward example - a simultaneous capacity of the flight change. In order to use optimistic locking, a persistent property with an annotation `@Version` must be added to the entity class. This property can be of type `int`, `Integer`, `short`, `Short`, `long`, `Long`, or `java.sql.Timestamp`. Version property is managed by the persistence provider, you do not need to change its value manually. If the entity is changed, the version number is increased by 1 (or the timestamp is updated if the field with the `@Version` annotation has the java.sql.Timestamp type). And if the original version does not match the version in the database when saving the entity, an exception is thrown. \ Add the `version` property to the `Flight` entity ```java @Entity @Table(name = "flights") public class Flight { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; private String number; private LocalDateTime departureTime; private Integer capacity; @OneToMany(mappedBy = "flight") private Set tickets; @Version private Long version; // ... // getters and setters // public void addTicket(Ticket ticket) { ticket.setFlight(this); getTickets().add(ticket); } } ``` \ Add the `version` column to the `flights` table | id | name | departure_time | capacity | version | |----|----|----|----|----| | 1 | FLT123 | 2022-04-01 09:00:00+03 | 2 | 0 | | 2 | FLT234 | 2022-04-10 10:30:00+03 | 50 | 0 | Now we change flight capacity in both threads: ```java @Service public class DbService { // ... // autowiring // ... @Transactional public void changeFlight1() throws Exception { var flight = flightRepository.findById(1L).get(); flight.setCapacity(10); Thread.sleep(1_000); } @Transactional public void changeFlight2() throws Exception { var flight = flightRepository.findById(1L).get(); flight.setCapacity(20); Thread.sleep(1_000); } } ``` \ Now when executing our application we will get an exception ``` org.springframework.orm.ObjectOptimisticLockingFailureException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; statement executed: update flights set capacity=?, departure_time=?, number=?, version=? where id=? and version=? ``` Thus, in our example, one thread saved the changes, and the other thread could not save the changes because there are already changes in the database. Thanks to this, simultaneous changes of the same flight are prevented. In the exception message, we see that `id` and `version` columns are used in the `where` clause. \ Keep in mind that the version number does not change when changing the `@OneToMany` and `@ManyToMany` collections with the `mappedBy` attribute. Let's restore the original DbService code and check it out: ```java @Service public class DbService { // ... // autowiring // ... private void saveNewTicket(String firstName, String lastName, Flight flight) throws Exception { if (flight.getCapacity() <= flight.getTickets().size()) { throw new ExceededCapacityException(); } var ticket = new Ticket(); ticket.setFirstName(firstName); ticket.setLastName(lastName); flight.addTicket(ticket); ticketRepository.save(ticket); } @Transactional public void changeFlight1() throws Exception { var flight = flightRepository.findById(1L).get(); saveNewTicket("Robert", "Smith", flight); Thread.sleep(1_000); } @Transactional public void changeFlight2() throws Exception { var flight = flightRepository.findById(1L).get(); saveNewTicket("Kate", "Brown", flight); Thread.sleep(1_000); } } ``` \ The application will run successfully and the result in the `tickets` table will be as follows | id | flight_id | first_name | last_name | |----|----|----|----| | 1 | 1 | Paul | Lee | | 2 | 1 | Robert | Smith | | 3 | 1 | Kate | Brown | Again, the number of tickets exceeds the flight capacity. \ JPA makes it possible to forcibly increase the version number when loading an entity using the `@Lock` annotation with the `OPTIMISTIC_FORCE_INCREMENT` value. Let's add the `findWithLockingById` method to the `FlightRepository` class. In Spring Data JPA, any text between `find` and `By` can be added to the method name, and if it does not contain keywords such as `Distinct`, the text is descriptive, and the method is executed as a regular `find…By…`: ```java public interface FlightRepository extends CrudRepository { @Lock(LockModeType.OPTIMISTIC_FORCE_INCREMENT) Optional findWithLockingById(Long id); } ``` Use the `findWithLockingById` method in `DbService` ```java @Service public class DbService { // ... // autowiring // ... private void saveNewTicket(String firstName, String lastName, Flight flight) throws Exception { // ... } @Transactional public void changeFlight1() throws Exception { var flight = flightRepository.findWithLockingById(1L).get(); saveNewTicket("Robert", "Smith", flight); Thread.sleep(1_000); } @Transactional public void changeFlight2() throws Exception { var flight = flightRepository.findWithLockingById(1L).get(); saveNewTicket("Kate", "Brown", flight); Thread.sleep(1_000); } } ``` When the application starts, one of the two threads throws `ObjectOptimisticLockingFailureException`. The state of the `tickets` table is | id | flight_id | first_name | last_name | |----|----|----|----| | 1 | 1 | Paul | Lee | | 2 | 1 | Robert | Smith | We see that this time only one **Ticket** has been saved to the database. \ If it is impossible to add a new column to the table, but there is a need to use optimistic locking, you can apply Hibernate annotations `OptimisticLocking` and `DynamicUpdate`. The type value in the OptimisticLocking annotation can take the following values: * `ALL` - perform locking based on all fields * `DIRTY` - perform locking based on only changed fields fields * `VERSION` - perform locking using a dedicated version column * `NONE` - don’t perform locking \ We will try the `DIRTY` optimistic locking type in the changing flight capacity example. ```java @Entity @Table(name = "flights") @OptimisticLocking(type = OptimisticLockType.DIRTY) @DynamicUpdate public class Flight { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; private String number; private LocalDateTime departureTime; private Integer capacity; @OneToMany(mappedBy = "flight") private Set tickets; // ... // getters and setters // ... public void addTicket(Ticket ticket) { ticket.setFlight(this); getTickets().add(ticket); } } ``` \ ```java @Service public class DbService { // ... // autowiring // ... @Transactional public void changeFlight1() throws Exception { var flight = flightRepository.findById(1L).get(); flight.setCapacity(10); Thread.sleep(1_000); } @Transactional public void changeFlight2() throws Exception { var flight = flightRepository.findById(1L).get(); flight.setCapacity(20); Thread.sleep(1_000); } } ``` An exception will be thrown ``` org.springframework.orm.ObjectOptimisticLockingFailureException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; statement executed: update flights set capacity=? where id=? and capacity=? ``` Now `id` and `cpacity` columns are utilized in the `where` clause. If you change the lock type to `ALL`, such an exception will be thrown ``` org.springframework.orm.ObjectOptimisticLockingFailureException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; statement executed: update flights set capacity=? where id=? and capacity=? and departure_time=? and number=? ``` Now all columns are used in the `where` clause. ## Pessimistic locking With pessimistic locking, table rows are locked at the database level. Let's change the blocking type of the `FlightRepository#findWithLockingById` method to `PESSIMISTIC_WRITE` ```java public interface FlightRepository extends CrudRepository { @Lock(LockModeType.PESSIMISTIC_WRITE) Optional findWithLockingById(Long id); } ``` and rerun the booking tickets example. One of the threads will throw `ExceededCapacityException` and only two tickets will be in the `tickets` table. | id | flight_id | first_name | last_name | |----|----|----|----| | 1 | 1 | Paul | Lee | | 2 | 1 | Kate | Brown | Now the thread that first loaded the flight has exclusive access to the row in the `flights` table, so the second thread suspends its work until the lock is released. After the first thread commits the transaction and releases the lock, the second thread will get monopole access to the row, but at this point, the flight capacity will already be exhausted, because the changes made by the first thread will get into the database. As a result, the controlled `ExceededCapacityException` exception will be thrown. \ There are three types of pessimistic locking in JPA: * `PESSIMISTIC_READ` - acquire a shared lock, and the locked entity cannot be changed before a transaction commit. * `PESSIMISTIC_WRITE` - acquire an exclusive lock, and the locked entity can be changed. * `PESSIMISTIC_FORCE_INCREMENT` - acquire an exclusive lock and update the version column, the locked entity can be changed \ If many threads are locking the same row in the database, it may take a long time to get the lock. You can set a timeout to receive a lock: ```java public interface FlightRepository extends CrudRepository { @Lock(LockModeType.PESSIMISTIC_WRITE) @QueryHints({@QueryHint(name = "javax.persistence.lock.timeout", value ="10000")}) Optional findWithLockingById(Long id); } ``` If the timeout expires, `CannotAcquireLockException` will be thrown. It is important to note that not all persistence providers support the hint `javax.persistence.lock.timeout`. For example, Oracle's persistence provider supports this hint, whereas it does not for PostgreSQL, MS SQL Server, MySQL, and H2. \ Now we consider a deadlock situation. ```java @Service public class DbService { // ... // autowiring // ... private void fetchAndChangeFlight(long flightId) throws Exception { var flight = flightRepository.findWithLockingById(flightId).get(); flight.setCapacity(flight.getCapacity() + 1); Thread.sleep(1_000); } @Transactional public void changeFlight1() throws Exception { fetchAndChangeFlight(1L); fetchAndChangeFlight(2L); Thread.sleep(1_000); } @Transactional public void changeFlight2() throws Exception { fetchAndChangeFlight(2L); fetchAndChangeFlight(1L); Thread.sleep(1_000); } } ``` We will get the following stack trace from one of the threads ``` org.springframework.dao.CannotAcquireLockException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.LockAcquisitionException: could not extract ResultSet ... Caused by: org.postgresql.util.PSQLException: ERROR: deadlock detected ... ``` The database detected that this code leads to a deadlock. However, there may be situations when the database will not be able to do this and threads will suspend their execution until the timeout ends. ## Conclusion Optimistic and pessimistic locking are two different approaches. Optimistic locks are suitable for situations where an exception that has been thrown can be easily handled and either notify the user or try again. At the same time, rows at the database level are not blocked, which does not slow down the operation of the application. If it was possible to get a block, pessimistic locks give great guarantees for the execution of queries to the database. However, using pessimistic locking, you need to carefully write and check the code because there is a possibility of deadlocks, which can become floating errors that are difficult to find and fix.

Optimistic and Pessimistic Locking in JPA

Too Long; Didn't Read

Companies Mentioned

@yaf

RELATED STORIES