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 table stores information about flights, and stores information about booked tickets. Each flight has its own capacity, which is stored in the 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. flights tickets flights.capacity Simultaneous changes without locking We will use Spring Data JPA and Spring Boot. Let’s create entities, repositories, and other classes: @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<Ticket> tickets; // ... // getters and setters // ... public void addTicket(Ticket ticket) { ticket.setFlight(this); getTickets().add(ticket); } } public interface FlightRepository extends CrudRepository<Flight, Long> { } @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 // ... } public interface TicketRepository extends CrudRepository<Ticket, Long> { } performs transactional changes: DbService @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: 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<Exception> runnable) { return () -> { try { runnable.run(); } catch (Exception e) { e.printStackTrace(); } }; } } We will use this database state in every following run of the application table: flights 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 table: tickets id flight_id first_name last_name 1 1 Paul Lee Let's write a code simulating the simultaneous purchase of tickets without locking. @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); } } public class ExceededCapacityException extends Exception { } Calling makes sure that transactions started by both threads will overlap in time. The result of executing this example in the database: Thread.sleep(1_000); 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 must be added to the entity class. This property can be of type , , , , , , or . 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 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. @Version int Integer short Short long Long java.sql.Timestamp @Version Add the property to the entity version Flight @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<Ticket> tickets; @Version private Long version; // ... // getters and setters // public void addTicket(Ticket ticket) { ticket.setFlight(this); getTickets().add(ticket); } } Add the column to the table version flights 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: @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 and columns are used in the clause. id version where Keep in mind that the version number does not change when changing the and collections with the attribute. Let's restore the original DbService code and check it out: @OneToMany @ManyToMany mappedBy @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 table will be as follows tickets 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 annotation with the value. Let's add the method to the class. In Spring Data JPA, any text between and can be added to the method name, and if it does not contain keywords such as , the text is descriptive, and the method is executed as a regular : @Lock OPTIMISTIC_FORCE_INCREMENT findWithLockingById FlightRepository find By Distinct find…By… public interface FlightRepository extends CrudRepository<Flight, Long> { @Lock(LockModeType.OPTIMISTIC_FORCE_INCREMENT) Optional<Flight> findWithLockingById(Long id); } Use the method in findWithLockingById DbService @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 . The state of the table is ObjectOptimisticLockingFailureException tickets id flight_id first_name last_name 1 1 Paul Lee 2 1 Robert Smith We see that this time only one has been saved to the database. Ticket 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 and . The type value in the OptimisticLocking annotation can take the following values: OptimisticLocking DynamicUpdate - perform locking based on all fields ALL - perform locking based on only changed fields fields DIRTY - perform locking using a dedicated version column VERSION - don’t perform locking NONE We will try the optimistic locking type in the changing flight capacity example. DIRTY @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<Ticket> tickets; // ... // getters and setters // ... public void addTicket(Ticket ticket) { ticket.setFlight(this); getTickets().add(ticket); } } @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 and columns are utilized in the clause. If you change the lock type to , such an exception will be thrown id cpacity where ALL 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 clause. where Pessimistic locking With pessimistic locking, table rows are locked at the database level. Let's change the blocking type of the method to FlightRepository#findWithLockingById PESSIMISTIC_WRITE public interface FlightRepository extends CrudRepository<Flight, Long> { @Lock(LockModeType.PESSIMISTIC_WRITE) Optional<Flight> findWithLockingById(Long id); } and rerun the booking tickets example. One of the threads will throw and only two tickets will be in the table. ExceededCapacityException tickets 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 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 exception will be thrown. flights ExceededCapacityException There are three types of pessimistic locking in JPA: - acquire a shared lock, and the locked entity cannot be changed before a transaction commit. PESSIMISTIC_READ - acquire an exclusive lock, and the locked entity can be changed. PESSIMISTIC_WRITE - acquire an exclusive lock and update the version column, the locked entity can be changed PESSIMISTIC_FORCE_INCREMENT 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: public interface FlightRepository extends CrudRepository<Flight, Long> { @Lock(LockModeType.PESSIMISTIC_WRITE) @QueryHints({@QueryHint(name = "javax.persistence.lock.timeout", value ="10000")}) Optional<Flight> findWithLockingById(Long id); } If the timeout expires, will be thrown. It is important to note that not all persistence providers support the hint . For example, Oracle's persistence provider supports this hint, whereas it does not for PostgreSQL, MS SQL Server, MySQL, and H2. CannotAcquireLockException javax.persistence.lock.timeout Now we consider a deadlock situation. @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.
